Passing Case Expression to a IN Operator

Analytics
Enthusiast

Passing Case Expression to a IN Operator

Hi,

How do we pass a Case expression to a IN Operator

Select * from table
where col1 in (case when parameter is null then Col1 else parameter)

Parameter in this case has a value = 'A','B','C'

Sam
6 REPLIES
Junior Supporter

Re: Passing Case Expression to a IN Operator

Maybe something like this:

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
FROM MY_DB.PRUEBA01
ORDER BY ID_N;

*** Query completed. 10 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

ID_N C_TXT
----------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
6 SEIS
7 SIETE
8 OCHO
9 NUEVE
10 DIEZ

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
FROM MY_DB.PRUEBA01
WHERE COALESCE('1,2,5', TRIM(id_n)) LIKE '%'|| TRIM(id_n) || '%'
;

*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

ID_N C_TXT
----------- -------------------------
2 DOS
5 CINCO
1 UNO

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
FROM MY_DB.PRUEBA01
WHERE COALESCE('3,4,6', TRIM(id_n)) LIKE '%'|| TRIM(id_n) || '%';

*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

ID_N C_TXT
----------- -------------------------
3 TRES
4 CUATRO
6 SEIS

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
FROM MY_DB.PRUEBA01
WHERE COALESCE(NULL, TRIM(id_n)) LIKE '%'|| TRIM(id_n) || '%'
;

*** Query completed. 10 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

ID_N C_TXT
----------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
6 SEIS
7 SIETE
8 OCHO
9 NUEVE
10 DIEZ

HTH

Cheers.

Carlos.
Enthusiast

Re: Passing Case Expression to a IN Operator

Hi Carlos,

This is an excellent solution. However you are passing numeric values , how do we do it with character values.

So instead of 1,2,3 how do we use 'A','B','C'

Sam
Junior Supporter

Re: Passing Case Expression to a IN Operator

>>"However you are passing numeric values , how do we do it with character values."

Just the same as with numbers ;-).

SELECT *
FROM MY_DB.PRUEBA01
WHERE COALESCE('A,B,C', TRIM(id_C)) LIKE '%'|| TRIM(id_C) || '%';

Cheers.

carlos.
Enthusiast

Re: Passing Case Expression to a IN Operator

Did you try that out ? It does not work for me. If it's a number it works fine with character it does not.

Shouldn't character values be quoted in single quotes ? I tried with single quotes as well but still won't work.
Junior Supporter

Re: Passing Case Expression to a IN Operator

I'll try it for you:

SELECT * FROM MY_DB.PRUEBA02 ORDER BY ID_C;

*** Query completed. 5 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

ID_C C_TXT
---- -------------------------
A Letra A
E Letra E
I Letra I
O Letra O
U Letra U

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
FROM MY_DB.PRUEBA02
WHERE COALESCE('A,I,U', TRIM(ID_C)) LIKE '%'|| TRIM(ID_C) || '%';

*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

ID_C C_TXT
---- -------------------------
U Letra U
A Letra A
I Letra I

If you want to concat the letters to select one by one:

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
FROM MY_DB.PRUEBA02
WHERE COALESCE('A'|| ',' || 'I' || ',' || 'U', TRIM(ID_C)) LIKE '%'|| TRIM(ID_C) || '%';

*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

ID_C C_TXT
---- -------------------------
U Letra U
A Letra A
I Letra I

Cheers.

Carlos.
Enthusiast

Re: Passing Case Expression to a IN Operator

Thanks Carlos!

I was giving a different column name which is why the query was not returning anything.After the corrections the query works great.