Link to home
Start Free TrialLog in
Avatar of apitech
apitech

asked on

Nested and Complex If Then Statement

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}
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of apitech
apitech

ASKER

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.
Avatar of apitech

ASKER

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'


Avatar of apitech

ASKER

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.