CODEWITHSUNDEEP
sqldb2
BerBoort
BerBoort

Reputation: 11

Need to code to skip one line of query based on previous value

IN the below code, we have a problem when the row is a Royalty row, but doesn't have LC then the lack of LC will wipe out the amount displayed from this part:

CASE WHEN lctype='RO' THEN ROUND(SUM(totalland.olrqty),2) ELSE 0  
                  END NAME(roytotal) COLHDG("Royalties") LEN(11,2),

How would I code that, if LCTYPE = "RO" then don't check the next line if its = 'LC' ? As what happens here is this column totalland/olrqty) will get zero'd when LC but if previous was RO we dont want to zero.

SELECT   olsrom, olorno, olline, olprdc, oldesc,                             
            olrqty COLHDG("Qty Rec") LEN(11,0), WDATA(lctype),                
            SUM(olamou.olrqty) LEN(11,2) NAME(unit) COLHDG("Unit Price"),     
            CASE WHEN lctype='RO' THEN ROUND(SUM(totalland.olrqty),2) ELSE 0  
              END NAME(roytotal) COLHDG("Royalties") LEN(11,2),               
            CASE WHEN lctype='LC' THEN ROUND(SUM(totalland.olrqty),2) ELSE 0  
              END NAME(lctotal) COLHDG("Add-on Cost") LEN(11,2),              
            (roytotal+lctotal+unit) COLHDG("Landed Cost") NAME(totallc)       
              LEN(11,2)

Upvotes: 1

Views: 45

Answers (1)

David Bachmann Jeppesen
David Bachmann Jeppesen

Reputation: 395

You can use the LAG function to check a value in the previous row.

EDIT (if ParFait is right):

Just add...

OR lctype = 'RO'

...to that next line:

CASE WHEN lctype='LC' OR lctype = 'RO'
THEN ROUND(SUM(totalland.olrqty),2)
ELSE 0
END NAME(lctotal) COLHDG("Add-on Cost") LEN(11,2),

Upvotes: 1

Related Questions