NVL Function for Date field

[Vinodsrvk]

while am executing the following query

select nvl(create_date_time, sysdate)
from bucomments
where lseqno = '19068'
/
even I am using the nvl function am getting the 'no row selected' message.

Help me in this, Thanks in advance.

[amitpatel66]

while am executing the following query

select nvl(create_date_time, sysdate)
from bucomments
where lseqno = '19068'
/
even I am using the nvl function am getting the 'no row selected' message.

Help me in this, Thanks in advance.

Check if you have data for the Iseqno = '19068'

[Vinodsrvk]

Check if you have data for the Iseqno = '19068'

Yeah, I don't have data for the Iseqno=19068.
well, I need to display for the result as sysdate if records dosen't match with the Iseqno.

[amitpatel66]

Yeah, I don't have data for the Iseqno=19068.
well, I need to display for the result as sysdate if records dosen't match with the Iseqno.

Well, NVL is a SINGLE ROW FUNCTION which will be executed once for each row. Now if your query does not fetch any record, then the output will be no rows returned.

If you want to display the SYSDATE, particularly for the above Query where Iseqno = 19068, then try the following:

SELECT create_date FROM table_name WHERE Iseqno = 19068
UNION ALL
SELECT SYSDATE FROM dual
 

The above code might not satisfy your requirement completely, but as per yur request above, it will work!!

[Saii]

Why do you need a create date as sysdate for a record which does not even exist in table?