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)='PI 0' then {SOP30300.QUANTITY}*100 else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='D3 K' then {SOP30300.QUANTITY}*500 else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='PS 0' then {SOP30300.QUANTITY}*100 else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='DB M' 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)='PI 0' then {SOP30300.QUANTITY}*100 else
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='D3 K' then {SOP30300.QUANTITY}*500 else
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='PS 0' then {SOP30300.QUANTITY}*100 else
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='DB M' then {SOP30300.QUANTITY}*200 else
{SOP30300.QUANTITY}
if {SOP30200.SOPTYPE}=4 then
-{SOP30300.QUANTITY}else
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BK
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='IV
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='IN
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BO
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BS
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='PI
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='D3
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='PS
if {SOP30200.CUSTNMBR} startswith 'PSSI' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='DB
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BK
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='IV
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='IN
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BO
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},2)='BS
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='PI
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='D3
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='PS
if {SOP30200.CUSTNMBR} startswith 'BXTR' and {SOP30300.UOFM} = 'CASE' and left ({IV00101.ITEMNMBR},3)='DB
{SOP30300.QUANTITY}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
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'
when SOP30200.CUSTNMBR startswith 'PSSI' and SOP30300.UOFM = 'CASE'
and left (IV00101.ITEMNMBR,3)='D3K'
when SOP30200.CUSTNMBR startswith 'PSSI' and SOP30300.UOFM = 'CASE'
and left (IV00101.ITEMNMBR,3)='PS0'
when SOP30200.CUSTNMBR startswith 'PSSI' and SOP30300.UOFM = 'CASE'
and left (IV00101.ITEMNMBR,3)='DBM'
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'
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,3)='D3K'
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,3)='PS0'
when SOP30200.CUSTNMBR startswith 'BXTR' and SOP30300.UOFM = 'CASE' and left (IV00101.ITEMNMBR,3)='DBM'
SOP30300.QUANTITY end
from
KLAI.dbo.SOP30200 INNER JOIN KLAI.dbo.SOP30300
ON SOP30200.SOPTYPE=SOP30300.
INNER JOIN KLAI.dbo.IV00101 ON SOP30300.ITEMNMBR=IV00101.
WHERE (SOP30200.SOPTYPE=3 OR SOP30200.SOPTYPE=4) AND SOP30200.VOIDSTTS=0
AND SOP30300.SALSTERR>='REGION
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.
I'm going to try to see if I can get this into code per angellll's suggestion.
ASKER
I'm taking a Crystal report and putting it into SSRS. I just need that statement translated.
Thank you.