Re: Newbie to SEQUEL

[Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>]

OK i see why, the group by i had to add pproyal there or else it would not
let me use it. that explains that.

On Tue, Jan 12, 2016 at 11:58 AM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

> I have never had the pleasure of working with the SEQUEL tool before.
> i added one table to this query, z1grtout. I need the Royalty amt from
> there not how it was doing.  This works but it adds a new ROYALTY col with
> the caps, and keeps the old one as well. I can't see here why
> it decides to display 2 columns and so it doesn't have room for the
> landded costs col as well. baffled. do you see here why it would be nmaking
> two Royalty columns?
>
> SELECT   olsrom, olorno, olline, olprdc, oldesc, pproyal,
>
>             olrqty COLHDG("Qty Rec") LEN(11,0), WDATA(lctype),
>
>             SUM(olamou/olrqty) LEN(11,2) NAME(unit) COLHDG("Unit Price"),
>
>             CASE WHEN pproyal>0 THEN ROUND((pproyal),2) ELSE 0 END
>
>               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),
>
>             (pproyal+lctotal+unit) COLHDG("Landed Cost") NAME(totallc)
> LEN(11,
>               2),
>
>             WDATA('&&yesterday')
>
>  FROM     sroorppl, landcost1, z1grtout
>
>  JOIN     grorno=olorno AND grline=olline AND grorno=pporno AND
> grline=ppline
>  GROUP BY olsrom, olorno, olline, olprdc, oldesc, olrqty, lctype, pproyal
>
>  ORDER BY olsrom, olorno, olline