Oracle Case Statement Ignoring Date TO_CHAR Formatting

I have a case statement where I'm assigning a date to a field that may or may not be null and the NVL is formatting correctly but the pull from the field as a CHAR is not formatting.

Example:
        case when to_char(b.original_effective_date, 'YYYYMMDD') is null then to_char(A.term_date, 'YYYYMMDD')
         when to_char(b.original_effective_date, 'YYYYMMDD') is not null then NVL(to_char(B.RECENT_TERM_DT),'99991231')
         else to_char(B.RECENT_TERM_DT, 'YYYYMMDD')
                     END  as revised_Member_Latest_End_Date,

The NVL(to_char(B.RECENT_TERM_DT),'99991231') is formatting correctly.

The to_char(B.RECENT_TERM_DT, 'YYYYMMDD') is not, it's coming back in the query formatted like this 31-DEC-99 instead of 99991231.

What am I missing?
SharonBernalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
if to_char(B.RECENT_TERM_DT, 'YYYYMMDD')  does not format a date into YYYYYMMDD format, please provide some sample data that demonstrates the problem.

Also, confirm the data types of your input values.



You might also consider simplifying your CASE to only have one data type conversion

TO_CHAR(
           CASE
               WHEN b.original_effective_date IS NULL THEN a.term_date
               WHEN b.original_effective_date IS NOT NULL THEN NVL(b.recent_term_dt, DATE '9999-12-31')
               ELSE b.recent_term_dt
           END,
           'YYYYMMDD')
           AS revised_member_latest_end_date,

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Pls try this-

case 
		when to_char(b.original_effective_date, 'YYYYMMDD') is not null then to_char(NVL(B.RECENT_TERM_DT,'99991231'))
		when to_char(b.original_effective_date, 'YYYYMMDD') is null then to_char(A.term_date, 'YYYYMMDD') 
        else to_char(B.RECENT_TERM_DT, 'YYYYMMDD')
        END  as revised_Member_Latest_End_Date,

Open in new window

0
johnsoneSenior Oracle DBACommented:
There isn't 3 cases.  Either a field is null or not null, there is no third option.  There shouldn't be any type conversion on the case because it is a waste of resource.  You should be able to do this:

case when b.original_effective_date is null then to_char(A.term_date, 'YYYYMMDD') 
     else nvl(to_char(B.RECENT_TERM_DT, 'YYYYMMDD'),'99991231')
END  as revised_Member_Latest_End_Date

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

johnsoneSenior Oracle DBACommented:
Actually, why even use a CASE?

nvl2(b.original_effective_date,
        nvl(to_char(B.RECENT_TERM_DT, 'YYYYMMDD'),'99991231'),
        to_char(A.term_date, 'YYYYMMDD')
    )

Open in new window

0
sdstuberCommented:
>>> There shouldn't be any type conversion on the case because it is a waste of resource.

I don't understand where you're going with that statement.  In order to get the required formatting, you must have a type conversion from date to text type.

Then, your own examples have multiple conversions in them.
0
johnsoneSenior Oracle DBACommented:
Sorry, I should have been clearer.  You shouldn't be putting the type conversion in the check.  The WHEN part of the CASE statement.

If COL1 is null, then TO_CHAR(COL1) is null.  There is no need for the TO_CHAR type conversion, it is a waste of a function call.
0
awking00Information Technology SpecialistCommented:
>>case when to_char(b.original_effective_date, 'YYYYMMDD') is null then to_char(A.term_date, 'YYYYMMDD')
when to_char(b.original_effective_date, 'YYYYMMDD') is not null then NVL(to_char(B.RECENT_TERM_DT),'99991231')
          else to_char(B.RECENT_TERM_DT, 'YYYYMMDD')
<<

When the original_effective_date is not null, then the NVL(to_char(B.RECENT_TERM_DT),'99991231') will always return something and the else condition is never met. What you are seeing is not the to_char(B.RECENT_TERM_DT, 'YYYYMMDD') being returned but when the B.RECENT_TERM_DT is not null, the first argument of the NVL statement, which is  is to_char(B.RECENT_TERM_DT), is being returned. You simply need to modify that first argument to to_char(B.RECENT_TERM_DT,'YYYYMMDD') and forget about the else portion.
0
SharonBernalAuthor Commented:
Thank you all for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.