Re: SQL - Correction...

[Don Mellen <donm@ONTKO.COM>]

I thought this was mentioned before, but have you tried:

select
....
c.merchandise_amt - (select nvl(sum(amt_invoiced),0)
                     from ps_vchr_ln_po_vw
                     where business_unit = a.business_unit
                       and po_id=a.po_id and line_nbr=b.line_nbr
                       and sched_nbr=c.sched_nbr),
....

HTH,
Don Mellen


On Mon, 14 Sep 1998, Rakesh Maheshwari wrote:

> Hi,
>
> Here is a correction in description of the SQL. This SQL is not in SQR ,
> it is a SQL Create View Script in DataDesigner of PeopleSoft.
>
> Still I couldn't solve this SQL Problem.
> Help is needed.
>
> Thanks
> Rakesh
>
>
> > Hi,
> >
> > I'm working with SQR v 3.0.13.3 and Informix v 7.14
> >
> > Here I'm trying to execute SQL (SEE BELOW) for computing the field
> > c.merchandise_amt . But I don't get any value in this field expression
> > if the other SQL output is null (i.e. if don't meet the where clause
> and
> > output sum(amt_invoiced)= ' ').
> >
> > My Aim is to get the value of field c.merchandise_amt expression even
> > the other SQL gives the value nothing for sum(amt_invoiced).
> >
> >
> > *************************************************************
> > select
> > a.business_unit,
> > a.po_id,
> > b.line_nbr,
> > c.sched_nbr,
> > b.itm_setid,
> > b.inv_item_id,
> > b.itm_descr,
> > b.unit_of_measure,
> > c.price_po,
> > c.merchandise_amt - (select sum(amt_invoiced) from ps_vchr_ln_po_vw
> >                      where business_unit = a.business_unit and
> >                      po_id=a.po_id and line_nbr=b.line_nbr and
> >                      sched_nbr=c.sched_nbr),
> > c.qty_po ,
> > c.qty_po,
> > b.unit_of_measure,
> > c.price_po,
> > c.unit_prc_tol,
> > c.pct_unit_prc_tol,
> > c.ext_prc_tol,
> > c.pct_ext_prc_tol,
> > c.match_line_opt,
> > c.vchr_frt_appl_flg,
> > c.vchr_dsc_appl_flg,
> > c.vchr_utx_appl_flg,
> > c.vchr_stx_appl_flg,
> > b.m1099_cd,
> > b.m1099_sw,
> > c.distrib_mthd_flg,
> > a.currency_cd
> > from
> > ps_po_hdr_fs a,
> > ps_po_line_fs b,
> > ps_po_line_ship_fs c
> > where
> > a.business_unit = b.business_unit and
> > a.po_id = b.po_id and
> > b.business_unit = c.business_unit and
> > b.po_id = c.po_id and
> > b.line_nbr = c.line_nbr and
> > a.in_process_flg = 'N' and
> > ((a.po_status in ('D','X')) or
> > ((a.chng_ord_batch > 0) and
> > (a.po_status in ('A','PA','O'))) or
> > ((a.po_status = 'C') and
> > (a.match_action = 'N'))) and
> > a.ers_action = 'N' and
> > a.match_status_po in ('N','P','T') and
> > c.match_status_ln_po in ('N','P') and
> > c.cancel_status = 'A' and
> > b.cancel_status = 'A'
> > ***********************************************************
> >
> > For understanding to all I used word 'Other SQL' here is for SQL
> > statement:
> >                      (select sum(amt_invoiced) from ps_vchr_ln_po_vw
> >                      where business_unit = a.business_unit and
> >                      po_id=a.po_id and line_nbr=b.line_nbr and
> >                      sched_nbr=c.sched_nbr)
> > ***************************************************************
> >
> > Any help to solve this problem is highly appreciated.
> >
> > Thanks in advance,
> > Rakesh
> >
>

-----------------------------------------------------------------------
Donald Mellen  | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/
donm@ontko.com |  "In the beginning, there was nothing, which exploded"