ASC / DESC based on CASE

Hi is it possible to sort data ASC or DESC using a CASE Statement based on
user input parameter ?
ThanksYes, use separate CASE expressions in the ORDER BY clause.
Anith|||On Tue, 3 Jan 2006 15:06:51 -0800, Vishal wrote:

>Hi is it possible to sort data ASC or DESC using a CASE Statement based on
>user input parameter ?
>Thanks
>
Hi Vishal,
Yes - but not the exact way that you think/hope for.
Example #1:
SELECT yadda, yadda
FROM whatever
ORDER BY CASE WHEN @.Direction = 'ASC' THEN SortColumn END ASC,
CASE WHEN @.Direction = 'DESC' THEN SortColumn END DESC
Example #2 (works only for numeric columns)
SELECT yadda, yadda
FROM whatever
ORDER BY SortColumn * CASE WHEN @.Direction = 'ASC' THEN 1 ELSE -1 END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||note that this one-size-fits-all approach may perform poorly, as
described here
http://www.devx.com/dbzone/Article/30149/0/page/2

article row filter - 2 parameters

hello,

i need to filter an article based on a user-supplied datetime filter (the datetime parameter is specified by the subscriber just before replication). at the same time i need to filter again by user (different subscribers get different rows).

i already did the user-based filter using HOST_NAME( ). but the difficulty here (al least i think so) lies in passing 2 parameters to the filter. i cannot rely on using SUSER_SNAME to pass the user filter, because no one will want to create 500 user accounts. so i guess the only solution here is to pass both parameters using only HOST_NAME( ) and then write 2 splitting functions which uses HOST_NAME( ) as its parameter. am i right ?

publisher/distributor is sql server 2005, all subscribers use sql mobile.

TIA, kamil nowicki

Using datetime would not be recommended since it is not deterministic.

For eg:

lets say you have a filter to get rows being touched only in the last 7 days.

Initially you get all rows.

After 7 days having not touched any rows, you would expect the merge agent deletes the 7 rows which will not be the case.

I would advise you to use SUSER_SNAME() or HOST_NAME() to get specific rows and in addition use another column 'status' or something that you can set/reset according to your business needs.

|||

thanks for replying.

i think that i have to use a datetime filter because the parameter to this filter has to be dynamic (worst case scenario: each subscriber uses different filter parameter for each of his replication sessions). is there another way to accomplish this ?

also, is there a way to store something in SUSER_SNAME() like using SqlCeReplication.HostName to store something into HOSTNAME() ?

|||

If each session of the subscriber uses a different filter, you will get an error with mismatched partitions. You would need to reinitialize the subscriber in that case. Are you ready to reinitialize the subscribers for every sync?

But note that as I mentioned previously, you will not be able to rely on datetime filter. Use a status column or something like that and update this column when you want rows to be in partition or out of partition.

|||

>> Use a status column or something like that and update this column when you want rows to be in partition or out of partition.

but that would mean that i have to use a "fixed filter" (same "replicate from ..." date for every subscriber), wouldn't it ?

|||How about: login=SUSER_SNAME and status='Y'|||

We have a best practice article for time-based filtering, you may want to reference it, it's what Mahesh is talking about:

Best Practices for Time-Based Row Filters

http://msdn2.microsoft.com/en-us/library/ms365153.aspx

|||

and how does that solve my scenario ? i want each subscriber to be able to choose a date ( "replicate from..." ) before each of his replication sessions. SP on the server will not know those dates when executed, so how am i supposed to update the "status" column ?

thanks for the link, i have read that before starting the thread.

|||

Are you saying that every time the subscriber syncs, it sends a new date and expects only releant rows? You cannot achieve this using the non-deterministic filter functions. If you are ready to reinitialize your subscriptions every time you sync, you may do that. Do a reinit on the publisher/subscriber, then send in the appropriate date as the hostname to get the relevant rows. However you wont be able to upload in this session because your filters may not match data that you want to upload.

