Solved

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

Posted on 2008-09-30
7
702 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
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 31

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 250 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 31

Assisted Solution

by:awking00
awking00 earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now