Home » SQL & PL/SQL » SQL & PL/SQL » null values
null values [message #396705] Tue, 07 April 2009 15:00 Go to next message
nalifan
Messages: 4
Registered: March 2009
Junior Member
SELECT job_id, nvl(loaded_date,SYSDATE) AS "Loaded Date" 
FROM loaded_tables
WHERE job_id BETWEEN 21117 AND 21196


Result:
JOB_ID Loaded Date
-------- ----------------------
21184 26/09/2008 9:45:02 AM

repeated for all 121 records.

SELECT job_id, nvl(loaded_date,SYSDATE) AS "Loaded Date" 
FROM loaded_tables
WHERE job_id BETWEEN 21117 AND 21196
AND loaded_date BETWEEN '26-SEP-08' AND '07-APR-09'


Result:
JOB_ID Loaded Date
-------- ----------------------
21184 26/09/2008 9:45:02 AM

repeated for only 84 records.

The other 37 values that are missing have null values in the "loaded_date" field.

Is there a way to get those null values to display in a query where you specify a between clause?
Maybe something like the "%" wildcard.

I tried the nvl function, can't seem to make it work.

Is this possible?
Re: null values [message #396707 is a reply to message #396705] Tue, 07 April 2009 15:11 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>AND loaded_date BETWEEN '26-SEP-08' AND '07-APR-09'
For Oracle strings are delimited by single quote marks on each end.
'this is a string 2009-04-06 not a DATE'
When a DATE datatype is needed use TO_DATE() function

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.



or loaded_date is null

[Updated on: Tue, 07 April 2009 15:12]

Report message to a moderator

Re: null values [message #396709 is a reply to message #396705] Tue, 07 April 2009 15:33 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
If I understand your question, you will need to use a ROW GENERATOR to "show" the missing rows. Search for it.

And please do take the advice about DATEs and strings. too many people treat strings as DATEs. Best case is that it is sloppy. Bad case is it will fail with an error message. Worst case is it won't give you results that you think are correct but are not.
Previous Topic: Result of COUNT function back in Table Column
Next Topic: Compute Case Statement
Goto Forum:
  


Current Time: Thu Dec 08 00:13:52 CST 2016

Total time taken to generate the page: 0.13392 seconds