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

From: <smccurry_at_hotmail.com>
Date: Wed, 15 Dec 1999 02:17:52 GMT
Message-ID: <836tod$3v2$1@nnrp1.deja.com>

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 Tue Dec 14 1999 - 20:17:52 CST