Home » SQL & PL/SQL » SQL & PL/SQL » populate values when "not found" condition occurs
populate values when "not found" condition occurs [message #378256] Mon, 29 December 2008 08:46 Go to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
I have 2 tables (table-A & table-B). They are defined as such:

TABLE-A

DEPARTMENT
ACCOUNT

TABLE-B

DEPARTMENT
ACCOUNT
SPEND
BUDGET

TABLE-A is my driver for my query as it has all the departments and accounts, whereas TABLE-B only has records for the departments and accounts that have either a spend or a budget amount associated with it.

I need to report on all department / accounts regardless if there is a spend or budget amount, thus I know I need to do an outer join such as:

select a.department, a.account, b.spend, b.budget
from table-a a LEFT OUTER JOIN table-b b on
a.department = b.department and
a.account = b.account

my problem is HOW DO I INSERT VALUES of zero FOR SPEND & BUDGET when there isn't a record in table-b ???

many many thanks!
Re: populate values when "not found" condition occurs [message #378260 is a reply to message #378256] Mon, 29 December 2008 09:16 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
my problem is HOW DO I INSERT VALUES of zero FOR SPEND & BUDGET when there isn't a record in table-b ???

Insert where? If you are meaning the result of that query, it returns NULLs for rows not existing in table-B. Use NVL or COALESCE function to show something else.
Re: populate values when "not found" condition occurs [message #378273 is a reply to message #378256] Mon, 29 December 2008 12:29 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Do you mean nvl(spend,0) ???
Previous Topic: How to execute a procedure from trigger?
Next Topic: Need whole ( system date )month production
Goto Forum:
  


Current Time: Thu Dec 08 04:11:32 CST 2016

Total time taken to generate the page: 0.16381 seconds