Re: sql needs a flag

On 1/11/2016 5:05 PM, Hoteltravelfundotcom wrote:

This is query created from SEQUEL.

My first reaction is to advise you to call SEQUEL support.

I think I need a CTE for the following scenario.
we have an order with 3 rows, the first has 'RO' in LCTYPE, the next 2 have
LC. I suspect the RO amt, is getting zero'd by the LC statement. so would
like have a flag here is the prev row had an RO - don't zero out. what is
the way to do this here?

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)

I don't know if the GROUP BY is implied by SEQUEL or omitted on purpose.

You could try a CTE based on this sketch:

with royalties as (
select olorno, sum(...) as royalty
from...
where olorno = :order
and lctype = 'RO')
--and again for landed
select ...,
royalty, landed
from...
left join royalties using(olorno)
--and again for landed
where olorno = :order
order by...

You could also try a subselect:

select ...,
(select sum(...)
from... inner
where outer.olorno = inner.olorno
and = lctype = 'RO')
--and again for landed
from... outer
order by...