Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Posts
    13

    Unanswered: zDB2 syntax error

    I have a view that runs on DB2 UDB, SQL server 2000/2005 and ORacle 9i/10g. The same syntax breaks on zDB2. Read the description of the error, but it looks that DB2 UDB has the same limitations like zDB2.
    How could I fix this statement to run on both zDB2 and DB2 UDB?
    Thansk a lot for the help, mj
    Error executing the following statement:
    CREATE VIEW VIS_0D_EXISTS
    as
    select sc.majorlict_id, sc.inbound_ID_id, sc.majorlict_rule_id,
    sc.major_ID1, sc.major_ID2,
    sc.D_of_sep, sc. majorlict_dt, sc.majorlict_stat, sc.disp_comments,
    sc.weak_rel_score,
    sc.weak_like_score, sc.path_strength,
    (case when exists (select 1 from er_ID_score es
    WHERE sc.major_ID1 = es.matched_ID_id and sc.D_of_sep =
    0 and
    sc.sep_test_id = es.er_id)
    then sc.sep_test_id
    when not exists (select 1 from er_ID_score es
    WHERE sc.major_ID1 = es.matched_ID_id and
    sc.sep_test_id = es.er_id)
    then (case
    when exists (select 1 from er_rel er, er_ID_score es2
    where er.dest_ID_id = sc.major_ID1
    and es2.er_id = er.er_id)
    then (select max(er.er_id) from er_rel er
    where er.dest_ID_id = sc.major_ID1 )
    when
    exists (select 1 from sep_majorlict sc2, er_ID_score es2
    where sc.major_ID1 = sc2.major_ID1 and
    sc.major_ID2 = sc2.major_ID2 and
    sc2.sep_test_id = es2.er_id and
    sc.major_ID1 = es2.matched_ID_id and
    sc2.D_of_sep = 0 and
    sc.D_of_sep = sc2.D_of_sep)
    then (select max(es2.er_id) from sep_majorlict sc2, er_ID_score es2
    where sc.major_ID1 = sc2.major_ID1 and
    sc.major_ID2 = sc2.major_ID2 and
    sc2.sep_test_id = es2.er_id and
    sc.major_ID1 = es2.matched_ID_id and
    sc2.D_of_sep = 0 and
    sc.D_of_sep = sc2.D_of_sep)
    end)
    end) as sep_test_id
    from sep_majorlict sc where sc.D_of_sep = 0

    com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -582, SQLSTATE: 42625,
    SQLERRMC: INVALID PREDICATE TYPE

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    You are clearly using DB2 v8 for z/OS (or maybe DB2 9 ?)

    Syntactically everything looks fine.
    Is it possible that you are comparing incompatible columns, like INT with CHAR ?
    Or that the datatypes of the expressions following "THEN" are incompatible?

    Difficult to judge without having the table declarations.

    Is this -582 a BIND error or a runtime error?

    I would suggest using common table expressions ("WITH" clauses) to build up the query: this will make it more readable & will more easily lead to the error spot.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    DB2 UDB gives this explanation for the error:
    $ db2 "? sql582"


    SQL0582N A CASE expression in a VALUES clause, IN predicate,
    GROUP BY clause, or ORDER BY clause cannot include a
    quantified predicate, an IN predicate using a
    fullselect, or an EXISTS predicate.

    Explanation:

    A search condition of a CASE expression is:

    o a quantified predicate (one using SOME, ANY, or ALL),

    o an IN predicate using a fullselect or

    o an EXISTS predicate



    and the CASE expression is part of:

    o a VALUES clause

    o an IN predicate

    o a GROUP BY clause or

    o an ORDER BY clause.



    Such CASE expressions are not supported. The CASE expression
    may be part of a function written in SQL.

    The statement cannot be processed.

    User Response:

    Remove the use of the quantified predicate, IN predicate, or
    EXISTS predicate from the CASE expression. If the CASE
    expression is part of a function, the query may need to be
    written without the function that causes the error.

    sqlcode : -582

    sqlstate : 42625
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Which indeed supports my suggestion to rewrite the query with CTEs to make it working.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    May 2003
    Posts
    113
    just a suggestion,I am not sure whether it works:

    you can rewrite the exist from :

    (case when exists (select 1 from er_ID_score es
    WHERE sc.major_ID1 = es.matched_ID_id and sc.D_of_sep =
    0 and
    sc.sep_test_id = es.er_id)

    to
    (case when (1 = (select distinct 1 from er_ID_score es
    WHERE sc.major_ID1 = es.matched_ID_id and sc.D_of_sep =
    0 and
    sc.sep_test_id = es.er_id))

  6. #6
    Join Date
    Sep 2006
    Posts
    13
    Thanks a lot for the help!
    I could use nidm's example but could you please give me an example of what you mean with
    to rewrite the query with CTEs to make it working
    I could use some help with that...
    Thanks a lot again, mj

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by mjschwneger
    ... could you please give me an example of what you mean with CTEs
    It's like creating views which can then be used in subsequent SELECTs.
    An example: instead of writing
    Code:
    SELECT t.x,
                CASE WHEN (SELECT max(y) FROM t WHERE t.x = t0.x) < 0
                THEN 0
                ELSE (SELECT max(y) FROM t WHERE t.x = t0.x)
                END
    FROM t AS t0
    WHERE t0.x >= 1
    one could write something like
    Code:
    WITH v AS (SELECT x, max(y) AS y FROM t WHERE x >= 1 GROUP BY x)
    SELECT x, CASE WHEN y<0 THEN 0 ELSE y END
    FROM v
    On database systems not (yet) supporting CTEs, just replace them afterwards with nested table expressions; for the example above that would be:
    Code:
    SELECT x, CASE WHEN y<0 THEN 0 ELSE y END
    FROM (SELECT x, max(y) AS y FROM t WHERE x >= 1 GROUP BY x) AS v
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    The following query is equivalent to yours, but avoids the nested CASE expression (which might have been the problem with DB2 for z/OS); could you verify whether this one works?
    Code:
    SELECT sc.majorlict_id,
           sc.inbound_ID_id,
           sc.majorlict_rule_id,
           sc.major_ID1,
           sc.major_ID2,
           sc.D_of_sep,
           sc.majorlict_dt,
           sc.majorlict_stat,
           sc.disp_comments,
           sc.weak_rel_score,
           sc.weak_like_score,
           sc.path_strength,
           (CASE
            WHEN EXISTS (SELECT 1
                         FROM   er_ID_score AS es
                         WHERE  sc.major_ID1 = es.matched_ID_id
                           AND  sc.sep_test_id = es.er_id)
            THEN sc.sep_test_id
            WHEN EXISTS (SELECT 1
                         FROM   er_ID_score AS es, er_rel AS er
                         WHERE  er.dest_ID_id = sc.major_ID1
                           AND  es.er_id = er.er_id)
            THEN (SELECT max(er.er_id)
                  FROM   er_rel AS er
                  WHERE  er.dest_ID_id = sc.major_ID1)
            ELSE (SELECT max(es.er_id)
                  FROM   sep_majorlict AS sc2, er_ID_score AS es
                  WHERE  sc.major_ID1 = sc2.major_ID1
                    AND  sc.major_ID2 = sc2.major_ID2
                    AND  sc2.sep_test_id = es.er_id
                    AND  sc.major_ID1 = es.matched_ID_id
                    AND  sc2.D_of_sep = 0)
            END) AS sep_test_id
    FROM   sep_majorlict AS sc
    WHERE  sc.D_of_sep = 0
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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