Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Nested and Complex If Then Statement

Posted on 2011-04-27
4
Medium Priority
?
267 Views
Last Modified: 2012-05-11
How do I 'translate" the following Crystal Reports if, then statement into a comparable statement in SQL Reporting Services?

if {SOP30200.SOPTYPE}=4 then
-{SOP30300.QUANTITY}else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BK' then {SOP30300.QUANTITY}*50 else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='IV' then {SOP30300.QUANTITY}*50 else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='IN' then {SOP30300.QUANTITY}*200 else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BO' then {SOP30300.QUANTITY}*50 else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BS' then {SOP30300.QUANTITY}*50 else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='PI0' then {SOP30300.QUANTITY}*100 else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='D3K' then {SOP30300.QUANTITY}*500 else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='PS0' then {SOP30300.QUANTITY}*100 else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='DBM' then {SOP30300.QUANTITY}*200 else

if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BK' then {SOP30300.QUANTITY}*50 else
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='IV' then {SOP30300.QUANTITY}*50 else
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='IN' then {SOP30300.QUANTITY}*200 else
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BO' then {SOP30300.QUANTITY}*50 else
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BS' then {SOP30300.QUANTITY}*50 else
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='PI0' then {SOP30300.QUANTITY}*100 else
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='D3K' then {SOP30300.QUANTITY}*500 else
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='PS0' then {SOP30300.QUANTITY}*100 else
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='DBM' then {SOP30300.QUANTITY}*200 else
{SOP30300.QUANTITY}
0
Comment
Question by:apitech
  • 3
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35476266
I would not solve that with IF/CASE statements, but with a (left) join to a lookup table, and make it hence much more flexible, though simpler in code.
0
 
LVL 1

Author Comment

by:apitech
ID: 35476286
Well, I'd rather not go that route.  That's too difficult.

I'm taking a Crystal report and putting it into SSRS.  I just need that statement translated.

Thank you.
0
 
LVL 1

Author Comment

by:apitech
ID: 35476686
OK.  If I have to do that, so be it.  Can someone please at least help me fix the syntax errors in SQL in the following case statement?  That way, I can put it into a view.

select case when SOP30200.SOPTYPE=4 then
SOP30300.QUANTITY*-1
when left(SOP30200.CUSTNMBR, 4) = 'PSSI' and SOP30300.UOFM = 'CASE' and
left (IV00101.ITEMNMBR,2)='BK' then SOP30300.QUANTITY*50
when left(SOP30200.CUSTNMBR,4) and SOP30300.UOFM = 'CASE'
and left (IV00101.ITEMNMBR,2)='IV' then SOP30300.QUANTITY*50
when SOP30200.CUSTNMBR startswith 'PSSI' and SOP30300.UOFM = 'CASE'
and left (IV00101.ITEMNMBR,2)='IN' then SOP30300.QUANTITY*200
when SOP30200.CUSTNMBR startswith 'PSSI' and SOP30300.UOFM = 'CASE'
and left (IV00101.ITEMNMBR,2)='BO' then SOP30300.QUANTITY*50
when SOP30200.CUSTNMBR startswith 'PSSI' and SOP30300.UOFM = 'CASE'
and left (IV00101.ITEMNMBR,2)='BS' then SOP30300.QUANTITY*50
when SOP30200.CUSTNMBR startswith 'PSSI' and SOP30300.UOFM = 'CASE'
and left (IV00101.ITEMNMBR,3)='PI0' then SOP30300.QUANTITY*100
when SOP30200.CUSTNMBR startswith 'PSSI' and SOP30300.UOFM = 'CASE'
and left (IV00101.ITEMNMBR,3)='D3K' then SOP30300.QUANTITY*500
when SOP30200.CUSTNMBR startswith 'PSSI' and SOP30300.UOFM = 'CASE'
and left (IV00101.ITEMNMBR,3)='PS0' then SOP30300.QUANTITY*100
when SOP30200.CUSTNMBR startswith 'PSSI' and SOP30300.UOFM = 'CASE'
and left (IV00101.ITEMNMBR,3)='DBM' then SOP30300.QUANTITY*200  
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,2)='BK' then SOP30300.QUANTITY*50
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,2)='IV' then SOP30300.QUANTITY*50
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,2)='IN' then SOP30300.QUANTITY*200
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,2)='BO' then SOP30300.QUANTITY*50
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,2)='BS' then SOP30300.QUANTITY*50
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,3)='PI0' then SOP30300.QUANTITY*100
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,3)='D3K' then SOP30300.QUANTITY*500
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,3)='PS0' then SOP30300.QUANTITY*100
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,3)='DBM' then SOP30300.QUANTITY*200  
SOP30300.QUANTITY end
from
KLAI.dbo.SOP30200 INNER JOIN KLAI.dbo.SOP30300
ON SOP30200.SOPTYPE=SOP30300.SOPTYPE AND SOP30200.SOPNUMBE=SOP30300.SOPNUMBE
INNER JOIN KLAI.dbo.IV00101 ON SOP30300.ITEMNMBR=IV00101.ITEMNMBR
 WHERE  (SOP30200.SOPTYPE=3 OR SOP30200.SOPTYPE=4) AND SOP30200.VOIDSTTS=0
AND SOP30300.SALSTERR>='REGION 1' AND SOP30300.SALSTERR<='REGION 99'


0
 
LVL 1

Author Comment

by:apitech
ID: 35477209
Disregard asking for syntax help on that statement.  I fixed it.

I'm going to try to see if I can get this into code per angellll's suggestion.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question