>I have two tables: tCredit and tDebit. Each has three fields: cust_id1, cust_id2, amt. Want to produce a table that has cust_id1, cust_id2, credit_amt, debit_amt.
>
>There are cust_id1/cust_id2 records in tCredit that are not tDebit and visa versa. So they would have 0 in the non-matching amount field.
>
>I tried a FULL OUTER JOIN but could not get the cust_id1/cust_id2 to have a value for both the tCredit and tDebit non-matches.
>
>Thanks
>
>Brenda
Try using the NVL() function in the Amt fields. When the two don't match, a NULL value is returned. You can use NVL() to return a 0 in those cases. See help for more info.
Ex.
select nvl(.NULL.,0) as 'NewColumn' from MyTable && returns 0
HTH.
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao