We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Nested and Complex If Then Statement

apitech
apitech asked
on
Medium Priority
276 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}
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.

Author

Commented:
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'


Author

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.