null values [message #396705] |
Tue, 07 April 2009 15:00  |
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   |
 |
BlackSwan
Messages: 26766 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  |
joy_division
Messages: 4963 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.
|
|
|