Oracle SQL - Multiple return from case

Question

I may be trying it wrong. I am looking for any approach which is best.

Requirement:

My Query joins 4-5 tables based on few fields.

I have a column called product id. In my table there are 1.5 million rows. Out of those only 10% rows has product ids with the following attribute

A300X-%
A500Y-%

300,500, 700 are valid model numbers. X and Y are classifications. My query picks all the systems.

I have a check as follows

CASE
WHEN PID LIKE 'A300X%'
THEN 'A300' 
...
END AS MODEL

Similarly

CASE
WHEN PID LIKE 'A300X%'
THEN 'X'
...
END AS GENRE

I am looking for the best option from the below

1. How do I Combine both case statement and add another[third] case which will have these two cases. i.e

CASE 
    WHEN desc in ('AAA')
         First Case
         Second Case
    ELSE
         don't do anything for other systems
    END

2. Is there any regex way of doing this? Before first - take the string. Look for X, Y and also 300,500,700.

3. Is there any other way of doing this? Or doing via code is the best way?

Any suggestions?

EDIT:

Sample desc:

AAA,
SoftwARE, 
sw-app

My query picks all the desc. But the case should be running for AAA alone.

And Valid models are

A300X-2x-P
A500Y-5x-p
A700X-2x-p
A50CE-2x-P

I have to consider only 300,500,700. And the above two cases.

Expected result:

MODEL     GENRE
A300      X
A500      Y
A300      Y

Answer 1

Based on your sample data, logic such as this would work:

(CASE WHEN REGEXP_LIKE(PID, '^A[0-9]{3}[A-Z]-')
      THEN SUBSTR(PID, 1, 4)
      ELSE PID
 END) AS MODEL

(CASE WHEN REGEXP_LIKE(PID, '^A[0-9]{3}[A-Z]-')
      THEN SUBSTR(PID, 5, 1)
      ELSE PID
 END) AS GENRE

This assumes that the "model number" always starts with "A" and is followed by three digits (as in your example data). If the model number is more complicated, you may need regexp_substr() to extract the values you want.

Answer 2

Q: How do I Combine both CASE statement expressions

Each CASE expression will return a single value. If the requirement is to return two separate columns in the resultset, that will require two separate expressions in the SELECT list.

For example:

DESC  PID           model_number  genre
----  ----------    ------------  ------
AAA   A300X-2x-P    300           X 
AAA   A500Y-5x-p    500           Y
AAA   A700X-2x-p    700           X
AAA   A50CE-2x-P    (NULL)        (NULL)
FOO   A300X-2x-P    (NULL)        (NULL)

There will need to be an expression to return the model_number column, and a separate expression to return the genre column.

It's not possible for a single expression to return two separate columns.

---

Q: and add another[third] case which will have these two cases.

A CASE expression returns a value; we can use a CASE expression almost anywhere in a SQL statement where we can use a value, including within another CASE expression.

We can also combine multiple conditions in a WHEN test with AND and OR

As an example of combining conditions and nesting CASE expressions ditions...

 CASE 
 WHEN (    ( t.PID LIKE '_300%'  OR t.PID LIKE '_500%'  OR t.PID LIKE '_700%' )
       AND ( t.DESC = 'AAA' )
      )
 THEN CASE 
        WHEN ( t.PID LIKE '____X%' )
        THEN 'X'
        WHEN ( t.PID LIKE '____Y%' )
        THEN 'Y'
        ELSE NULL
      END
 ELSE NULL
 END AS `genre`

There are other expressions that will return an equivalent result; the example shown here isn't necessarily the best expression. It just serves as a demonstration of combining conditions and nesting CASE expressions.

Note that to return another column model we would need to include another expression in the SELECT list. Similar conditions will need to be repeated; it's not possible to reference the WHEN conditions in another CASE expression.