Help needed regarding the query?

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
ssameerahmed
Member
Posts: 2
Joined: Tue Jun 23, 2009 11:12 am

Help needed regarding the query?

Post by ssameerahmed » Tue Jun 23, 2009 11:18 am

--------------------------------------------------------------------------------

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.
_________________
Syed Sameer Ahmed
Syed Sameer Ahmed

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

Post by Natarajan » Tue Jun 23, 2009 11:53 am

Hi Syed,

To get available room you dont required to write subquery.
Any how, comming to your questions... "I want to fetch the room numbers one after another'

If you are using COBOL DB2 program... singleton SELECT statement will get first record from the fetched records... but you need to handle SQLCODE -811...

If you want all the records... you need to define COBOL DB2 CURSOR to get all
the records...
Natarajan
Chennai

ssameerahmed
Member
Posts: 2
Joined: Tue Jun 23, 2009 11:12 am

Post by ssameerahmed » Wed Jun 24, 2009 12:17 pm

How to handle 811 using cobol-db2 ? and How would the query look like because I have tried many things using Check-in date and check-out date.?

could you please give the logic or query please?

Thanks
Syed Sameer Ahmed

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

IBM DB2 SQL QUERY USING FETCH FIRST ROW ONLY

Post by Natarajan » Wed Jun 24, 2009 12:40 pm

ok let me try. :D

As per my understanding you can use following DB2 Query to get available rooms.
Input to this query is New-check-in date and New-check-out-date.

Here cobol-db2 code.

Code: Select all

EXEC SQL
SELECT ROOM_NUMBER 
FROM IDCL58.ROOM_INFO 
where type=1 
and Check_out_date <= New-check-in-date 
FETCH FIRST ROW ONLY
END-EXEC.
Above query should fetch only one record...
if FETCH FIRST ROW ONLY... not working in embedded COBOL db2 program... use following code ( without FETCH FIRST ROW ONLY clause)

Code: Select all

EXEC SQL
SELECT ROOM_NUMBER 
FROM IDCL58.ROOM_INFO 
where type=1 
and Check_out_date <= New-check-in-date 
END-EXEC.

IF SQLCODE = 0 OR -811
    write your logic...
ELSE
    use abend logic.
END-IF.

Let me know, if you have any questions on this DB2 Query.
Natarajan
Chennai

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others