Hi there, I have a question regarding a query to extract measures from a fact table. The fact table from the source system contains delivery notes with turnover in one dataset. The primary key is the delivery note number plus a consecutive number. The same table contains also the cancellations of the delivery note with the same turnover and the same primary key as the delivery note, only differed by another consecutive number. The cancellation is represented by another column which contains either a "0" or a "1" (cancellation no/yes).
The problem is that I'd like to change the turnover value for cancellations with a prefix instead of using another column in the fact table. Cause then I'd be able to group those numbers...
Is there a SQL function that allows me to
Example datasets:
Code Snippet
ConsNo DelNNo Canc Turnover Amount
001 200 0 1000 500
002 200 1 1000 500
I'd like to achieve this output by a query:
Code Snippet
ConsNo DelNNo Turnover Amount
001 200 1000 500
002 200 -1000 -500
Any ideas? I've heard of a function called "decode" but I think it doesn't work in SQL Server 2005...
Just create a view that depending on the Canc column multiplies the Turnover and Amount by 1 or -1.|||But how to use IF-statement and mathematical functions within a SELECT-statement? That would be T-SQL right?|||
In SQL you can do something like:
select ConsNo,
DelNNo,
Turnover = case
when Canc = 0
then Turnover
else Turnover * -1
end,
Amount = case
when Canc = 0
then Amount
else Amount * -1
end
from <table name>
There is also the possibility to use isnull and nullif to simulate the previous cases.
|||
Tiago Rente wrote:
In SQL you can do something like:
select ConsNo,
DelNNo,
Turnover = case
when Canc = 0
then Turnover
else Turnover * -1
end,
Amount = case
when Canc = 0
then Amount
else Amount * -1
end
from <table name>
There is also the possibility to use isnull and nullif to simulate the previous cases.
It works, but when I add the GROUP BY function I always get an error message that "Canc" and "Turnover" aren't groupable!
Surprisingly I didn't even select the "Canc"-attribute, and I don't use Turnover within the GROUP-function.
|||Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.
In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor 
Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.
|||
Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.
In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor ).
Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.
I tried to use the case-statement in the GROUP BY function but the result is the same. Weird...
|||Summing the case statements should do the trick.
Code Snippet
select ConsNo,
DelNNo,
Turnover = SUM(case
when Canc = 0
then Turnover
else Turnover * -1
end),
Amount = SUM(case
when Canc = 0
then Amount
else Amount * -1
end)
from <table name>
GROUP BY
ConsNo
, DelNo
