nickpick nickpick - 2 months ago 7
SQL Question

Best way to use variables that contain lists in Oracle SQL

I have around 5 long SQL queries and would like to replace some of the values with variables, so I can enter them directly in the script at the top (no pop ups).

What is the best way to achieve this (and now comes the part that makes this question not a duplicate), so that I can also have variables that are lists. For example here is how the queries should differ between configuration 1 and 2:

WHERE TRD_SRC_SYS ='blabla1'
WHERE TRD_SRC_SYS ='blabla223'

Select 'blabla1- NEW' as REC_SYSTEM,
Select 'blabla223- NEW' as REC_SYSTEM,

WHERE TRD_SRC_SYS in ('MAG','LLELLE')
WHERE TRD_SRC_SYS ='MA'


Independent of that I would also like to set the date in one place, so I don't have to change it everywhere it occurs in the query. (but I believe this is explained in other posts)

Any suggestions are appreciated.

Answer

You can use substitution variables like:

define sys_list = "'MAG','LLELLE'"

Then write the query like:

... where TRD_SRC_SYS in (&sys_list.)

It will resolve to:

... where TRD_SRC_SYS in ('MAG','LLELLE')

When you just need one value:

define sys_list = "'MA'"

... the query will resolve to

... where TRD_SYS in ('MA')

which means the same as

... where TRD_SYS = 'MA'

Script example

SQL> select * from t1;

TRD_SRC_SYS
------------------------------
MA
MAG
LLELLE

Create a script called script.sql with the contents:

select * from t1
where TRD_SRC_SYS in (&1.);

Run the script with different parameters:

SQL> @script "'MA'"
old   1: select * from t1 where TRD_SRC_SYS in (&1.)
new   1: select * from t1 where TRD_SRC_SYS in ('MA')

TRD_SRC_SYS
------------------------------
MA

SQL> @script "'MAG','LLELLE'"
old   1: select * from t1 where TRD_SRC_SYS in (&1.)
new   1: select * from t1 where TRD_SRC_SYS in ('MAG','LLELLE')

TRD_SRC_SYS
------------------------------
MAG
LLELLE
Comments