handling nulls

Posted on 2009-04-03
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?


from aa
Question by:nav29
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3

Author Comment

ID: 24060362
even coalese dont work
LVL 45

Accepted Solution

Kent Olsen 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,

Author Comment

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.


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:
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

LVL 45

Expert Comment

by:Kent Olsen
ID: 24060908
Hi nav,

Check your spelling.  :)   COALESCE....


Author Comment

ID: 24061117
my bad thanks
LVL 45

Expert Comment

by:Kent Olsen
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,

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

627 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