Solved

handling nulls

Posted on 2009-04-03
6
673 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
As400 Db2 to MS Sql server (data/schema) only 6 337
Help with DB2 Create store procedure query 4 113
AS400 Forms 9 122
find age for two dates 5 52
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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