0

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

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

  2. 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
Improve this question
6
  • 1
    Please show more sample data. If you require the two columns which you have aliased in those two CASE expressions, then it isn't clear why you want just a single column. Commented May 7, 2018 at 10:37
  • Are you aware (I guess you are, but had to ask) that % functions as a wildcard, meaning that 123abcdefg% and 123456789dfdgffdg will both match 123% (in connection to a like). Commented May 7, 2018 at 10:38
  • @TimBiegeleisen I added some more data. Please check. Commented May 7, 2018 at 10:47
  • @Gops AB: No, what Tim says is that we don't understand how you want two things at a time returned from case when. So please show the expected result, i.e. not only a list of strings, but also the columns the result shall contain. Will there be a model column? Will there be a genre column? Will there be another column combining these somehow? Please show. Commented May 7, 2018 at 10:55
  • @ThorstenKettner I added expected output columns. Please check and let me know. Yes, there will be a model column and a genre column. No combined columns. Commented May 7, 2018 at 14:58

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

4 Comments

Great, Looking good. Let me try this and get back to you immediately
How do you fit two results into a single field? I'm asking because, theoretically, they could both return a result, can't they?
Gordon, Your solution used regex which is a lot way better than what I thought. But still it does two checks. Isn't?
@GopsAB . . . You could do one check if you put the expression in the subquery.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.