|
View previous topic :: View next topic
|
| Author |
Message |
syed-sameer-ahmed
Warnings : 2
New User
.jpg)
Joined: 22 Feb 2007
Posts: 45
Location: Bangalore
|
|
|
|
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 |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Why do you want a subquery for this ? cant you put type <> 1 in a normal select query with the dates ? |
|
| Back to top |
|
|
syed-sameer-ahmed
Warnings : 2
New User
Joined: 22 Feb 2007
Posts: 45
Location: Bangalore
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Quote: |
I hope ,that answers your question
|
No
| Quote: |
is it possible to use cursor inside a cursor.?
|
?? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
|
|
