Home      Mainframe Forum      Mainfarme Tutorials      IBM Manuals      Mainframe Interview Questions      Mainframe Books      IT News     SiteMap     Downloads


     
 
MAINFRAME - TIP OF THE DAY : Q. If there is a situation, where we need to code more than 255 steps in a JOB? A. We need to split jcl into two jcls , at the end of the first jcl check the condition code and initiate the second jcl.

Google
 
Web mainframegurukul.com

Programmers Voted for below topics.      Please Vote for good Posts. Votes Salutes
Topic Title Votes Salutes
splitting of records using cobol 6
HOW TO FIND LINKAGE LENGTH 6
SOC 7 abend - interview question 6
Infosys Training Program for freshers 5
EJECT Verb in COBOL 5
jcl sort to add leading zeroes 5
Changes in copybook 4
Comparing two files 3
What are the issues related with correlated subqueries? 3
VSAM File status 23 ,but records are present in the file 3
what is index or subscript in cobol. please give me brief ex 3
IKJ56500I COMMAND DSN NOT FOUND 3
Cobol Multiple Choice Questions By Shravan Wipro 3
Merging two files into one based on certain key fields: 1 1
what is index or subscript in cobol. please give me brief ex 1 1
Copy GDG to GDG (one to one). 1 1
Cobol Multiple Choice Questions By Shravan Wipro 1 1
Hexadecimal to decimal conversion - SORT JCL INREC OUTREC 1 1
What is the difference between SYNONYM and ALIAS? 1 1
PROCLIB 1 1
sort two files with same sort condition 1 1
What is the difference between Copy and Include? 1 1
EZtrv+ - Is there a way to suppress ez-code from printing... 1 1
jcl sort to add leading zeroes 5
VSAM File status 23 ,but records are present in the file 3
Urgent Help needed !! 3
Hexadecimal to decimal conversion - SORT JCL INREC OUTREC 3
How we can access PDS and its Members thru COBOL ? 3
What is auditing? 2
Reg DFSORT utility. 2
Computer based tutorial on IMS DB/DC 2
what is index or subscript in cobol. please give me brief ex 2
How to compare two ps files having millions of records ? 2
Cobol Multiple Choice Questions By Shravan Wipro 2
SOC 7 abend - interview question 2
How to read HEX file and convert it into decimal in REXX. 2
Various Locking mechanism for cursors and other SQL? 2
How to fetch and compare the system date in JCL 2
stored procedure in cobol 2
VERTICAL ARRAY IN CICS 2
what s the difference between DB2 & ADB2? 2
How to assign System Date/Time to a parameter in a JCL pgm? 1
is the answer correct?? 1
Mainframe Developer Requirement - Coimbatore 1
Man of the year 1
I need to FTP from MF to UNIX 1
Control flow from one STEP to another 1
How to convert Julian date format into yyyymmdd date format? 1
What is the difference between Static and Dynamic Binding? 1
In jcl please help me. 1
Access DB2 region using rexx 1
DFHAC2016 - Trans XXXX cannot run because program XXX is Not 1
EZtrv+ - Is there a way to suppress ez-code from printing... 1
How to use XREF.. 1
DB2 checkpoint-restart doubts 1
Comparing 2 files based on Acct number without using tables 1
JCL-I need to FTP from MF to Local computer 1
cics 1
sort two files with same sort condition 1
U4093 IMS Abend 1
writing 2 records for each qualifying input record. 1
Is it possible to get last modified date of PDS members... 1
Merging two files into one based on certain key fields: 1
How to run only 2 steps out of 100 steps??? 1
Corresponding statement 1
Difference between an Object Module and Load Module 1
Moving cursor to next field in ISPF dialog 1
DB2 query !! 1
JCL Interpreter :?: 1
Comparing Date with current date 1
SORT verb - help 1
SQL Query Prob

 
Post new topic   Reply to topic    mainframegurukul.com Forum Index -> COBOL
  View previous topic :: View next topic  
Author Message
hbkanderi
Member


Joined: 27 Mar 2007
Posts: 1

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Wed Mar 28, 2007 12:44 pm    Post subject: SQL Query Prob Reply with quote

Hi.,

