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
 
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 How to force a select query to abend neo4u DB2 5 Mon Apr 23, 2018 1:36 pm
No new posts Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am
No new posts How to replace the below query? pkmurali DB2 12 Tue Feb 27, 2018 9:51 pm
No new posts Need Guidance regarding UPDATE query Poha Eater DB2 3 Mon Feb 12, 2018 11:35 pm
No new posts Query to skip Max time-stamp and fetc... vickey_dw DB2 8 Sun Jan 28, 2018 1:09 am

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