Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Case Statements

Hi,

I have this Case Statement, below, and I want it to return a specific numeric value.  Any thoughts?

thank.

case when d.product in ('DEBT', 'DISCOUNT', 'FRA', 'VARIANCE') and dspr.IsBuyOrSell = '1' then 'Buy'
when d.product in ('DEBT', 'DISCOUNT', 'FRA', 'VARIANCE') and dspr.IsBuyOrSell = '2' then 'Sell'  
when d.product in ('EQUITYSWAP', 'CREDITDEFAULT') and dspr.IsBuyOrSell = '1' then 'Recieve'--dsr.currentrate
when d.product in ('EQUITYSWAP', 'CREDITDEFAULT') and dspr.IsBuyOrSell = '2' then 'Pay'--dsp.currentrate  
when dsp.side = 'R' then 'Recieve'-- dsr.currentrate  
when dsp.side = 'P' then  'Pay' --dsp.currentrate  
when d.product in ('CAP','EUITYOPTION') then 'Pay' else 'Option' end

dsp.currentrate  [pay rate]
0
fundsf
Asked:
fundsf
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
gpizzutoCommented:
Why don't you use a pre-defined table containing the values of the fields you want to match
and JOIN it to your table ?
0
 
SJCFL-AdminCommented:
If you wanted it to return a numeric value then i would expect to see a numeric expression after the 'then' specifying how the returned numeric value should be computed.  but perhaps I am misunderstanding and you need to provide additional information as to what your requirements are..
0
 
HainKurtSr. System AnalystCommented:
here is numeric values :)

case 
when d.product in ('DEBT', 'DISCOUNT', 'FRA', 'VARIANCE') and dspr.IsBuyOrSell = '1' then 1
when d.product in ('DEBT', 'DISCOUNT', 'FRA', 'VARIANCE') and dspr.IsBuyOrSell = '2' then 2
when d.product in ('EQUITYSWAP', 'CREDITDEFAULT') and dspr.IsBuyOrSell = '1' then 3
when d.product in ('EQUITYSWAP', 'CREDITDEFAULT') and dspr.IsBuyOrSell = '2' then 4
when dsp.side = 'R' then 3
when dsp.side = 'P' then  4
when d.product in ('CAP','EUITYOPTION') then 4
else 0
end 

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
fundsfAuthor Commented:
base on the case i want it to return the dsp.currentrate...
0
 
SJCFL-AdminCommented:
The equivalent to a case in SSRS is the SWITCH.. Is that what you wanted?

=SWITCH (  Fields!d.product='DEBT'      AND dspr.IsBuyOrSell = '1', 1,
           Fields!d.product='DISCOUNT'  AND dspr.IsBuyOrSell = '1', 1,
           Fields!d.product='FRA'       AND dspr.IsBuyOrSell = '1', 1,
           Fields!d.product='DEBT'      AND dspr.IsBuyOrSell = '1', 1,
           Fields!d.product='DEBT'      AND dspr.IsBuyOrSell = '1', 1,
           Fields!d.product='DEBT'      AND dspr.IsBuyOrSell = '2', 2,
           Fields!d.product='DISCOUNT'  AND dspr.IsBuyOrSell = '2', 2,
           Fields!d.product='FRA'       AND dspr.IsBuyOrSell = '2', 2,
           Fields!d.product='DEBT'      AND dspr.IsBuyOrSell = '2', 2,
           Fields!d.product='DEBT'      AND dspr.IsBuyOrSell = '2', 2,
           Fields!d.product='EQUITYSWAP'    AND dspr.IsBuyOrSell = '1', 3,
           Fields!d.product='CREDITDEFAULT' AND dspr.IsBuyOrSell = '2', 4,
           Fields!d.product='EQUITYSWAP'    AND dspr.IsBuyOrSell = '1', 3,
           Fields!d.product='CREDITDEFAULT' AND dspr.IsBuyOrSell = '2', 4,
           True, 0)
0
 
SJCFL-AdminCommented:
oops missing equivalents for

when dsp.side = 'R' then 3
when dsp.side = 'P' then  4
when d.product in ('CAP','EUITYOPTION') then 4


but I think you get the idea...  And setting up a table and doing a join is looking really really good to me... lol
0
 
gpizzutoCommented:
@SJCFL-Admin: really happy you appreciated it (joining with the table)
In this way changes to logic can be easily managed
0
 
SJCFL-AdminCommented:
fundsf,

Did any of the suggestions help?  Or did you decide on an alternate solution?  It has been a while and I am curious to know how this was finally resolved....
0
 
fundsfAuthor Commented:
Thnaks for all the help!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now