Re: SQL

[Wayne Ivory <wivory@WSL.COM.AU>]

How about selecting c.merchandise_amt and sum(amt_invoiced) as separate expressions.  That way you can test if your sum returns a null before deciding to perform any arithmetic.

Wayne Ivory
Information Services
Westralian Sands Limited


>>> Rakesh Maheshwari <Rakesh_Maheshwari@SCPS.K12.FL.US> 12/9/98 6:27:25 am >>>
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