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
 

 

How to process the query?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
syed-sameer-ahmed
Warnings : 2

New User


Joined: 22 Feb 2007
Posts: 46
Location: Bangalore

PostPosted: Mon Jun 22, 2009 6:09 pm    Post subject: How to process the query?
Reply with quote

Hello ,

I have a table Room_info
which has following fields and the table fields cannot be changed at any cost :

---------------------------------------------------------------------------------
Room_number| Check_In Date| Check_Out Date| Confirmation_Id | Room_type

The characters are as follows :


Room_number
Char(3)
Not Null

Check_In Date
Date
Not Null

Check_Out Date
Date
Not Null

Confirmation_Id
Char(16)
Foreign Key to Customer Info table

Room_type
Numeric(3)
Not Null


I have to fetch the available room numbers when inputted with Check in date and check out date. and I have to pick more than 2 rooms for booking.

I had written the query for fetching the room numbers
Code:

SELECT distinct ROOM_NUMBER FROM IDCL58.ROOM_INFO
where room_number != (select distinct room_muber from IDCL58.ROOM_INFO
                                              where type=1
                                              and Check_in_date >= 2009-08-06 
                                              and Check_out_date <= 2009-10-06)


but the sub query is fetching multiple rooms,And I want to fetch the room numbers one after the another.

How do I go about it.
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jun 22, 2009 6:50 pm    Post subject:
Reply with quote

Why do you want a subquery for this ? cant you put type <> 1 in a normal select query with the dates ?
Back to top
View user's profile Send private message
syed-sameer-ahmed
Warnings : 2

New User


Joined: 22 Feb 2007
Posts: 46
Location: Bangalore

PostPosted: Tue Jun 23, 2009 10:36 am    Post subject:
Reply with quote

there are rooms of 5 type: 1,2,3,4,5 and there are 500 rooms totally which makes 100 rooms each.

and at a time each customer can book rooms of all the type.Hence cannot fetch the rooms by type bu type is one of the criteria.

I hope ,that answers your question.

is it possible to use cursor inside a cursor.?

kindlly let me know if you any queries

Please help.
Thanks
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jun 23, 2009 1:10 pm    Post subject:
Reply with quote

Quote:

I hope ,that answers your question


No


Quote:

is it possible to use cursor inside a cursor.?



??
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jun 23, 2009 8:11 pm    Post subject:
Reply with quote

Hello,

Quote:
is it possible to use cursor inside a cursor.?
I suspect so, but not in the same EXEC SQL. . .

Declare both cursors separately.
Open the "outside" cursor.
Fetch a row from the "outsice" cursor until no more
. . Open the "inside" cursor"
. . Fetch the row(s) from the "inside" cursor til no more
. . Close the "inside" cursor.
Close the "outside" cursor.
All done. . .

If you post a more clear definition of what you are trying to do, someone may have other suggestions.
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 Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


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