?
Solved

Result of a CASE how to convert numeric to character using to_char

Posted on 2008-09-30
7
Medium Priority
?
722 Views
Last Modified: 2013-12-19
Oracle DB

I have a table that the information is being exported out to be used by another vendor source.  I need a new column included in the exported data named "NewSC".  The value of NewSC should be the value of SVCCD in character form.  SVCCD is numeric. And if SVCCD is null then NewSC needs to contain a '0' vlaue and not null.

I used a CASE to populate the NewSC.  However the NewSC needs to be in character.  How do I achieve this? I tried to use the to_char but I get an error.

I have practically zero SQL exposure so I'm not sure if it is syntax that is wrong or something entirely different.

The code below runs without error and gives the results requested except the values in the NewSC are numeric and they need to be character.

SELECT  ACTICDMF.CMHOSP
      , ACTICDMF.RECID
      , ACTICDMF.SVCCD
      , case
            when ACTICDMF.SVCCD IS NULL
            then 0
           else ACTICDMF.SVCCD
        end as NewSC      
      , ACTICDMF.DESC
      , ACTICDMF.GLKEY
      , ACTICDMF.INSCD4
      , ACTICDMF.INSCD24
      , ACTICDMF.INSCD34
      , ACTICDMF.MRCPTC
      , ACTICDMF.MRMOD
      , ACTICDMF.MACPTC
      , ACTICDMF.MAMOD
      , ACTICDMF.OTCPTC
      , ACTICDMF.OTMOD
      , ACTICDMF.WCCPTC
      , ACTICDMF.WCMOD
      , ACTICDMF.PRICE1
      , ACTICDMF.PANEL
      , ACTICDMF.BLOOD
FROM WAREHOUSE.ACTICDMF ACTICDMF
WHERE (ACTICDMF.CMHOSP = 067)

Thanks in advance for input and assistance.  




0
Comment
Question by:mreid3847
[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
7 Comments
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 22606958
Try this
SELECT  ACTICDMF.CMHOSP
      , ACTICDMF.RECID
      , ACTICDMF.SVCCD
      ,  nvl(ACTICDMF.SVCCD,'0')
       , ACTICDMF.DESC
      , ACTICDMF.GLKEY
      , ACTICDMF.INSCD4
      , ACTICDMF.INSCD24
      , ACTICDMF.INSCD34
      , ACTICDMF.MRCPTC
      , ACTICDMF.MRMOD
      , ACTICDMF.MACPTC
      , ACTICDMF.MAMOD
      , ACTICDMF.OTCPTC
      , ACTICDMF.OTMOD
      , ACTICDMF.WCCPTC
      , ACTICDMF.WCMOD
      , ACTICDMF.PRICE1
      , ACTICDMF.PANEL
      , ACTICDMF.BLOOD
FROM WAREHOUSE.ACTICDMF ACTICDMF
WHERE (ACTICDMF.CMHOSP = 067);

                       
0
 
LVL 32

Expert Comment

by:awking00
ID: 22607493
to_char(nvl(ACTICDMF.SVCCD,'0'))
0
 

Author Comment

by:mreid3847
ID: 22617907
I need the keep the SVCCD in its current state and I also need to NewSC field, as it is what the specs are requesting.  That is why I used the CASE.

The NewSC field needs to be character.
0
 
LVL 10

Accepted Solution

by:
dbmullen earned 1000 total points
ID: 22620840
awking00 has it correct..
but what you have is pretty much correct as well, juas add the to_char

SELECT  ACTICDMF.CMHOSP
      , ACTICDMF.RECID
      , ACTICDMF.SVCCD
      , case
            when ACTICDMF.SVCCD IS NULL
            then '0'
           else to_char(ACTICDMF.SVCCD)
        end as NewSC      
...
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1000 total points
ID: 22623553
>>I need the keep the SVCCD in its current state and I also need to NewSC field, as it is what the specs are requesting.  That is why I used the CASE.

The NewSC field needs to be character.<<
The nvl function is basically a special form of case.
nvl(field,'0') is the same as case when field is null then '0' else field.

select ...
ACTICDMF.SVCCD,
to_char(nvl(ACTICDMF.SVCCD,'0')) as NewSC,
...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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

771 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