Solved

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

Posted on 2008-09-30
7
714 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 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 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 32

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Queries 15 45
database upgrade 8 104
oracle- set role and grant privileges 6 38
Oracle - SQL Query with Function 3 50
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…
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

789 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