Re-entering a CASE statement in query

[Former Member]

Hi all

I have created the below query to show me all open quotations within a date range, calculating gross profit £ and %. I have entered the current exchange rates so the price is pulled through in £ (as we have three currencies on the system). This query gives me the exact data I need.

SELECT T0.[DocNum], T2.[SlpName], T0.[CardCode], T0.[CardName], T0.[TaxDate], T1.[ItemCode], T1.[Dscription], T1.[Price] AS 'Quote Price (£)',

CASE WHEN T3.[LastPurCur] = 'EUR' THEN T3.[LastPurPrc]*0.73 WHEN T3.[LastPurCur] = 'USD' THEN T3.[LastPurPrc]*0.68 ELSE T3.[LastPurPrc] END AS 'Last Purchase Price (£)',

(T1.[Price] - (CASE WHEN T3.[LastPurCur] = 'EUR' THEN T3.[LastPurPrc]*0.73 WHEN T3.[LastPurCur] = 'USD' THEN T3.[LastPurPrc]*0.68 ELSE T3.[LastPurPrc] END)) AS 'Gross Profit (£)',

CASE T1.[Price] WHEN 0 THEN 0 ELSE ((T1.[Price]-(CASE WHEN T3.[LastPurCur] = 'EUR' THEN T3.[LastPurPrc]*0.73 WHEN T3.[LastPurCur] = 'USD' THEN T3.[LastPurPrc]*0.68 ELSE T3.[LastPurPrc] END))/T1.[Price]*100) END AS 'Gross Profit (%)'

FROM OQUT T0  INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode] WHERE T0.[DocStatus] = 'O'  AND T0.[TaxDate]>='[%0]' and T0.[TaxDate]<='[%1]'

However, it seems complicated to have to re-enter the 'CASE' statement when I need to use the value returned from that case statement in another formula - ie in working out the gross profit. Is there a simpler way of doing this?

Regards

Wendy

[frank_wang6 · Accepted solution]

another way to it is to use sub query

basically, u do something like this.

select * from  (

  select case .... from ....

) T

You put your case statement in the sub query, and then you can use the result in outer select for other calculations.

[Former Member]

Hi Wendy,

It can be simplified as:

SELECT T0.[DocNum], T2.[SlpName], T0.[CardCode], T0.[CardName], T0.[TaxDate], T1.[ItemCode], T1.[Dscription], T1.[Price] AS 'Quote Price (£)', T3.[LastPurPrc]*

CASE T3.[LastPurPrc] WHEN 'EUR' THEN 0.73 WHEN 'USD' THEN 0.68 ELSE 1 END AS 'Last Purchase Price (£)',

(T1.[Price] - (T3.[LastPurPrc]*

CASE T3.[LastPurPrc] WHEN 'EUR' THEN 0.73 WHEN 'USD' THEN 0.68 ELSE 1 END) AS 'Gross Profit (£)',

CASE T1.[Price] WHEN 0 THEN 0 ELSE ((T1.[Price]-(T3.[LastPurPrc]*

CASE T3.[LastPurPrc] WHEN 'EUR' THEN 0.73 WHEN 'USD' THEN 0.68 ELSE 1 END)/T1.[Price]*100) END AS 'Gross Profit (%)'

FROM OQUT T0

INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode]

INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]

WHERE T0.[DocStatus] = 'O'  AND T0.[TaxDate]>='[%0]' and T0.[TaxDate]<='[%1]'

Thanks,

Gordon

[former_member184146]

Hi Wandy,

i did not get you , what you mean by " it seems complicated to have to re-enter the 'CASE' statement " once you have made the query then there is no need to re enter the case statement.

[Former Member]

If you look at my query, I have used a Case statement 'CASE WHEN T3.[LastPurCur] = 'EUR' THEN T3.[LastPurPrc]*0.73 WHEN T3.[LastPurCur] = 'USD' THEN T3.[LastPurPrc]*0.68 ELSE T3.[LastPurPrc] END'.

I need to use the result of that statement in order to work out Gross Profit, and so I've had to create formula (T1.[Price] - (CASE WHEN T3.[LastPurCur] = 'EUR' THEN T3.[LastPurPrc]*0.73 WHEN T3.[LastPurCur] = 'USD' THEN T3.[LastPurPrc]*0.68 ELSE T3.[LastPurPrc] END)) AS 'Gross Profit (£) - literally replicating the first Case statement into my formula.

I wondered if there was another way to do this.

Wendy

[former_member184146]

Okay , you can try the below query to prevent the re-entering of case

SELECT T0.[DocNum], T2.[SlpName], T0.[CardCode], T0.[CardName], T0.[TaxDate], T1.[ItemCode], T1.[Dscription], T1.[Price] AS 'Quote Price (£)',

t4.Last_Pur_in_euro as 'Last Purchase Price (£)',

(T1.[Price] - t4.Last_Pur_in_euro) AS 'Gross Profit (£)',

CASE T1.[Price] WHEN 0 THEN 0 ELSE ((T1.[Price]-(CASE WHEN T3.[LastPurCur] = 'EUR' THEN t4.Last_Pur_in_euro ELSE T3.[LastPurPrc] END))/T1.[Price]*100) END AS 'Gross Profit (%)'

FROM OQUT T0  INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]

INNER JOIN (SELECT CASE WHEN a.[LastPurCur] = 'EUR' THEN a.[LastPurPrc]*0.73

WHEN a.[LastPurCur] = 'USD' THEN a.[LastPurPrc]*0.68

ELSE a.[LastPurPrc] END AS Last_Pur_in_euro,a.ItemCode from OITM a) as t4 on t3.ItemCode=t4.itemcode

WHERE T0.[DocStatus] = 'O'  AND T0.[TaxDate]>='[%0]' and T0.[TaxDate]<='[%1]'

[frank_wang6]

I dont think there is another way in your case.

Frank