CODEWITHSUNDEEP
sqloracle-databasecase
artemis
artemis

Reputation: 7241

How to add a new column based on another's value

I have the following SQL query which works great for pulling all of our purchased, and manufactured, parts:

WITH purchasedItems AS
(
    SELECT DISTINCT
        'BUY' as MAKE_BUY, PART_NUMBER AS NUMBER

    FROM
        PURCHASES

    WHERE
        ORDER_CREATED_DATE >= (SYSDATE-(365 * 3))
),

madeItems AS
(
        SELECT DISTINCT
            'MAKE' AS MAKE_BUY, FILE_PART_NUMBER AS NUMBER

    FROM
        OPERATIONS

    WHERE
        (FILE_CREATED_DATE >= (SYSDATE-(365*3)))
)

SELECT purchasedItems.*
FROM purchasedItems

UNION

SELECT madeItems.*
FROM madeItems

Which works well in producing data like: 

| MAKE_BUY | NUMBER    |
|----------|-----------|
| Buy      | 1215DE-AA |
| Buy      | 7545IO-EE |
| Buy      | 1214MN-QQ |
| Buy      | 1555XC-DD |
| Make     | 3232EW-UU |
| Make     | 4548GB-II |
| Make     | 7477LA-AA |
| Buy      | 9988NM-OO |
| Buy      | 5624QZ-QQ |
| Make     | 4444BA-BB |

However, I am looking for a twist. I need create a new column (like I did for MAKE_BUY) to add, essentially, an if statement that says:

if program_code LIKE '%SEERS%' THEN 'SEERS' as PROGRAM
ELSE IF program_code LIKE '%HP%' THEN 'HP' as PROGRAM

I have tried adding the following to my program:

(CASE WHEN (program_code LIKE '%SEERS%') THEN 'SEERS' as PROGRAM)

but that dd not work. I get an error ORA-00905: missing keyword.

I would like for it to essentially look like this:

| MAKE_BUY | NUMBER    | PROGRAM |
|----------|-----------|---------|
| Buy      | 1215DE-AA | SEERS   |
| Buy      | 7545IO-EE | HP      |
| Buy      | 1214MN-QQ | HP      |
| Buy      | 1555XC-DD | HP      |
| Make     | 3232EW-UU | HP      |
| Make     | 4548GB-II | HP      |
| Make     | 7477LA-AA | HP      |
| Buy      | 9988NM-OO | HP      |
| Buy      | 5624QZ-QQ | SEERS   |
| Make     | 4444BA-BB | HP      |

I have not found any resources that allow me to use a case-when statement in creating a new column.

Thanks for your help.

Upvotes: 0

Views: 66

Answers (3)

derek.wolfe
derek.wolfe

Reputation: 1116

You likely need to add the END keyword to the end of your case. Like so:

(CASE WHEN (program_code LIKE '%SEERS%') THEN 'SEERS' END as PROGRAM)

Upvotes: 0

forpas
forpas

Reputation: 164089

If you want to check only 2 conditions then I think this is what you are after:

case 
  when program_code like '%SEERS%' then 'SEERS' 
  when program_code like '%HP%' then 'HP'
end as PROGRAM

but this means that for any other case this column will be null.
In the results you posted I see only the value 'HP' and no values containing 'HP' as a substring.
If this is the case then you don't need: 

program_code like '%HP%'

just 

program_code = 'HP'

is more efficient.
The same applies to 'SEERS'.
So maybe you can simplify to:

case when program_code in ('SEERS', 'HP') then program_code end as PROGRAM

Upvotes: 0

hotfix
hotfix

Reputation: 3396

your case should look like that:

CASE 
  WHEN (program_code LIKE '%SEERS%') THEN 'SEERS' 
  when program_code LIKE '%HP%' THEN '%HP%'
  else '??'
end as PROGRAM

Upvotes: 1

Related Questions