In my DB2 table contain 20 rows I need first 10 rows & last 5 rows (1 to 10 & 16 to 20) I need to skip 11 to 15 rows. Pls any one give me the SQL Query for this problem
Back to top
View user's profile Send private message Yahoo Messenger MSN Messenger
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.

Veera
Moderator


Joined: 22 Feb 2006
Posts: 111

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Wed Mar 28, 2007 8:14 pm    Post subject: Reply with quote

hbkanderi

There is separate FORUM for DB2 where you can post your DB2 QUERIES but you have posted in Enterprise COBOL, going forward please take care.

Regarding your question,

I am not sure if we can do what ever you have asked for in one PARSE with DB2.

Well but there are ways by which we can achieve what you have asked for.

Approach1:
************

1--> FETCH FIRST n ROWS ONLY -> Using this you can get first 10 rows. where n=10 this time.

2--> Now use the same Query and reverse the order by i.e. use DESC and then again select the FETCH FIRST n ROWS ONLY , where n=5 this time.

SELECT * FROM <<TABLE NAME>> ORDER BY <<Primary Key>> DESC FETCH FIRST 5 ROWS ONLY;; ----> last 5 rows

But for this you need to PARSE the DB2 2 times.

There may be another efficient means to achieve this, lets see if we get few more responses.


Approach2: Scrollable cursor: -> If not Approach1 then Try this approach
********* ***************

Scroallable cursor working with keywords should help us.

Scrollable cursors allow developers to move through the results of a query in multiple ways.

The following key words are supported when fetching data from a scrollable cursor:

NEXT - will FETCH the next row, the same way that the pre-V7 FETCH statement functioned

PRIOR - will FETCH the previous row

FIRST - will FETCH the first row in the results set

LAST - will FETCH the last row in the results set

CURRENT - will re-FETCH the current row from the result set

BEFORE - positions the cursor before the first row of the results set

AFTER - positions the cursor after the last row of the results set

ABSOLUTE n - will FETCH the row that is n rows away from the first row in the results set

RELATIVE n - will FETCH the row that is n rows away from the last row fetched

For both ABSOLUTE and RELATIVE, the number n must be an integer. It can be either a positive

or a negative number, and it can be represented as a numeric constant or as a host variable.

All of the FETCH options for scrollable cursors also reposition the cursor before fetching

the data. For example, consider the following cursor logic:


DECLARE csr1 SENSITIVE STATIC SCROLL CURSOR

FOR SELECT FIRSTNAME, LASTNME

FROM DSN8710.EMP

ORDER BY LASTNME;

OPEN csr1;

FETCH LAST csr1 INTO :FN, :LN;

NOTE : MOSTLY RELATIVE n IS THE KEY WORD WHICH YOU SHOULD USE.

P.S : I donot have DB2 in my system otherwise i wud have run a test and confirmed the results.well lets us know if any of the above approachs worked for you.

Thanks,
Veera.
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
DavidatK
Active Member


Joined: 27 Mar 2007
Posts: 65
Location: Troy, MI USA

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Thu Mar 29, 2007 5:14 am    Post subject: Reply with quote

Hbkanderi,

Also remember, that DB2, in theory, doesn?t maintain the concept of physical 1st, 2nd, 3rd? last records. It?s not like a flat file. When you ?fetch first n rows only? you get the first n rows that DB2 gives you, in the easiest access to the data for DB2. It is not necessarily the first n rows inserted into the table. DB2 is a relational database, and you should have some idea of the ?order? you want the rows returned in.

That said, Veera is probably right, that it cannot, at least easily, be done in a single SQL, at least in V7. I believe I?ve read somewhere, that what you want to do, may be possible in V9 and maybe V8 with the enhancements that have been made to those versions.

In the sorted ?order? of the table that you want the 1-10, and 15-20 rows, do the columns in the ?order? uniquely identify the row, without duplicates? We may be able to use this to our advantage if so.

Also, what version of DB2 are you using?

Though I am new to this forum, I have been a member of other forums for some time and know many of the members here and recognize the high degree of talent in them.

Also, academic problems are, many times, much different than business problems, but usually much more fun to solve.

