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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts Viewing executing process in NDM .. q... Debb.Brant IBM Tools 0 Wed Dec 14, 2016 10:55 pm


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