|||

i took a different approach and now everything is working as it should. i wrote a SP on the backend server which updates every row in the filtered article for a given user (SP is parametrized with @.user nvarchar and @.filter datetime), changing rep_status tinyint column. the SP is called from subscriber on the distributor just before each replication session, so now the SP has all the info to update the filtered article (@.user and @.filter). article is filtered by rep_status.

the drawback is that the backend has to be put out of LAN to the internet (public IP), but later i will write a WebService so that all the data will pass through IIS and then the backend will be again NATed.

edit: and of course this works without subscription reinitialization :)

Array?

I have a query where I pull data based on a min function the gives me the
newest order. However when I pull the data I will get four records as I nee
d
to pull all the data for this order. What I need to know is how do I take
the data that I need assign a variable to it and only return one record.
Do I need to write a stored procedure that writes the data fields to a
variable and then bring them into my query?>I have a query where I pull data based on a min function the gives me the
> newest order. However when I pull the data I will get four records as I
> need
> to pull all the data for this order.
I'm lost. Please show DDL, sample data, and desired results.
http://www.aspfaq.com/5006|||Here is my code:
declare @.con as varchar(30)
set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus
where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus =
1 and
dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
--Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1 an
d
--dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Station
5a,dtbl_1612BinData.Station5b,
dtbl_1612BinData.Station5c, dtbl_partList.partID,
dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
pckgPackageNumber, pkctSlotNumber
From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
dtbl_OrderStatus, dtbl_packagingDetails
Where @.con=dtbl_workOrder.corhOrderNumber and
dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
Here is my response:
corhOrderNumber attvId
Station1 Station3 Station5a
Station5b Station5c partID
CustomerPartId OrderStatus
pckgPackageNumber pkctSlotNumber
----
---- --
-- -- -- --
----
---- --
---- --
2006-0100001 CRM
1,0 0,0 2,0
44040192,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 CURLKOA
1,0 0,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 222MID
115,0 47185920,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 22xMOD
5,0 0,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
I need to take the attvID and station information and string it out like
this...
corhOrderNumber attvId
Station1 Station3 Station5a
Station5b Station5c attvId2 Station1.2 Station2.2
Station5a.2 Station5b.2 Station5c.2
Station1.3 Station2.3 Station5a.3 Station5b.3
Station5c.3
Station1.4 Station2.4 Station5a.4 Station5b.4
Station5c.4
partID CustomerPartId
OrderStatus pckgPackageNumber
pkctSlotNumber
Basically so it only returns one record. I will then need to pass this
information as a variable to another program.
Thanks,
"Aaron Bertrand [SQL Server MVP]" wrote:

> I'm lost. Please show DDL, sample data, and desired results.
> http://www.aspfaq.com/5006
>
>|||I believe we asked for DDL, not your code. What you posted looks like
"camelCase" names that violate ISO-11179; what the heck is a "dtbl_"
prefix?
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Your entire view of RDBMS is
wrong. You do not know that rows are not ANYTHING like a record, etc.
You have just been told by one of the people who wrote this language
that you do not know what you are doing. Think about that. Then tell
your boss, or I will. You need more help than you can get in a
Newsgroup.|||Hey Celko I really appreciate your help with this. Your the MAN!!
"--CELKO--" wrote:

