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 7 5151
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/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Edwinah63 |
last post by:
Hi Everyone,
All the very best for 2004!!
i need urgent help with this problem, the users are about to skin me
alive!!
we have an access front end with linked to sql server 2k tables.
...
|
by: Mike |
last post by:
Hello,
I have a few rather urgent questions that I hope someone can help with (I
need to figure this out prior to a meeting tomorrow.) First, a bit of
background: The company I work for is...
|
by: Dimitri |
last post by:
PLEASE HELP,I HAVE A DATABSE WITH MULTIPLE RECORDS AS OUTLINED BELOW
EMP NO LEVEL NEXTINCREASE WAGETYPE UNIT
1000 1 0 1000
1000 1 0 1002 ...
|
by: varkey.mathew |
last post by:
Dear all,
Bear with me, a poor newbie(atleast in AD)..
I have to authenticate a user ID and password for a user as a valid
Active Directory user or not. I have created the IsAuthenticated...
|
by: AVL |
last post by:
Hi
I'm working on indexing a website. I want to restict the search to only few
file types like .doc,.txt and.ppt
How to specify the file types in the 'ixsso.Query' object
Presently i'n using...
|
by: sangram_149 |
last post by:
hi,
i have a query which fetches the sum of amounts from a table .the
column is 19 bytes
but the query returns only the actual amount..for example ..it returns
...
-1245.00
but i want it to...
|
by: Liam.M |
last post by:
HEY GUYS,
need some urgent help here....I am querying my database based on a
DueDate field...and want to send an automated email to anyone that
falls within two months PRIOR to this "DueDate",...
|
by: JHNielson |
last post by:
I Know I've posted an Urgent message before. But I'm in the middle of system testing, and these little stupid bugs are killing me......
I have a query that checks that a set of values (the...
|
by: gopim |
last post by:
strSql = "SELECT ISNULL(max(substring(User_ID,2,len(User_ID))) + 1,'100') FROM Users";
sqlCmd.CommandText = strSql;
sqlDr = sqlCmd.ExecuteReader();
...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |