By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,277 Members | 1,548 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,277 IT Pros & Developers. It's quick & easy.

Urgent Help :Query

P: n/a
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

Aug 21 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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/
Aug 21 '06 #2

P: n/a
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.

Aug 21 '06 #3

P: n/a
Serge, Brian...
thanks a lot....Solved it and 'values' is great..

tariq

Aug 22 '06 #4

P: n/a
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

Aug 24 '06 #5

P: n/a

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.

Aug 24 '06 #6

P: n/a
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.

Aug 24 '06 #7

P: n/a
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/
Aug 24 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.