Re: SQL (and sqlplus) Help - Null values and calculated columns

In your query, you are doing an outer join which can lead to null values being returned. If you want to substitue a value for the null value, use the NVL function. For example if colA is null, return zero will be coded:
  SELECT NVL(colA,0) FROM sometable;

HTH,
Brian

smccurry_at_hotmail.com wrote:
>
> I am somewhat of a newbie to sql. I am having trouble adding two
> columns b/c of null values. I am trying to update columns that are
> null to 0 and then sum up a calculated column. see below for sql and
> report generated. Image Cost and Product Cost cannot be added when
> there is a null value. My update to 0 is not working, as well as my
> attempt to compute sum on the last column (computed). Any help
> appreciated - THANKS!
>
> (this column heading does not work)
> COLUMN X_IMAGE_COST+X_PRICE_LIST_COST FORMAT $999,999.99 HEADING
> 'TOTALS';
> rem
> (this compute sum on calculated column does not work)
> COMPUTE SUM OF X_PRICE_LIST_COST+X_IMAGE_COST ON REPORT;
>
> rem
> UPDATE S_PROD_INT
> SET X_IMAGE_COST = 0
> WHERE X_IMAGE_COST is null;
> UPDATE S_PRI_LST
> SET X_PRICE_LIST_COST = 0
> WHERE X_PRICE_LIST_COST is null;
> SELECT O.NAME, A.OWNER_ASSET_NUM, X.X_IMAGE_COST, R.X_PRICE_LIST_COST,
> (X.X_IMAGE_COST + R.X_PRICE_LIST_COST)
> FROM S_ASSET A, S_PROD_INT P, S_PROD_INT X, S_PRI_LST R, S_PRI_LST_ITEM
> I, S_ORG_EXT O
> WHERE O.NAME = 'Martian' AND A.STATUS_CD = 'Supported' AND O.ROW_ID =
> A.OWNER_ACCNT_ID (+) AND A.PROD_ID = P.ROW_ID (+) AND P.PROD_IMAGE_ID =
> X.ROW_ID (+) AND P.ROW_ID = I.PROD_ID (+) AND I.PRI_LST_ID = R.ROW_ID
> (+);
> ROLLBACK;
>
> (beginning of sqlplus session)
> SQL> @sql_prod
> ENTER A DEPARTMENT NAME...: Martian
> Input truncated to 17 characters
> 204 rows updated.
> 0 rows updated.
> XYZ Corporation
> Product Cost Report
> DEPARTMENT
> NAME ASSET# IMAGE COST PRODUCT COST X_IMAGE_COST+X_PRICE_LIST_COST
> ----- ------ ----------- ----------- -------------------------------
> Martian D012 null $187.50 null
> Martian D345 $92.50 $187.50 280
> Martian A123 null $125.00 null
> Martian B456 $35.00 $125.00 160
> Martian C789 $92.50 $125.00 217.5
> --------- ----------
> sum $220.00 $750.00
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Dec 15 1999 - 08:12:56 CST