SQL Function to Determine if a Set of Fields Meet Some Criteria

I’m working with a DB2 database, and was wondering whether a function exists that allows me to determine whether a set of columns meet some criteria.

I can simply use OR to accomplish this, however, I’m wondering whether there exists a more sophisticated way.

For example, this works:

Select UNIQUE_ID
, CASE WHEN FIELD1 IN (SELECT DISTINCT FIELDS FROM TAB2) OR
FIELD2 IN (SELECT DISTINCT FIELDS FROM TAB2) OR
FIELD3 IN (SELECT DISTINCT FIELDS FROM TAB2) OR
FIELD4 IN (SELECT DISTINCT FIELDS FROM TAB2) OR
FIELD5 IN (SELECT DISTINCT FIELDS FROM TAB2) THEN 1 ELSE 0 END AS FINAL_FIELD
FROM TAB1

But I am looking for SOME_FUNCTION as in the below:

Select UNIQUE_ID
, CASE WHEN SOME_FUNCTION(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) IN (SELECT DISTINCT FIELDS FROM TAB2) THEN 1 ELSE 0 END AS FINAL_FIELD
FROM TAB1

The idea is to avoid stretching out the case statement if it is not needed. Any help would be great!

Answer

You can use exists instead of in:

(case when exists (select 1
                   from tab2
                   where fields in (tab1.field1, tab1.field2, tab1.field3, tab1.field4, tab1.field5)
                  )
      then 1 else 0
 end)

Advertisement

Answer