Solved

handling nulls

Posted on 2009-04-03
6
665 Views
Last Modified: 2012-05-06
Hi there,
I am trying to put 1800-01-01 if date is null else populate what ever date is there
seems like it dont like nvl

Any idea why?

Thanks

SELECT
nvl(DTL_REC_CRE_DATE,'1800-01-01')
,nvl(DTL_UPDT_DATE,'1800-01-01')
from aa
0
Comment
Question by:nav29
  • 3
  • 3
6 Comments
 

Author Comment

by:nav29
ID: 24060362
even coalese dont work
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 24060522
Hi nav,

Coalesce should work just fine.  That's its purpose.

SELECT coalesce (DTL_REC_CRE_DATE, '1800-01-01'), coalesce (DTL_UPDT_DATE, '1800-01-01')
FROM aa;

The only restriction is that every "parameter" to the coalesce function must return compatible data types.  A properly formatted date string should suffice as a date.


Good Luck,
Kent
0
 

Author Comment

by:nav29
ID: 24060860
Data type is date
but getting errors

SQL0440N  No authorized routine named "COALESE" of type "FUNCTION" having
compatible arguments was found.  SQLSTATE=42884

SQL0440N  No authorized routine named "COALESE" of type "FUNCTION                                                      " having compatible arguments was found.

Explanation:

This occurs in a reference to routine "<routine-name>", when the
database manager cannot find a routine it can use to implement
the reference. There are several reasons why this could occur:
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 45

Expert Comment

by:Kdo
ID: 24060908
Hi nav,

Check your spelling.  :)   COALESCE....

Kent
0
 

Author Comment

by:nav29
ID: 24061117
my bad thanks
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24061188
Hi nav,

It's amazing how many people miss this subtlety about the COALESCE function, but it can take more than 2 arguments.  The function searches the parameter list and returns the first value that is not NULL.  Most people use it to convert a single value to non-null.

  SELECT  coalesce (accounting_date, posting_date, transaction_date, current_date) FROM mytable;

That query will check the accounting_date, posting_date, and transaction_date for each row and return the first one in the list that is not NULL.  If they are all NULL, the current date is returned.


Good Luck,
Kent
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now