Re: SQL - Correction...

[Charu Srinivas <C._Srinivas/dsisys/US@DSISYS.COM>]

Hi Rakesh!

Try using the nvl command in the code so that when the Other SQL evaluates
to NULL, a value of zero will be assigned to it and the expression will be
evaluated correctly.

This nvl command assigns a particular value to the variable when it
evaluates to NULL.

Here's the suggested code:

 c.merchandise_amt - nvl((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), 0),

This is just a thought. Hope this helps.

Charu





Rakesh_Maheshwari@SCPS.K12.FL.US on 09/14/98 09:29:47 AM

Please respond to SQR-USERS@USA.NET

To:   SQR-USERS@list.iex.net
cc:    (bcc: C. Srinivas/dsisys/US)
Subject:  SQL - Correction...




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
>