[DB2 ver 9.1 z/OS] IN predicate with multiple columns

humble_man · #1 (**permalink**) 11-27-11, 07:11

Hi All,

I need to select some rows by using IN predicate with multiple columns.

Code:

SELECT TYP, ID FROM MYVIEW WHERE (TYP, ID) IN (VALUES ('1','1'),('2','2'),('3','3'))

I got an error:

Quote:

ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE HOURS. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.8.86

Is the syntax wrong ?

Can it be a JDBC driver problem ?

How can I rewrite it without using bunch of OR inside WHERE ?

Thank you,
Humble Man

tonkuma · #2 (**permalink**) 11-27-11, 08:23

What are the data type of TYP and ID?

humble_man · #3 (**permalink**) 11-27-11, 08:34

Here is an excerpt from the DDL:

Quote:

"TYP" CHAR(5) FOR SBCS DATA WITH DEFAULT NULL,
"ID" CHAR(7) FOR SBCS DATA WITH DEFAULT NULL,

tonkuma · #4 (**permalink**) 11-27-11, 08:57

Multi row constructor "VALUES (...) , (...) , ..." is not supported on DB2 for z/OS.

So, please try to replace VALUES clause with "SELECT ... sysibm.sysdummy1 UNION ALL ...".

humble_man · #5 (**permalink**) 11-27-11, 09:46

Tonkuma, thank you.

Could you show how the original query (from first post) will look like ? I am a programmer, so having difficulties with syntax.

Is it OK to use sysibm.sysdummy1 table in production ?

Best regards,
Humble man.

tonkuma · #6 (**permalink**) 11-27-11, 10:37

Quote:

... I am a programmer, ...

SQL is a programming language.
If you are a programmer and you are requested to use SQL,
you should learn it and get (at least) average skill of it.

Quote:

... having difficulties with syntax.

It's a basic skill for a programmer to study a programming language.
If you couldn't read and understand C(or any other programming language) syntax,
you can't be a C expert.
You can't be more than mediocre programmer.

Quote:

Is it OK to use sysibm.sysdummy1 table in production ?

No problrm, I think.

Anyhow, try this...

Code:

SELECT typ, id
 FROM  myview
 WHERE (typ, id)
       IN (SELECT '1' , '1' FROM sysibm.sysdummy1 UNION ALL
           SELECT '2' , '2' FROM sysibm.sysdummy1 UNION ALL
           SELECT '3' , '3' FROM sysibm.sysdummy1
          )
;

humble_man · #7 (**permalink**) 11-27-11, 11:06

Thank you, Tonkuma

Could you tell what is advantage (if any) of UNION ALL and SYSDUMMY1 approach, compared to the following query:

Code:

SELECT TYP, ID FROM MYVIEW WHERE
(TYP='1' AND ID='1') OR
(TYP='2' AND ID='2') OR
(TYP='3' AND ID='3')

Thank you,
Humble Man

tonkuma · #8 (**permalink**) 11-27-11, 11:56

Access path might be different.
Using OR would be less chance to use index scan.

But, it depends on many factors,
for example, number of rows in a table, created(and exist) indexes, result of runstats, so on...

You can examine the access path by explain.

I don't konw much about DB2 z/OS,
so please read manuals or Information Center for tuning your query.
For example:
DB2 9 - Performance - Tuning your queries
DB2 9 - Performance - Boolean term predicates
DB2 9 - Performance - Summary of predicate processing

humble_man · #9 (**permalink**) 11-27-11, 12:04

Thank you very much !

dav1mo · #10 (**permalink**) 11-28-11, 13:21

I would much prefer using a CTE, than hit against sysdummy1. In fact, I, typically, tell folks to stay away from sysdummy1 in anything other than an adhoc basis, as most times it is misused and costs you more.
Try something like:

Code:

WITH CTE(typ,id)
   AS ( VALUES
 ('1','1')
,('2,'2')
,('3','3'))
SELECT * FROM MYVIEW MV
WHERE mv.typ = cte.typ
   and mv.id = cte.id

Dave Nance

humble_man · #11 (**permalink**) 11-28-11, 15:25

Hi Dave,

Thank you for suggestions.

Unfortunately, it seems like (as pointed by Tonkuma), VALUES statement does not work on z/OS DB2 v.9.1.

The statement you posted gives an error:

Quote:

ILLEGAL SYMBOL "2". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE HOURS. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.8.86

Internet Services

Web Development

Digital Marketing

Consumer Tech