asked
validation in CASE STATEMENT
Dear All, I have a query if someone can help me formulate. Basically, the query is about validating an calculated amount against a number and see if there is a breach or not. SELECT DISTINCT D.DEALNO,D.CCY, CASE WHEN D.CCY='AED' THEN d.CCYAMT*R.SPOTRATE_8*3.673 else D.CCYAMT*r.SPOTRATE_8/3.673 END AS 'Deal_Amount_USD', CASE WHEN (D.CCY ='AED' OR D.CTRCCY='AED') and (35000000 - (d.CCYAMT*R.SPOTRATE_8*3.673)) < 0 then 'Breach' WHEN (D.CCY ='USD' OR D.CTRCCY='USD') and (35000000 - (d.CCYAMT*R.SPOTRATE_8*3.673)) < 0 then 'Breach' WHEN (D.CCY <> 'AED' or D.CCY <> 'USD' AND D.CTRCCY <>'AED' OR D.CTRCCY <>'USD') AND (35000000 - (d.CCYAMT*R.SPOTRATE_8*3.673)) < 0 then 'Breach' else 'No Breach' END AS 'Remarks' FROM FXDH D INNER JOIN REVP R ON D.CCY=R.CCY INNER JOIN TRAD T ON D.TRAD=T.TRAD where d.INPUTDATE > '10 jun 2015' AND D.CCY=R.CCY Results DEALNO CCY Deal_Amount_USD Remarks 1011259 USD 500000.000000000 No Breach 1011260 USD 3000000.000000000 Breach 1011261 EUR 57173.972229784 No Breach 1011262 GBP -155900.081677103 No Breach 1011263 USD 30000000.000000000 Breach 1011264 EUR 9147835.556765586 Breach 1011265 EUR 45739177.783827933 Breach 1011266 USD 20000000.000000000 Breach 1011267 EUR -1143479.444595698 No Breach 1011268 USD 500000.000000000 No Breach 1011269 EUR 9147835.556765586 Breach 1011270 EUR -9147835.556765586 No Breach 6000004 EUR 1143479.444595698 No Breach 6000005 GBP -1559000.816771031 No Breach 6000006 EUR 571739.722297849 No Breach 6000007 AUD 75230.057173972 No Breach 6000008 EUR 1143479.444595698 No Breach 6000009 EUR -1143479.444595698 No Breach 6000010 EUR 29730465.559488156 Breach 6000011 EUR 29730465.559488156 Breach 6000012 EUR 17152191.668935475 Breach 6000013 EUR 28586986.114892458 Breach 6000014 EUR 29730465.559488156 Breach 6000015 EUR -1143479.444595698 No Breach 6000016 EUR 1143479.444595698 No Breach 6000017 EUR 571739.722297849 No Breach 6000018 EUR 285869.861148924 No Breach The number validation doesnt seem to work as the remarks are wrong, can someone help.
In order to help you quicker - is there any particular one that is wrong? I don't really want to have to code up your table structure and 28 inserts! Of course if you can provide that DDL as well it would help!
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
I think query looks allright, you just need to figure out the first part of when condition and the proper brackets:- 1. D.CCY ='AED' OR D.CTRCCY='AED' --- either of the columns could be equal to aed 2. D.CCY ='USD' OR D.CTRCCY='USD' --- either of the columns could be equal to usd 3. D.CCY 'AED' or D.CCY 'USD' AND D.CTRCCY 'AED' OR D.CTRCCY 'USD' -- this condition, would never get executed because possible values would be:- ccy ctrccy aed aed aed usd usd aed usd usd abc usd usd abd abc aed aed abc abc efg For 3 case I think you are looking for abc and efg values in that case the code should become:- sELECT DISTINCT D.DEALNO,D.CCY, CASE WHEN D.CCY='AED' THEN d.CCYAMT*R.SPOTRATE_8*3.673 else D.CCYAMT*r.SPOTRATE_8/3.673 END AS 'Deal_Amount_USD', CASE WHEN ((D.CCY ='AED' OR D.CTRCCY='AED') and (35000000 - (d.CCYAMT*R.SPOTRATE_8*3.673)) < 0) then 'Breach' WHEN ((D.CCY ='USD' OR D.CTRCCY='USD') and (35000000 - (d.CCYAMT*R.SPOTRATE_8*3.673)) < 0) then 'Breach' WHEN ((D.CCY 'AED' and D.CCY 'USD' AND D.CTRCCY 'AED' and D.CTRCCY 'USD') AND (35000000 - (d.CCYAMT*R.SPOTRATE_8*3.673)) < 0) then 'Breach' else 'No Breach' END AS 'Remarks' FROM FXDH D INNER JOIN REVP R ON D.CCY=R.CCY INNER JOIN TRAD T ON D.TRAD=T.TRAD where d.INPUTDATE > '10 jun 2015' AND D.CCY=R.CCY
As @erlokeshsharma08 suggests, I believe that your problem is with the (technical term) [operator precedence][1] in your case statement. To summarise that last `WHEN` logic, you have: WHEN (a OR b AND c OR d) The way that precedence works is that this would be calculated as: WHEN (a OR (b AND c) or D) So, the `AND` is evaluated before either of the `OR`s. Perhaps a restructure / refactor of that code would be of help? [1]:
https://msdn.microsoft.com/en-us/library/ms190276.aspx
Many thanks for your feedback and the clue. I will try modifying as per suggestion and let you know. Really appreciate your help.
20 People are following this question.
