Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Order by and Fetch clause in Cartesian product

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kalaikala

New User


Joined: 21 Jul 2007
Posts: 27
Location: Chennai

PostPosted: Tue May 20, 2008 7:08 pm    Post subject: Order by and Fetch clause in Cartesian product
Reply with quote

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.
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue May 20, 2008 7:19 pm    Post subject:
Reply with quote

You will have to look up sqlcode -4700 in the DB2 Version 8 Codes manual.
Back to top
View user's profile Send private message
sainathvinod

New User


Joined: 01 Apr 2008
Posts: 11
Location: Chennai

PostPosted: Wed May 21, 2008 1:30 pm    Post subject:
Reply with quote

Hi Kalaikala,

"FETCH FIRST 1 ROW ONLY" clause cannot be used inside a subquery.For wt exactly are you using the "FETCH FIRST" clause ???
Back to top
View user's profile Send private message
kalaikala

New User


Joined: 21 Jul 2007
Posts: 27
Location: Chennai

PostPosted: Fri May 23, 2008 2:29 pm    Post subject: Reply to: Order by and Fetch clause in Cartesian product
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts Sorting on text - but in non-alphabet... Roy Ware SYNCSORT 5 Wed Aug 23, 2017 9:15 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Route a product's job to specific lpar vasanthz All Other Mainframe Topics 9 Thu Mar 02, 2017 2:22 am
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us