Hi,

I get the following error in the query

SQL0415N The data types of corresponding columns are not compatible in
a
fullselect that includes a set operator or in the multiple rows of a
VALUES
clause of an INSERT or fullselect. SQLSTATE=42825

How can i specify a combination of values in the IN phrase ? I need a
combination as
I need to check against valid combinations of ('test',1)
('test1,2),('test3',3)

I cant figure out how to specify these in the IN phrase.

If i give 2 IN phrases a.nme in (''test','test1','test3') and a.seq_num
IN (1,2,3) it will not give me the correct result cause the a.nme and
a.seq_num are the primary keys and i need to filter out the
combination.


SELECT
a.name,
a.seq_num
FROM
db2test.a
(
SELECT
num,
seq_num,
yr
FROM
db2test.b
WHERE
p_num= '100' AND
p_seq_num=1 AND
p_bgt_yr='2006'
) as c
WHERE
a.num=c.num AND
a.seq_num=c.seq_num AND
a.yr=c.yr AND
(a.nme,a.seq_num) IN ('test',1)
GROUP BY
nme,seq_num

rAinDeEr wrote:
> Hi,
>
> I get the following error in the query
>
> SQL0415N The data types of corresponding columns are not compatible in
> a
> fullselect that includes a set operator or in the multiple rows of a
> VALUES
> clause of an INSERT or fullselect. SQLSTATE=42825
>
> How can i specify a combination of values in the IN phrase ? I need a
> combination as
> I need to check against valid combinations of ('test',1)
> ('test1,2),('test3',3)
>
> I cant figure out how to specify these in the IN phrase.
>
> If i give 2 IN phrases a.nme in (''test','test1','test3') and a.seq_num
> IN (1,2,3) it will not give me the correct result cause the a.nme and
> a.seq_num are the primary keys and i need to filter out the
> combination.
>
>
> SELECT
> a.name,
> a.seq_num
> FROM
> db2test.a
> (
> SELECT
> num,
> seq_num,
> yr
> FROM
> db2test.b
> WHERE
> p_num= '100' AND
> p_seq_num=1 AND
> p_bgt_yr='2006'
> ) as c
> WHERE
> a.num=c.num AND
> a.seq_num=c.seq_num AND
> a.yr=c.yr AND
> (a.nme,a.seq_num) IN ('test',1)
> GROUP BY
> nme,seq_num
>
(a.nme,a.seq_num) IN (VALUES('test',1), ('test2', 2), ....)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

rAinDeEr wrote:
> Hi,
>
> I get the following error in the query
>
> SQL0415N The data types of corresponding columns are not compatible in
> a
> fullselect that includes a set operator or in the multiple rows of a
> VALUES
> clause of an INSERT or fullselect. SQLSTATE=42825
>
> How can i specify a combination of values in the IN phrase ? I need a
> combination as
> I need to check against valid combinations of ('test',1)
> ('test1,2),('test3',3)
>
> I cant figure out how to specify these in the IN phrase.
>
> If i give 2 IN phrases a.nme in (''test','test1','test3') and a.seq_num
> IN (1,2,3) it will not give me the correct result cause the a.nme and
> a.seq_num are the primary keys and i need to filter out the
> combination.
>
>
> SELECT
> a.name,
> a.seq_num
> FROM
> db2test.a
> (
> SELECT
> num,
> seq_num,
> yr
> FROM
> db2test.b
> WHERE
> p_num= '100' AND
> p_seq_num=1 AND
> p_bgt_yr='2006'
> ) as c
> WHERE
> a.num=c.num AND
> a.seq_num=c.seq_num AND
> a.yr=c.yr AND
> (a.nme,a.seq_num) IN ('test',1)
> GROUP BY
> nme,seq_num

According to the documentation, SQL Reference Volume 1, Chapter 2.
Language Elements, Predicates=>IN predicate, when the left-expression
has parenthesis (making it expression3 and allowing mutiple values) the
right-expression must be a full-select.

Therefore: (a.nme,a.seq_num) IN (VALUES ('test',1))

Or with multiple possibilities:
(a.nme,a.seq_num) IN (VALUES ('test',1), ('test1,2), ('test3',3))

B.

Serge, Brian...


thanks a lot....Solved it and 'values' is great..

tariq

Hi,

am able to run the query from the command window but when I am tryg to
use this from a java application with parameter markers, its giving me
an error -418.

A STATEMENT STRING TO BE PREPARED CONTAINS AN INVALID USE OF PARAMETER
MARKERS

Explanation ::

Parameter markers cannot be used in the SELECT list, as the sole
argument of a scalar function, or in a concatenation operation.
Parameter markers cannot be used in the string expression of an EXECUTE
IMMEDIATE SQL statement.

I had found this from the group..Serge had replied...

Here's your porblem:

bitwiseAnd(?,805)
DB2 supports function overloading. Since the parameter marker is
untyped, db2 doesn't know how to resolve the function and how to type
the
parameter marker. You can do: bitwiseAnd(cast(? as integer),805)

Do I need to typecast the parameter marker ?

tariq

rAinDeEr wrote:
> Hi,
>
> am able to run the query from the command window but when I am tryg to
> use this from a java application with parameter markers, its giving me
> an error -418.
>
> A STATEMENT STRING TO BE PREPARED CONTAINS AN INVALID USE OF PARAMETER
> MARKERS
>
> Explanation ::
>
> Parameter markers cannot be used in the SELECT list, as the sole
> argument of a scalar function, or in a concatenation operation.
> Parameter markers cannot be used in the string expression of an EXECUTE
> IMMEDIATE SQL statement.
>
> I had found this from the group..Serge had replied...
>
> Here's your porblem:
>
> bitwiseAnd(?,805)
> DB2 supports function overloading. Since the parameter marker is
> untyped, db2 doesn't know how to resolve the function and how to type
> the
> parameter marker. You can do: bitwiseAnd(cast(? as integer),805)
>
> Do I need to typecast the parameter marker ?
>
> tariq

What's the exact command you are trying to execute?

B.

SELECT
a.name,
a.seq_num
FROM
db2test.a
(
SELECT
num,
seq_num,
yr
FROM
db2test.b
WHERE
p_num= ? AND
p_seq_num=? AND
p_bgt_yr=?
) as c
WHERE
a.num=c.num AND
a.seq_num=c.seq_num AND
a.yr=c.yr AND
(a.nme,a.seq_num) IN (values(?,?) ,values(?,?) )
GROUP BY
nme,seq_num

This is the command am trying to Execute.

I don't think the parameter marker can reach through values and in
predicate.
So you need to help it out by casting the first row.
Note that you only need one VALUES keyword
(a.nme,a.seq_num) IN (values(CAST(? AS <sometype>),
CAST(? AS <someothertype>)),
(?,?))

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/