Re: SQL

[mahidhar puvvada <mpuvvada@HOTMAIL.COM>]

Hi,

Try using a "Group By" clause in the other SQL. U'r nested SQL would be
like this

select Col1, 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
group by Col1

Hope this helps

>Date:         Fri, 11 Sep 1998 18:27:25 -0400
>Reply-To: SQR-USERS@USA.NET
>From: Rakesh Maheshwari <Rakesh_Maheshwari@SCPS.K12.FL.US>
>Subject:      SQL
>To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
>
>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
>


______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com