Solved

Posted on 2011-04-27

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}

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}

4 Comments

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

Thank you.

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

Title | # Comments | Views | Activity |
---|---|---|---|

SQL Server Degrading on Write | 13 | 51 | |

Record open by another user | 6 | 24 | |

SSRS 2008 R2 legend colors not matching chart series colors | 4 | 7 | |

SQL Find correct record using ;WITH | 7 | 9 |

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

Connect with top rated Experts

**23** Experts available now in Live!