Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 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
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts JCL to fetch schedule status from act... parasmalik20 CA Products 1 Thu Jun 02, 2016 7:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us