[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 728
  • Last Modified:

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

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
mreid3847
Asked:
mreid3847
2 Solutions
 
Shaju KumbalathCommented:
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
 
awking00Commented:
to_char(nvl(ACTICDMF.SVCCD,'0'))
0
 
mreid3847Author Commented:
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
 
dbmullenCommented:
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
 
awking00Commented:
>>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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now