Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

handling nulls

Posted on 2009-04-03
6
Medium Priority
?
710 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
[X]
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
6 Comments
 

Author Comment

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

Accepted Solution

by:
Kent Olsen earned 2000 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
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24060908
Hi nav,

Check your spelling.  :)   COALESCE....

Kent
0
 

Author Comment

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

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

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

715 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