Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2011
    Posts
    7

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

    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:

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are the data type of TYP and ID?

  3. #3
    Join Date
    Nov 2011
    Posts
    7
    Here is an excerpt from the DDL:

    "TYP" CHAR(5) FOR SBCS DATA WITH DEFAULT NULL,
    "ID" CHAR(7) FOR SBCS DATA WITH DEFAULT NULL,
    Last edited by humble_man; 11-27-11 at 09:39.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 ...".

  5. #5
    Join Date
    Nov 2011
    Posts
    7
    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.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... 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.

    ... 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.

    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
              )
    ;

  7. #7
    Join Date
    Nov 2011
    Posts
    7
    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

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    Last edited by tonkuma; 11-27-11 at 13:08. Reason: Add reference to "Summary of predicate processing".

  9. #9
    Join Date
    Nov 2011
    Posts
    7

    Thumbs up

    Thank you very much !


  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  11. #11
    Join Date
    Nov 2011
    Posts
    7
    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:

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •