Order by and Fetch clause in Cartesian product

kalaikala · New User Joined: 21 Jul 2007 Posts: 27 Location: Chennai

Table1 Table2

Col1 Col2 Col3 Col4 Col5 Co1 Co2 Co3 Co4 Co5

My requirement is:

Col1 Col2 Col3 Co1

I want Col1, Col2 and Col3 from Table1 and co1 from Table2 with below conditions.

1. I want all rows from the Table1, which satisfies the condition. The select query for this is alone,

SELECT col1,Col2,Col3
FROM Table1
WHERE Col4 = 'value1'
AND Col5 <> 0;

2. I want only one row from the Table2, The select query for this is alone,

SELECT Co1
FROM Table2
WHERE Co2 = 'XX'
AND Co3 = 'YY'
AND Co4 = 1;
ORDER BY
Co3 DESC,
Co5 DESC
FETCH FIRST ROW ONLY;

When I query using Cartesian product for the above condition I am getting error message.

Error Message:
"SQLCODE = -4700, ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW"

Query (Cartesian product):
SELECT X.Col1,X.Col2,X.Col3,Y.Co1
FROM Table1 X,
(SELECT Co1
FROM Table2
WHERE Co2 = 'XX'
AND Co3 = 'YY'
AND Co4 = 1
ORDER BY
Co4 desc,
Co5 Desc
FETCH FIRST 1 ROW ONLY) Y
WHERE X.Col4 = 'Value1'
AND X.Col5 <> 0;

My Doubt is, How can we use Order by and Fetch clause in Cartesian product?. Or Can you give me some other solution for this.

Craq Giegerich · Posted: Tue May 20, 2008 7:19 pm

You will have to look up sqlcode -4700 in the DB2 Version 8 Codes manual.

sainathvinod · New User Joined: 01 Apr 2008 Posts: 11 Location: Chennai

Hi Kalaikala,

"FETCH FIRST 1 ROW ONLY" clause cannot be used inside a subquery.For wt exactly are you using the "FETCH FIRST" clause ???

kalaikala · New User Joined: 21 Jul 2007 Posts: 27 Location: Chennai

Hi Sainath,

Oh.. Okay. Thank U!..

Its for getting only one row(ordered) from the Table2. That paricular value should come with all the row in table1.

Can you answer my one more question in this forum

'Cursor Declaration with "WITH CLAUSE"'.

This also helps me to solve this problem.