Case when statement SQL

Question

I am facing some difficulties for a Datawarehouse transformation task, I have some source columns which are coming in varchar format, data contained: Blanks, -, decimal numbers such as (1234.44).

Those columns in target are declared as number.

I am trying to treat that data with this code but I keep receiving invalid number error:

 CASE WHEN
        LENGTH(TRIM(TRANSLATE(column78input, '-', ' '))) is null then null 
    WHEN column78input IS NULL THEN 0
     else to_number(column78input)
END

In first when statement I am trying to check if there is - in source, it returns null when found, and if you find it then place it as null (replacing dashes with nulls in essence)

In second when statements I am trying to treat those blanks, I thought that they might cause the error

And finally in else statement I want to parse it from varchar to number to load in target table.

If someone has some kind of suggestion, please help!

Thanks

Answer 1

Try with

CASE
    WHEN INSTR(column78input, '-') > 0 OR column78input IS NULL THEN 0
    ELSE TO_NUMBER(REPLACE(column78input, ' '))
END

INSTR returns the first position of a character in a string. So if there is no dash, it would return 0. A value greater than 0 means there is at least one dash in the string.

Here are a few mistakes in your code :

• A case when statement will exit when a condition is met. So you can remove the dash in the first condition and expect it to continue to process your string in the next condition. In your code if a string had a dash, the result would be null.
• LENGTH function returns the number of characters in a string. It will return a null value only if the string is null. So it's easier to directly write column78input IS NULL
• You current first condition is basically this : "After replacing the dash by a space and removing all the leading/trailing spaces, if the string is null then". Because you are replacing the dashes in the string, you can't check if there is an occurrence or not.