Keep posting, put us to the test, and get your class mates to post also. We love the opportunity to help.

Dave
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
DavidatK
Active Member


Joined: 27 Mar 2007
Posts: 65
Location: Troy, MI USA

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Thu Mar 29, 2007 6:27 am    Post subject: Reply with quote

Hbkanderi,

Well, I had to think about this for awhile, and am making some assumptions, which is probably a bad thing to do. I?m going to assume there is a unique key on the table and you want the first 10 key values and the last 5 key values.

I created a temporary table and inserted rows into it. ?N? will be the unique key and ?NM? the name of the key value. As you can see, the values were inserted in random order.

Code:

  DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL       
         (N                DEC(6)               
         ,NM              CHAR(20)             
          )                                     
  ;                                                                                                   
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (1, 'ONE');         
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (2, 'TWO');         
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (10, 'TEN');         
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (15, 'FIFTEEN');     
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (25, 'TWENTY FIVE');
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (12, 'TWELVE');     
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (100, 'ONE HUNDRED');
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (99, 'NINETY NINE');
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (51, 'FIFTY ONE');   
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (11, 'ELEVEN');     
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (6, 'SIX');         
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (16, 'SIXTEEN');     
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (8, 'EIGHT');       
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (9, 'NINE');         
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (34, 'THIRTY FOUR');
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (21, 'TWENTY ONE'); 
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (7, 'SEVEN');       
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (88, 'EIGHTY EIGHT');
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (4, 'FOUR');         
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (26, 'TWENTY SIX');   
--                                                             
  SELECT *                                                     
    FROM SESSION.TEMP_TBL                                     
   ORDER BY N                                                 
  ;                                                           
  SELECT *                                                     
    FROM SESSION.TEMP_TBL                                     
   WHERE N <=                                                 
     (SELECT N                                                 
        FROM SESSION.TEMP_TBL   TT1                           
       WHERE (10 - 1) IN                 -- < 10TH LOWEST N   
         (SELECT COUNT(*)                                     
            FROM SESSION.TEMP_TBL   TT2                       
           WHERE TT1.N > TT2.N                                 
          )                                                   
       )                                                       
   OR N >=                                                   
     (SELECT N                                               
        FROM SESSION.TEMP_TBL   TT1                         
       WHERE (5 - 1) IN                  -- < 5TH HIGHTEST N
         (SELECT COUNT(*)                                   
            FROM SESSION.TEMP_TBL   TT2                     
           WHERE TT1.N < TT2.N                               
          )                                                 
       )                                                     
   ORDER BY N                                               
  ;                                                         


The result of the first SELECT above is:

Code:

    +----------------------------------+
    |     N     |          NM          |
    +----------------------------------+
  1_|       1   | ONE                  |
  2_|       2   | TWO                  |
  3_|       4   | FOUR                 |
  4_|       6   | SIX                  |
  5_|       7   | SEVEN                |
  6_|       8   | EIGHT                |
  7_|       9   | NINE                 |
  8_|      10   | TEN                  |
  9_|      11   | ELEVEN               |
 10_|      12   | TWELVE               |
 11_|      15   | FIFTEEN              |
 12_|      16   | SIXTEEN              |
 13_|      21   | TWENTY ONE           |
 14_|      25   | TWENTY FIVE          |
 15_|      26   | TWENTY SIX           |
 16_|      34   | THIRTY FOUR          |
 17_|      51   | FIFTY ONE            |
 18_|      88   | EIGHTY EIGHT         |
 19_|      99   | NINETY NINE          |
 20_|     100   | ONE HUNDRED          |
    +----------------------------------+
 


The result of the second SELECT above is:

Code:

    +----------------------------------+
    |     N     |          NM          |
    +----------------------------------+
  1_|       1   | ONE                  |
  2_|       2   | TWO                  |
  3_|       4   | FOUR                 |
  4_|       6   | SIX                  |
  5_|       7   | SEVEN                |
  6_|       8   | EIGHT                |
  7_|       9   | NINE                 |
  8_|      10   | TEN                  |
  9_|      11   | ELEVEN               |
 10_|      12   | TWELVE               |
 11_|      34   | THIRTY FOUR          |
 12_|      51   | FIFTY ONE            |
 13_|      88   | EIGHTY EIGHT         |
 14_|      99   | NINETY NINE          |
 15_|     100   | ONE HUNDRED          |
    +----------------------------------+


