We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

NVL in TO CHAR

GRChandrashekar
on
Medium Priority
634 Views
Last Modified: 2012-06-27
I have my query as below

/* Formatted on 26/04/2011 4:44:25 PM (QP5 v5.136.908.31019) */
SELECT TRANSACTIONMONTH
  FROM MONTHLYBILL
 WHERE TO_CHAR (TO_DATE (CREATEDDATE), 'MONTH-YYYY') =
          TO_CHAR (TO_DATE ('1/02/2011', 'DD/MM/YYYY'), 'MONTH-YYYY')
       AND EMPLOYEE_ID = 4

When TO_CHAR (TO_DATE ('1/02/2011', 'DD/MM/YYYY'), 'MONTH-YYYY') is passed NULL
I want to return the first available row in DB based on MIN(Createddate) and employee_id=4

IN addition, the ouput of above query is "FEBRUARY-2011"
I need one more column in output which returns first date of month following TRANSACTIONMONTH returned
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
First....

>>> TO_DATE (CREATEDDATE)

this is wrong,   if createdate is already a date, then don't apply to_date to it
if createdate is a string, then you should have an explicit mask to convert it with the appropriate format

assuming it's a date then try this...


 WHERE     createdate >= TRUNC(TO_DATE('1/02/2011', 'DD/MM/YYYY'), 'mm')
       AND createdate < ADD_MONTHS(TRUNC(TO_DATE('1/02/2011', 'DD/MM/YYYY'), 'mm'), 1)
       AND employee_id = 4;

it's a little bit more code but is much more efficient

if it's a string, then do the same thing except wrap createdate with to_date()  including the appropriate date mask




Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
for the actual NVL part

use the MIN analytic

nvl(transactionmonth,min(transactionmonth) over())

Author

Commented:
Can you please alter this query.

WHERE     createdate >= TRUNC(TO_DATE('1/02/2011', 'DD/MM/YYYY'), 'mm')
       AND createdate < ADD_MONTHS(TRUNC(TO_DATE('1/02/2011', 'DD/MM/YYYY'), 'mm'), 1)
       AND employee_id = 4;

And what about  one more column in output which returns first date of month following TRANSACTIONMONTH returned
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
first date of month following transaction month is


ADD_MONTHS(TRUNC(transactionmonth,'mm'), 1)
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> Can you please alter this query.

what do you want to alter about it?
it's not a full query, that's just a suggested where clause

Author

Commented:
I want this in output column
ADD_MONTHS(TRUNC(transactionmonth,'mm'), 1)
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> use the MIN analytic

actually this won't work, because the analytic will only return the min date within the where clause date range.

If you want the min date for the whole table, for that employee then you'll have to use a subquery.

I suggest using case or decode  instead of NVL because NVL always evaluates both fields, which you don't really want to do if the data isn't null

something like this...
(case when transactionmonth is null then (select min(transactionmonth) from monthlybill where employee_id = 4) else transactionmonth end)
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
http:#35466369

ok, use that function call exactly as shown as a column
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you want the generated transactionmonth (the one with the NULL handling) to be used in the next month call  then try something like this...


SELECT transactionmonth, ADD_MONTHS(TRUNC(transactionmonth, 'mm'), 1) nextmonth
  FROM (SELECT (CASE
                    WHEN transactionmonth IS NULL
                    THEN
                        (SELECT MIN(transactionmonth)
                           FROM monthlybill
                          WHERE employee_id = 4)
                    ELSE
                        transactionmonth
                END)
                   transactionmonth
          FROM monthlybill
         WHERE     createdate >= TRUNC(TO_DATE('1/02/2011', 'DD/MM/YYYY'), 'mm')
               AND createdate < ADD_MONTHS(TRUNC(TO_DATE('1/02/2011', 'DD/MM/YYYY'), 'mm'), 1)
               AND employee_id = 4);

Open in new window

Author

Commented:
SQL command not properly ended
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
the query above assumes createdate is, in fact, a date

if not, then modify as described previously
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> SQL command not properly ended

what query did you run?  It works fine for me when I tested it

Author

Commented:
Error SQL command not properly ended
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> Error SQL command not properly ended

what did you run? again, I tested what I posted, it works correctly for me

Author

Commented:
It was this one what u posted ID: 35466416
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
are you using Toad?  If so, do you have other queries in your editor window that may or may not have semicolons ?

Author

Commented:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

TRUNC(transactionmonth, 'mm'), 1) nextmonth


transactionmonth THIS FIELD STORES DATA LIKE FEBRUARY-2011, MARCH-2011 ETC
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
then you'll need to covert it to a date

TO_DATE(transactionmonth,'fmMONTH-yyyy')
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
the "fm" isn't really necessary

Author

Commented:
Perfect.  Now only problem is passing NVL which returns min and the first date following the ouput returned by min
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
that's already done in the query posted above

if not,  please post your final version of the query you are using

along with sample data and expected results

Author

Commented:
SELECT transactionmonth, ADD_MONTHS(TO_DATE(transactionmonth,'fmMONTH-yyyy'), 1) nextmonth

  FROM (SELECT (CASE
                    WHEN transactionmonth IS NULL
                    THEN
                        (SELECT MIN(transactionmonth)
                           FROM monthlybill
                          WHERE employee_id = 4)
                    ELSE
                        transactionmonth
                END)
                   transactionmonth
          FROM monthlybill
         WHERE     CREATEDDATE >= TRUNC(TO_DATE('1/02/2011', 'DD/MM/YYYY'), 'mm')
               AND CREATEDDATE < ADD_MONTHS(TRUNC(TO_DATE('1/02/2011', 'DD/MM/YYYY'), 'mm'), 1)
               AND employee_id = 4);

Now if i pass null dates in the last 2 lines, it wont return any record at all

Author

Commented:
expected result is when dates are null, it should return first available transactionmonth, ADD_MONTHS(TO_DATE(transactionmonth,'fmMONTH-yyyy'), 1) nextmonth
in DB for employee_ID based on MIN (createddate) in DB for that employeeID
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I meant "show me" expected results given some sample data

but...maybe I've misunderstood all along.

when you say null dates,  do you mean null in the data you are using as the where clause filters or do you mean null in the data in the table?

Author

Commented:
let us say in the where clause  i pass createddate as null

now let us say in DB the min(Createddated) is 4/1/2011, then output will be
January-2011 which is transaction month in DB (if present in DB else obvious output will be null)
and nextmonth will be 1/2/2011

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Genius
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.