Solved

NVL in TO CHAR

Posted on 2011-04-26
27
552 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
0
Comment
Question by:GRChandrashekar
[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
  • 16
  • 11
27 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466322
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




0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466344
for the actual NVL part

use the MIN analytic

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

Author Comment

by:GRChandrashekar
ID: 35466349
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
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 74

Expert Comment

by:sdstuber
ID: 35466351
first date of month following transaction month is


ADD_MONTHS(TRUNC(transactionmonth,'mm'), 1)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466355
>>> 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
0
 

Author Comment

by:GRChandrashekar
ID: 35466369
I want this in output column
ADD_MONTHS(TRUNC(transactionmonth,'mm'), 1)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466373
>>> 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)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466381
http:#35466369

ok, use that function call exactly as shown as a column
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466416
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

0
 

Author Comment

by:GRChandrashekar
ID: 35466421
SQL command not properly ended
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466423
the query above assumes createdate is, in fact, a date

if not, then modify as described previously
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466428
>>> SQL command not properly ended

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

Author Comment

by:GRChandrashekar
ID: 35466430
Error SQL command not properly ended
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466437
>>> Error SQL command not properly ended

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

Author Comment

by:GRChandrashekar
ID: 35466439
It was this one what u posted ID: 35466416
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466445
are you using Toad?  If so, do you have other queries in your editor window that may or may not have semicolons ?
0
 

Author Comment

by:GRChandrashekar
ID: 35466450
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

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


transactionmonth THIS FIELD STORES DATA LIKE FEBRUARY-2011, MARCH-2011 ETC
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466467
then you'll need to covert it to a date

TO_DATE(transactionmonth,'fmMONTH-yyyy')
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466478
the "fm" isn't really necessary
0
 

Author Comment

by:GRChandrashekar
ID: 35466484
Perfect.  Now only problem is passing NVL which returns min and the first date following the ouput returned by min
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466571
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
0
 

Author Comment

by:GRChandrashekar
ID: 35466604
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
0
 

Author Comment

by:GRChandrashekar
ID: 35466615
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35466686
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?
0
 

Author Comment

by:GRChandrashekar
ID: 35466736
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

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 35466846
yes, definitely SHOW, don't describe.

but, I "think" this is what you just said.

If not,  please SHOW me.  That is show me data, and show me results as data.
Remove all ambiguity of words and misunderstandings.
If this isn't correct, post data and I can replicate and results and I can test for.


SELECT transactionmonth, ADD_MONTHS(TO_DATE(transactionmonth, 'MONTH-yyyy'), 1) nextmonth
  FROM monthlybill
 WHERE     createdate >= NVL(TRUNC(TO_DATE(NULL, 'DD/MM/YYYY'), 'mm'),
                             (SELECT MIN(TO_DATE(transactionmonth, 'MONTH-yyyy'))
                                FROM monthlybill
                               WHERE employee_id = 4)
                            )
       AND createdate < ADD_MONTHS(NVL(TRUNC(TO_DATE(NULL, 'DD/MM/YYYY'), 'mm'),
                                       (SELECT MIN(TO_DATE(transactionmonth, 'MONTH-yyyy'))
                                          FROM monthlybill
                                         WHERE employee_id = 4)
                                      ),
                                   1
                                  )
       AND employee_id = 4;
0
 

Author Closing Comment

by:GRChandrashekar
ID: 35466918
Genius
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

632 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