zDB2 syntax error

mjschwneger · #1 (**permalink**) 08-11-07, 18:30

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

Peter.Vanroose · #2 (**permalink**) 08-12-07, 05:29

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.

stolze · #3 (**permalink**) 08-12-07, 16:54

DB2 UDB gives this explanation for the error:

Quote:

$ 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

Peter.Vanroose · #4 (**permalink**) 08-12-07, 17:04

Which indeed supports my suggestion to rewrite the query with CTEs to make it working.

nidm · #5 (**permalink**) 08-12-07, 22:43

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

mjschwneger · #6 (**permalink**) 08-13-07, 00:17

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

Quote:

to rewrite the query with CTEs to make it working

I could use some help with that...
Thanks a lot again, mj

Peter.Vanroose · #7 (**permalink**) 08-15-07, 15:16

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 · #8 (**permalink**) 08-15-07, 15:44

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

Internet Services

Web Development

Digital Marketing

Consumer Tech