> I believe we asked for DDL, not your code. What you posted looks like
> "camelCase" names that violate ISO-11179; what the heck is a "dtbl_"
> prefix?
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Your entire view of RDBMS is
> wrong. You do not know that rows are not ANYTHING like a record, etc.
>
> You have just been told by one of the people who wrote this language
> that you do not know what you are doing. Think about that. Then tell
> your boss, or I will. You need more help than you can get in a
> Newsgroup.
>|||If I were in the position to need to do what you ask..
If there were always a max of 4 rows, and you can uniquely identify them,
I would create a user defined function which returns a table. THe table
definition would look like your code suggests.
You could insert the first row, then update it with the other rows of data -
appending to the trailing columns.
Then return the table with a single row...
Alternately you could create a stored procedure which has a local variable
for each value... populate the values with your 4 select statements, Then
SELECT the values out of the SP as a result set.
The first option is more flexible because it yeilds a table instead of a
result set...
Good luck, and hope this helps;
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"meverts" wrote:
> Here is my code:
> declare @.con as varchar(30)
> set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStat
us
> where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus
=
> 1 and
> dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
> --Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
> dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1
and
> --dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
>
> Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
> dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Stati
on5a,dtbl_1612BinData.Station5b,
> dtbl_1612BinData.Station5c, dtbl_partList.partID,
> dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
> pckgPackageNumber, pkctSlotNumber
> From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
> dtbl_OrderStatus, dtbl_packagingDetails
> Where @.con=dtbl_workOrder.corhOrderNumber and
> dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
> and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
> dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
> dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
> dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
> Here is my response:
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c partID
> CustomerPartId OrderSta
tus
> pckgPackageNumber pkctSlotNumber
> ----
> ---- --
> -- -- -- --
-
> ----
> ---- --
> ---- --
> 2006-0100001 CRM
> 1,0 0,0 2,0
> 44040192,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 CURLKOA
> 1,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 222MID
> 115,0 47185920,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 22xMOD
> 5,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
>
> I need to take the attvID and station information and string it out like
> this...
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c attvId2 Station1.2 Station2.2
> Station5a.2 Station5b.2 Station5c.2
> Station1.3 Station2.3 Station5a.3 Station5b.3
> Station5c.3
> Station1.4 Station2.4 Station5a.4 Station5b.4
> Station5c.4
> partID CustomerPartId
> OrderStatus pckgPackageNumber
> pkctSlotNumber
> Basically so it only returns one record. I will then need to pass this
> information as a variable to another program.
> Thanks,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||>I believe we asked for DDL, not your code.
Correct, we did not get any CREATE TABLE or INSERT statements, so we have to
guess about a lot of things (or keep asking, maybe if we put sugar on top?).
However, the rest of your post is a useless tangent. Who cares if he uses
camelCase names? He still has a database problem he needs help with. Just
because his metadata, element names and table structure aren't identical to
what *you* would have created with the same task in front of you, doesn't
mean he should be ignored or told to go take a class or told that he is not
worthy of the newsgroup because he is not as smart as you.
I have been in the situation where the structure is NOT mine and I just have
to deal with it. I have also been in the situation where I've been made
responsible for parts of the project that did not align exactly with my core
competencies. And I have been in the situation where the guy that knew all
about it has been fired or left for other reasons. You should consider
giving people the benefit of the doubt instead of sh*tting on their head.
I have met you in person multiple times, I have shared lunch with you, and I
know that you can be a polite and even humble person. Why you choose to be
such a hateful, miserable know-it-all here, I have yet to figure out.
A|||No offense, but I can't really read this (particularly the results, which
may have looked good while composing your message, but certainly don't now).
Could you provide DDL (CREATE TABLE ...), sample data (INSERT ...), and a
more concise version of your desired results, so we can understand exactly
what you need? You might need to see http://www.aspfaq.com/500 again.
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:549C0EB5-1406-4433-A165-CF06CE38ABD6@.microsoft.com...
> Here is my code:
> declare @.con as varchar(30)
> set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder,
> dtbl_OrderStatus
> where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus
> =
> 1 and
> dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
> --Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
> dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1
> and
> --dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
>
> Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
> dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Stati
on5a,dtbl_1612BinData.Station5b,
> dtbl_1612BinData.Station5c, dtbl_partList.partID,
> dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
> pckgPackageNumber, pkctSlotNumber
> From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
> dtbl_OrderStatus, dtbl_packagingDetails
> Where @.con=dtbl_workOrder.corhOrderNumber and
> dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
> and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
> dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
> dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
> dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
> Here is my response:
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c partID
> CustomerPartId
> OrderStatus
> pckgPackageNumber pkctSlotNumber
> ----
> ---- --
> -- -- -- --
-
> ----
> ---- --
> ---- --
> 2006-0100001 CRM
> 1,0 0,0 2,0
> 44040192,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 CURLKOA
> 1,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 222MID
> 115,0 47185920,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 22xMOD
> 5,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
>
> I need to take the attvID and station information and string it out like
> this...
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c attvId2 Station1.2 Station2.2
> Station5a.2 Station5b.2 Station5c.2
> Station1.3 Station2.3 Station5a.3 Station5b.3
> Station5c.3
> Station1.4 Station2.4 Station5a.4 Station5b.4
> Station5c.4
> partID CustomerPartId
> OrderStatus pckgPackageNumber
> pkctSlotNumber
> Basically so it only returns one record. I will then need to pass this
> information as a variable to another program.
> Thanks,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Here are the ddl's for the three table from which my query uses.
Basically what I want to do is take 4 sets of data back.
I would like to figure out how to make this into one set.
CREATE TABLE [dtbl_workOrder] (
[corhOrderNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[corhVinNumber] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[corhSequenceNumber] [int] NOT NULL ,
[prdfId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[wordDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordBatchQty] [int] NULL ,
[wordPreScheduleKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordBatchIdSched] [int] NOT NULL ,
[wordBatchSerialSched] [int] NOT NULL ,
[wordReleaseOrder] [int] NOT NULL ,
[wordPackOrder] [int] NOT NULL ,
[wordProcessStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordProductionStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordBackflushStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordPackStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordHold] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordCreationDateTime] [datetime] NULL ,
[wordUpdatedDateTime] [datetime] NULL ,
[wordReleaseDateTime] [datetime] NULL ,
[labelID] [numeric](18, 4) NULL ,
[wolvId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wolvVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wolvDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[asshId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[assuId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wcelId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderSequence] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_dtbl_workOrder] PRIMARY KEY CLUSTERED
(
[wordReleaseOrder]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dtbl_OrderStatus] (
[wordReleaseOrder] [int] NOT NULL ,
[OrderStatus] [int] NULL ,
[OrderFillDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderSubmitDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_dtbl_OrderStatus] PRIMARY KEY CLUSTERED
(
[wordReleaseOrder]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dtbl_attributeList] (
[wordReleaseOrder] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[partId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attributeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[attnId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attvId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attribkey] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_dtbl_attributeList] PRIMARY KEY CLUSTERED
(
[attribkey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Currently I return the following( I know this doesn't look very good)
2006-0100001 CRM 1,0 0,0 2,0 44040192,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 CURLKOA 1,0 0,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 222MID 115,0 47185920,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 22xMOD 5,0 0,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
The desired results would be like this
2006-0100001 CRM 1,0 0,0 2,0 44040192,0 0,0
CURLKOA 1,0 0,0 0,0 0,0 0,0 222MID 115,0
47185920,0 0,0 0,0 0,0 22xMOD 5,0 0,0 0,0
0,0 0,0 1655009B1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
I hope this helps. I appreciate you help. I obviously am very new at this,
and dont' know what I am doing, but sometimes in the real world you need to
learn on the job.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:

> No offense, but I can't really read this (particularly the results, which
> may have looked good while composing your message, but certainly don't now
).
> Could you provide DDL (CREATE TABLE ...), sample data (INSERT ...), and a
> more concise version of your desired results, so we can understand exactly
> what you need? You might need to see http://www.aspfaq.com/500 again.
>
>
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:549C0EB5-1406-4433-A165-CF06CE38ABD6@.microsoft.com...
>
>|||In my experience, displaying multiple rows of data as one usually turns out
to be more trouble than it is worth. I reccomend looking for another way to
present the data, or possibly handling it in your app instead of in the
database. I've been forced to do what you are discussing in the past, and
have found ugly ways to do it, but I dont recomend it. The biggest problem
is that the number of rows which need to be consolidated has a tendency to
change over time, which means you are constantly updating your code to
match.
Revisit the reasons for this aproach and see if there is another way to
display this data that will fit your business need.
That said, if you absolutely have to do it this way, populating a temp table
in a stored procedure is better than any of the solutions I have used in the
past. It shouldnt be too much trouble to loop through the results and
insert/update the table as needed, then select from the table when you are
done.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:998EFAD9-7CCD-470D-9183-D89F8CB2FBFB@.microsoft.com...
> If I were in the position to need to do what you ask..
> If there were always a max of 4 rows, and you can uniquely identify them,
> I would create a user defined function which returns a table. THe table
> definition would look like your code suggests.
> You could insert the first row, then update it with the other rows of
data -
> appending to the trailing columns.
> Then return the table with a single row...
> Alternately you could create a stored procedure which has a local variable
> for each value... populate the values with your 4 select statements, Then
> SELECT the values out of the SP as a result set.
> The first option is more flexible because it yeilds a table instead of a
> result set...
> Good luck, and hope this helps;
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "meverts" wrote:
>
dtbl_OrderStatus
dtbl_OrderStatus.orderstatus =
and
dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Station
5a,dtbl_1612BinData.Station5b,
dtbl_partList,
OrderStatus
> -- -- -- --
-
Station2.2
the
as I

Arithmetic Overflow - VB6.0/Stored Proc - Application

Dear All,

I am a Singapore based ASP/VB developer and need your advise for a problem i am facing in my VB/SQLServer/Stored Procedure application.

When i executed a stored procedure(SQLServer 6.5) from VB(6.0) application, I had error messages, from ADO error collection, which are "Arithmetic overflow error converting numeric to data type numeric." followed by "The statement has been terminated.". This is the exact message(s) i am getting.

The commands i executed are :

1.
update live_stock
set
stock_value = convert(numeric(9,3), convert(numeric(28,3) , "2593500")/1 ),
day = convert(Char(3), datename(dw, getdate())) ,
stock_timestamp = getdate()
where
stock_code="C27 " and
market =

Pls note that the database data type of "stock_value" column is "int".

2.
update live_stock
set
stock_volume = convert(numeric(9,3), convert(numeric(28,3) , "1017000")/1 ),
day = convert(Char(3), datename(dw, getdate())) , stock_timestamp = getdate()
where
stock_code="C27 " and
market =

Pls note that the database data type of "stock_volume" column is "numeric](11, 2)".

Need you advise/help as this is taking up lot of my time.

Thanks and regards,
Muru
Singaporei know nothing of asp or vb, so maybe the reasons why you are doing it that way escape me

if i were doing it, i would write

set stock_value = 2593500

rudy|||Thanks for the reply.

For simplicity, i have mentioned the expression like "2593500"/1. Actually, this 1 is a parameter - could be 1 or 2 etc...

Any suggestion?

Regards,
Muru|||okay, try not putting quotes around your numerics

e.g. set stock_value = 2593500 / 2

rudy|||Originally posted by r937
okay, try not putting quotes around your numerics

e.g. set stock_value = 2593500 / 2

rudy

2593500 is also a parameter of string and is being passed from VB. My backend VB application is updating the database only and this program is an ugraded version of existing version. Currently, there are ASP pages accessing this data from the sqlserver database.

I can do the conversion on the VB side. But my concern is that it may affect the formatting for existing ASP pages. And also that, this will lead to changes in the parameter datatype.

For this reason, i am keeping all the existing data types for the parameters for all the functions and stored procedures.

Thanks,
Muru|||Here is the problem - you have a value that has 7 digits to the left and 3 digits to the right of the decimal point. However, your precision that you are asking for is 9 digits total - but you have 10 total. That is why you are receiving the overflow. Either change to (9,2) or (10,3) and that will fix your overflow problem. Also, be careful and examine how sql server handles rounding/truncating when you store an int/decimal(11,2) from a decimal(x,3). In your case -- When you convert to an int, ss will truncate. When you convert to a smaller decimal place, ss will round. So, in your situation you have 2 different "rounding" models which may eventually affect (adversely) your results - depending on how scientific or precise you are trying to be.|||rnealejr is correct, (you are receiving the overflow because you have 10 digits that you are casting to 9). I would add the following more general warning:

It is often very important to be aware of the implications of (both) explicit (as rnealejr pointed out) and implicit type conversions (unfortunatly, the results are often DBMS implementation, and / or version specific);

-- Simple example:
Select
1234567 / 17 As 'Truncation',
1.0000 * (1234567 / 17 ) As 'Truncation + Scale',
1.0000 * 1234567 / 17 As 'LessTruncation',
1.0000 * 1234567 / '17' As 'MuchLessTruncation',
1.0000 * (1234567 / '17') As 'Truncation + Scale'

-- More simple examples with some explanation:
Select
1234567.0000 / 17.0000 As 'A',
(1.0000 * 1234567) / (1.0000 * 17) As 'B',
1234567 / 17 As 'C'

-- Some Conversions involve truncation
Select
Cast ((1234567.0000 / 17.0000) AS Int) As 'Cast A --> Int',
Cast (((1.0000 * 1234567) / (1.0000 * 17)) AS Int) As 'Cast B --> Int',
Cast ((1234567 / 17) AS Int) As 'Cast C --> Int'

-- Some Conversions involve rounding
Select
Cast ((1234567.0000 / 17.0000) AS Money) As 'Cast A --> Money',
Cast (((1.0000 * 1234567) / (1.0000 * 17)) AS Money) As 'Cast B --> Money',
Cast ((1234567 / 17) AS Money) As 'Cast C --> Money'

-- Other Conversions are not as intuitively obvious
-- Char (implicit conversions) of A, B, and C give:
Select 1234567.0000 / '17.0000' As 'A Char',
'B Char results in an overflow' As 'B Char',
1234567 / '17' As 'C Char'
-- NOTE B Char --> results in an overflow (OVERFLOW error result)
Select (1.0000 * 1234567) / (1.0000 * '17') As 'B Char Overflow'|||For future reference, go to your sql books online and look at the "Cast and Convert" reference.|||Dear All,

Thanks a lot for your help.

I have changed the scaling from numeric(9,3) to (11,3) and it now works. When tried with (10,3), my program gave the same error again for another set of data and hence i am using (11,3).

Thanks and regards,
Muru

Are there limitations to OLAP in Excel?

Hi,

Now, I use Analysis Services 2005 and Excel 2003 SP2 to deploy my OLAP model.

In Excel sheet, when creating a pivot table based on a Cube in Analysis Services, everything appears to work until I add a fourth dimension to the pivot table. Once I do that, I get a message that says "Running OLAP Query...(Press Esc to cancel)" so I must wait for a long time (about 5 minutes) to get the result. But while browsing the same data dimensions in Analysis Services Window, everything appears immediately.

Any ideas?

Best Regards!

You might qualify the question to read: "Are there limitations to using AS 2005 cubes in Excel 2003?"

Choosing client for SSAS 2005: Excel 2003 or Excel 2007

By Ramunas Balukonis

After we migrated Analysis Services from 2000 to 2005 we discovered that the same (or analogous) queries against SSAS 2005 runs even slower than AS 2000. Our users used MS Excel 2003 as front end on AS 2005. After some time I installed MS Excel 2007. Then I tried to do the same Pivot Table actions on both Excel versions (2003 and 2007): make Pivot Table from Advenure Works database placing days of [Date].[Calendar] [July 2003] and [August 2003] on rows, [Mountain-200 Silver, 38] and [Mountain-200 Silver, 42] from product [Product].[categories] on columns and [Sales Amount] as facts

Excel 2003 generates cumbersome MDX wits lots of except:

...

Excel 2007 generates elegant MDX:

...

The 1’st query from Excel 2003 runs 5 times longer!

After some time I gathered statistics from profiler and querylog. The results are as follows:

Avg MDX text length (Excel 2003): 2403
Avg MDX text length (Excel 2007): 1567
Avg query duration (Excel 2003): 507 ms
Avg query duration (Excel 2007): 96 ms

|||Thanks Ramunas!
Did you try to use the browser in Analysis Services or the Reporting Services to query your OLAP data . In my opinion, the queried time of using two ways above is better than the time of using Excel pivot table.
Could you explain me the reason why?

|||

Yeah, Excel 2003 is horrible with OLAP. It's the MDX issue, it does tons of EXCEPTS(), etc. Instead of scripting what it wants in the pivottable, it scripts what it doesn't want. Not sure how this ever got passed.

For your last reply, you will be hitting cache if you run from Excel, then run the same query somewhere else. You would have to execute the ClearCache command after every MDX statement for a true test. Or could be a network slowup if your using Excel externally (off the server) and running Management Studio on the server directly, for example.

Are there limitations to OLAP in Excel?

Hi,

Now, I use Analysis Services 2005 and Excel 2003 SP2 to deploy my OLAP model.

In Excel sheet, when creating a pivot table based on a Cube in Analysis Services, everything appears to work until I add a fourth dimension to the pivot table. Once I do that, I get a message that says "Running OLAP Query...(Press Esc to cancel)" so I must wait for a long time (about 5 minutes) to get the result. But while browsing the same data dimensions in Analysis Services Window, everything appears immediately.

Any ideas?

Best Regards!

You might qualify the question to read: "Are there limitations to using AS 2005 cubes in Excel 2003?"

Choosing client for SSAS 2005: Excel 2003 or Excel 2007

By Ramunas Balukonis

After we migrated Analysis Services from 2000 to 2005 we discovered that the same (or analogous) queries against SSAS 2005 runs even slower than AS 2000. Our users used MS Excel 2003 as front end on AS 2005. After some time I installed MS Excel 2007. Then I tried to do the same Pivot Table actions on both Excel versions (2003 and 2007): make Pivot Table from Advenure Works database placing days of [Date].[Calendar] [July 2003] and [August 2003] on rows, [Mountain-200 Silver, 38] and [Mountain-200 Silver, 42] from product [Product].[categories] on columns and [Sales Amount] as facts

Excel 2003 generates cumbersome MDX wits lots of except:

...

Excel 2007 generates elegant MDX:

...

The 1’st query from Excel 2003 runs 5 times longer!

After some time I gathered statistics from profiler and querylog. The results are as follows:

Avg MDX text length (Excel 2003): 2403
Avg MDX text length (Excel 2007): 1567
Avg query duration (Excel 2003): 507 ms
Avg query duration (Excel 2007): 96 ms

|||Thanks Ramunas!
Did you try to use the browser in Analysis Services or the Reporting Services to query your OLAP data . In my opinion, the queried time of using two ways above is better than the time of using Excel pivot table.
Could you explain me the reason why?

|||

Yeah, Excel 2003 is horrible with OLAP. It's the MDX issue, it does tons of EXCEPTS(), etc. Instead of scripting what it wants in the pivottable, it scripts what it doesn't want. Not sure how this ever got passed.

For your last reply, you will be hitting cache if you run from Excel, then run the same query somewhere else. You would have to execute the ClearCache command after every MDX statement for a true test. Or could be a network slowup if your using Excel externally (off the server) and running Management Studio on the server directly, for example.