Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2008-09-30
7
Medium Priority
?
739 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
5 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.

595 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