Re: sql needs a flag

The round and sum are just in the wrong place. They should be
encapsulating the entire case statement.

ROUND(SUM( CASE WHEN lctype='RO' THEN totalland/olrqty else 0 end ), 2 )
NAME(roytotal) COLHDG("Royalties") LEN(11,2)

So you are correct that, it's zeroing out when it reads the LC type.

Do the same change for the LC type.


On Mon, Jan 11, 2016 at 5:43 PM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:

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

--
--buck

Visit wiki.midrange.com and register for an account. Edit a page that
helps you, and because it's public, you'll help someone else, too!

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.