| subquery and CLOB [message #19653] |
Tue, 02 April 2002 22:22  |
Mats Gard
Messages: 3
Registered: April 2002
|
Junior Member |
|
|
I have a problem with the following:
SELECT x.element_type_name, x.element_name, x.element_description
FROM apps.cs_kb_set_elements_v x
WHERE x.SET_ID IN (
SELECT DISTINCT(y.SET_ID)
FROM apps.cs_kb_set_elements_v y
WHERE y.element_type_name = 'Fact'
AND y.element_name = 'Windows 95');
The result is:
ORA-00932 inconsistent datatypes
It works if i remove "x.element_description", which is
a CLOB.
If I only execute the subquery, it returns:
10, 11, 12
Then I try the main query with 10, 11, 12:
SELECT x.element_type_name, x.element_name, x.element_description
FROM apps.cs_kb_set_elements_v x
WHERE x.SET_ID IN (10, 11, 12);
It works!
What's the problem???
|
|
|
|
| Re: subquery and CLOB [message #19655 is a reply to message #19653] |
Tue, 02 April 2002 23:38  |
Epe
Messages: 99
Registered: March 2002
|
Member |
|
|
Hello,
actually, I don't understand why you use the subquery.
The tables from the main query and the subquery are the same, as well as the field you are selecting in the subquery and the field you are comparing it with
Can't you use :
SELECT x.element_type_name, x.element_name, x.element_description
FROM apps.cs_kb_set_elements_v x
WHERE y.element_type_name = 'Fact'
AND y.element_name = 'Windows 95';
????
Success,
epe
|
|
|
|
| Re: subquery and CLOB [message #19657 is a reply to message #19655] |
Wed, 03 April 2002 00:51 |
Mats Gard
Messages: 3
Registered: April 2002
|
Junior Member |
|
|
That is another query!
What I want to get is ALL
element_type_name, element_name, element_description
for all SET_ID that have a
element_type_name = 'Fact' and element_name = 'Windows 95'
Your suggestion give me ONLY the
element_type_name, element_name, element_description
where the
element_type_name = 'Fact' and element_name = 'Windows 95'
The rest of the
element_type_name, element_name, element_description
for that SET_ID is not received.
|
|
|
|
| Re: subquery and CLOB [message #19666 is a reply to message #19655] |
Wed, 03 April 2002 05:17 |
Epe
Messages: 99
Registered: March 2002
|
Member |
|
|
Yow,
I'm sorry for the misunderstanding.
I've been searching the manuals, but I couldn't find any reason.
The first error you describe (inconsistent datatype) rather seems to me as if there is something wrong with the subquery datatypes (but I suppose you would have noticed that). Especially when it seems to work with the hard coded values...
I can't find an answer (maybe better to repost your message, for people might think I was able to help you).
Sorry and good luck,
epe
|
|
|
|
| Re: subquery and CLOB [message #19679 is a reply to message #19653] |
Wed, 03 April 2002 09:16 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Which Oracle version are you on? Here is an example from 8.1.7:
sql>create table t
2 (set_id number,
3 element_type_name varchar2(20),
4 element_name varchar2(20),
5 element_description clob);
Table created.
sql>insert into t values (1, 'Fact', 'Windows 95', 'Description');
1 row created.
sql>insert into t values (1, 'Fact', 'Windows 98', 'Description');
1 row created.
sql>insert into t values (2, 'Fact', 'Windows 95', 'Description');
1 row created.
sql>insert into t values (2, 'Fact', 'Windows NT', 'Description');
1 row created.
sql>insert into t values (3, 'Fact', 'Windows 2000', 'Description');
1 row created.
sql>select set_id, element_type_name, element_name, element_description
2 from t
3 where set_id in
4 (select distinct(set_id)
5 from t
6 where element_type_name = 'Fact'
7 and element_name = 'Windows 95');
SET_ID ELEMENT_TYPE_NAME ELEMENT_NAME
--------- -------------------- --------------------
ELEMENT_DESCRIPTION
------------------------------------------------------------------------------
1 Fact Windows 95
Description
1 Fact Windows 98
Description
2 Fact Windows 95
Description
2 Fact Windows NT
Description |
|
|
|
| Re: subquery and CLOB [message #19689 is a reply to message #19679] |
Wed, 03 April 2002 21:47 |
Mats Gard
Messages: 3
Registered: April 2002
|
Junior Member |
|
|
Thank you very much Todd for taking your time to help me.
My version is 8.1.7.1.0.
I see that your query works and I have tried to do the same query in my database without success.
It is really anoying!
Anyway, to get around the problem I rewrote the query using a join like this:
SELECT x.SET_ID, x.element_type_name, x.element_name, x.element_description
FROM apps.cs_kb_set_elements_v x, apps.cs_kb_set_elements_v y
WHERE x.SET_ID = y.SET_ID
AND y.element_type_name = 'Fact'
AND y.element_name = 'Windows 95';
That gives me the result that I intended with the subquery.
Thanks /Mats
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