Hope this helps.

Dave



Dave
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
Veera
Moderator


Joined: 22 Feb 2006
Posts: 111

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Thu Mar 29, 2007 11:29 pm    Post subject: Reply with quote

DavidatK,

Well it slipped of my mind the co-related sub-query did the trick Smile, always
handy for recursive table processing.

But i have few questions regarding your query DavidatK

1-> The query you have mentioned is a CURSOR or SINGLETON select ??

What i am assuming is since you are using the co-related query
foreach value of N from TT1 it should yeild multiple values
from TT2.

Say when the value of N = 3

the results i am aniticpating is

| N | NM |
+----------------------------------+
1_| 1 | ONE |
2_| 2 | TWO |

Likey for each value of N we should get more than one record as a result.

or

Is it since you are using a singleton select the latest record will override
the value and hence we get unique ???

1 - 10 keys......

But when we use CURSOR the results should be like

1,
1,1
1,2....so on ...

The results you showed is executed on DB2???

Well my interpretation of Query may be wrong , DavidatK can you please throw some light on the Query execution......I will be glad to know.

And oops i forgot ....Welcome Dave to IBMMAINFRAMEGURU.

Thanks,
Veera
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
Display posts from previous:   
Post new topic   Reply to topic    mainframegurukul.com Forum Index -> COBOL All times are GMT + 5 Hours
Page 1 of 1



 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Related topics
 Topics   Replies   Author   Views   Last Post 
No new posts pls give me the answers for the following interview quesions 13 abcdef1234 23442 Thu Sep 08, 2011 2:53 pm
helianthusdd View latest post
No new posts pls give me answers these are may help for jobhunters.. 3 abcdef1234 8757 Thu Sep 23, 2010 12:40 pm
Anuj Dhawan View latest post
No new posts Many JCL statements contain specific values designed to dire 1 Krishna 3971 Tue May 12, 2009 5:39 pm
Krishna View latest post
No new posts give me a code to reverse a COBOL string. 2 mainframe5 11718 Sat Feb 16, 2008 4:14 am
DavidatK View latest post
No new posts CICs 5 vidhya 16501 Fri Nov 17, 2006 12:13 pm
hari.paramasivam View latest post
 





Cobol Tutorial
This cobol tutorial covers most of the important topics like STRING, UNSTRING, COMP, COMP-3.....
DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. Explains in simple language. Some Chapters are locked, Forum members have free access to these chapters
CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming...
JCL Tutorial
This is most popular JCL tutorial from mainframegurukul. It does contain important jcl ....
SORT Tutorial
This Tutorial covers all important aspects of DFSORT. Has more SORT examples
INTERVIEW Questions
This page covers important interview questions
Mainframe Jobs
Mainframe Jobs posted by members of mainframegurukul forum
FaceBook Page
MainframeGurukul FaceBook Page
LinkedIn Page
MainframeGurkul Linkedin Page

This widget requires Flash Player 9 or better







Go to top of the page
 

Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  DB2 INTERVIEW QUESTIONS ||  COBOL INTERVIEW QUESTIONS  ||  JCL INTERVIEW QUESTIONS ||  JCL2 INTERVIEW QUESTIONS ||  VSAM INTERVIEW QUESTIONS ||  CICS INTERVIEW QUESTIONS  ||  Online tutorials ||  Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  SiteMap  ||  Expeditor Tutorial  ||  FILE-AID Tutorial  ||  Changeman Tutorial  ||  COBOL   ||  DB2   ||  JCL  ||  CICS  ||  VSAM  ||  DB2 Interview Questions ( 110 )   || Simple JCL Tutorials  || JCL Tutorial from MainframeGurukul.com   || Simple JCL Tutorial - Chapter1 ;|| Mainframe Forum - Tutorials  || Mainframe Tutorials || Mainframe CICS tutorial|| Mainframe COBOL Tutorial

Drona Educational Forums - Mainframe Cobol DB2 CICS Board
Powered by phpBB

mainframetopic-2390.html