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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

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