IBM Mainframe Forum

by **rajas.abcdefg** » Sun Jul 26, 2009 4:48 am

Hi All,

I need a DB2 V8, SQL Query cursor as below.

(SELECT COL1, COL2
FROM TABLE1 T1 , TABLE2 T2
WHERE T1.COL3=T2.COL4
ORDER BY 1 DESC
, 2 DESC
FETCH FIRST 1 ROW ONLY)
UNION ALL
(SELECT COL1, COL2
FROM TABLE1 T1 , TABLE2 T2
WHERE T1.COL3=T2.COL4
ORDER BY 1 DESC
, 2 DESC
FETCH FIRST 1 ROW ONLY)

ORDER BY 1 DESC
, 2 DESC
FETCH FIRST 1 ROW ONLY ;

Will this be possible?

I tried the following SQL to get this done,

Select all

SELECT COL1, COL2
from (SELECT COL1, COL2
FROM TABLE1 T1 , TABLE2 T2
WHERE T1.COL3=T2.COL4
ORDER BY 1 DESC
, 2 DESC
FETCH FIRST 1 ROW ONLY) as temp1
UNION ALL
SELECT COL1, COL2
from (SELECT COL1, COL2
FROM TABLE1 T1 , TABLE2 T2
WHERE T1.COL3=T2.COL4
ORDER BY 1 DESC
, 2 DESC
FETCH FIRST 1 ROW ONLY) as temp2

ORDER BY 1 DESC
, 2 DESC
FETCH FIRST 1 ROW ONLY ;

But I am facing the error as :
"DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD FETCH. TOKEN ) UNION
EXCEPT WAS EXPECTED "

I am unable to solve the issue and get the required functionality.

Plz help me ..!!!!!!

by **dick scherrer** » Sun Jul 26, 2009 5:36 am

Hello,

It will probably help us help you if you post some representative sample data and the result you want when this sample data is used as input to your process. Do not use "alphabet soup" values (like aa, ab, ad, p1, p2 etc), but rather something everyone can relate to (preferable the real data you are using with values changed as needed to protect confidentiality).

by **rajas.abcdefg** » Sun Jul 26, 2009 5:47 am

Hi Dick ,

My table as Millions of records.

For e.g., COLS1 - Different dates with format MM/DD/YYYY. My intention is to let the latest Date.

col1 char(10)
---------------
05/01/2009
05/01/2009
04/06/2009
01/08/2008

col2 char(10)
---------------
05/01/2009
05/01/2009
04/06/2009
01/08/2008

table 1 and table2 has refential key col3 which is serial number

e.g, col3 small int
--------------------
1
2
3

I hope this might help you a bit

by **dick scherrer** » Sun Jul 26, 2009 6:48 am

Hello,

Please post more comprehensive sample data. Show all relevant columns for a row on the same line (use the Code tag for alignment). The original post mentions 2 tables, but the sample does not show 2 tables. The original mentions a col4 but there is no col4 shown.

For us to help you need to post far more complete info. Also, names like table1 and col3 provide no meaning. When you re-post, use "real" named for the tables and columns.

by **rajas.abcdefg** » Sun Jul 26, 2009 11:56 pm

I have Student table,CLASS table, ANNUAL table IN DB2 VERSION 8

Student table as following columns :
STUDENT_ID (Numeric 6 digit)
STUDENT_Name (char 6 )

CLASS TABLE as following columns :
STUDENT_ID (Numeric 6 digit)--
DATE_EXAM (date)
MARKS (NUMERIC)

ANNUAL TABLE as following columns :
STUDENT_ID (Numeric 6 digit)--
DATE_EXAM (date)
MARKS (NUMERIC)

My requirement is to get only one record of the lastest exam date with its marks that a student had appeared. The main constraint here is that , the student may have appeared both for CLASS and ANNUAL exam on the same date then the output should be based on the marks.

The problem with just not using UNION or UNION ALL, is to improve the performance of the cursor by querying separately the joins as I am dealing with trillions of records i.e Querying Student table and CLASS table has to give one record UNION ALL Student table,ANNUAL table has to give one records --> Out of the resultant two records, I need to fetch the latest record.

Finally My query has to look as below :

(SELECT DATE_EXAM, MARKS
FROM Student T1 ,Class T2
WHERE T1.STUDENT_ID=T2.STUDENT_ID
ORDER BY 1 DESC
, 2 DESC
FETCH FIRST 1 ROW ONLY)
UNION ALL
(SELECT DATE_EXAM, MARKS
FROM Student T1 ,Annual T2
WHERE T1.STUDENT_ID=T2.STUDENT_ID
ORDER BY 1 DESC
, 2 DESC
FETCH FIRST 1 ROW ONLY)

ORDER BY 1 DESC
, 2 DESC
FETCH FIRST 1 ROW ONLY ;

I will send u the sample data

by **rajas.abcdefg** » Mon Jul 27, 2009 12:19 am

Select all

STUDENT TABLE

STUDENT_ID STUDENT_NAME
++++++++++ ++++++++++
123456 JOHN
123457 ABRAHAM
123458 WILLIAMS
123459 DAVID

Annual TABLE

STUDENT_ID DATE_EXAM    MARKS
++++++++++ ++++++++++    ++++++++++
123456       07/10/2009    20
123456       05/20/2009    30
123457       06/10/2009    60
123457       08/15/2009    30
123456       04/10/2008    30
123456       03/27/2009    80
123457       02/10/2009    10
123457       02/12/2008    90
123457       07/10/2008    40
123457       07/12/2009    20

CLASS TABLE

STUDENT_ID DATE_EXAM    MARKS
++++++++++ ++++++++++    +++++++
123456       07/10/2009    10
123456       06/10/2009    20
123457       06/10/2009    20
123457       07/10/2008    30
123456       07/10/2008    30
123456       07/12/2009    40
123457       07/10/2009    10
123457       06/10/2009    20
123457       07/10/2008    30
123457       07/12/2009    40

by **dick scherrer** » Mon Jul 27, 2009 2:55 am

Hello,

Some editing has been done to remove the requested column from the student table description and to consolidate the sample data into one post. Suggest you practice with the "Code" tag to create more readable posts of things like code, jcl and data. There is a Preview function so you can see your post as the forum will see it. Modify until you are satisfied with the alignment and then Submit.

as I am dealing with trillions of records

If this is the case, i suspect that a single query may not run acceptably. . . The shortest amount of code is often the worst performing. . .

What are the results you want from only that sample data?

by **rajas.abcdefg** » Mon Jul 27, 2009 7:28 am

My requirement is to get only one record of the lastest exam date with its marks that a student had appeared.

by **dick scherrer** » Mon Jul 27, 2009 7:56 am

Hello,

That does not show what you want from the sample data. . . That merely describes what you want. . .

There has still been no explanation of why the original post mentions 2 tables, but the sample table explanations and data uses 3. The "referential" column has not been shown in any of the sample data.

Looking back, i see no cursor (that might be improved or otherwise).

How many students will be input to this process? How might they be identified from the student file (i suspect that every student who ever attended is not needed as input, but only "current" students which should hopefully be identifiable without looking at any grades).

Suggest in your next reply, you present the info as though it were a new topic and include all of the info from the previous dialog that helps define the problem. When re-posting this, use copy/paste and the Code tag. Once the consolidated info is posted, i'll edit the preceding to make the topic less fragmented.

Keep in mind that what you have and what you want is completely clear to you, but may not be to others who would like to help.

by **BChat** » Mon Jul 27, 2009 10:14 pm

Hi Rajas,

If you insist for a single query, something like this may work; not a tested version though

Select all

SELECT L2.STUDENT_ID STUDENT
,SUBSTR(L2.LAST_EXAM, 1, 10) EXAM_DATE
,SUBSTR(L2.LAST_EXAM, 11, 1) EXAM
,SUBSTR(L2.LAST_EXAM, 12) MARKS
FROM
(SELECT L1.STUDENT_ID STUDENT_ID
,MAX(L1.LAST_EXAM) LAST_EXAM
FROM
(SELECT E1.STUDENT_ID STUDENT_ID
,MAX(CHAR(E1.EXAM_DATE) || 'A' || CHAR(E1.MARKS)) LAST_EXAM
FROM ANNUAL E1
,STUDENT S1
WHERE E1.STUDENT_ID = S1.STUDENT_ID
GROUP BY S1.STUDENT_ID
UNION ALL
SELECT E2.STUDENT_ID STUDENT_ID
,MAX(CHAR(E2.EXAM_DATE) || 'C' || CHAR(E2.MARKS)) LAST_EXAM
FROM CLASS E2
,STUDENT S2
WHERE E2.STUDENT_ID = S2.STUDENT_ID
GROUP BY S2.STUDENT_ID
) AS L1
GROUP BY L1.STUDENT_ID
) AS L2
WITH UR;

Assumptions:
1) ANNUAL takes precidence if there is both CLASS & ANNUAL exam on the same day

I am sure if this query runs for trillions of records it may take a day or two to run

.

If you ask for a more reasonable solution and have optimization in mind, take these steps;

- This is not to be achieved in one SQL; you need programming
- I assume CLASS & the ANNUAL tables are the trillion record tables and the STUDENT table is a smaller table with a possible filtering criteria

1) Try defining a partitioned tablespace for the CLASS & ANNUAL tables. If you give the format of the STUDENT_ID, we can suggest the partitioning

2) Have a clastered index on STUDENT_ID

3) Have a index on
- STUDENT_ID + EXAM_DATE for CLASS & ANNUAL table (can be a Data partitioned secondary index)
- STUDENT_ID for STUDENT table (if not already there)

4) Run a CURSOR on the STUDENT table applying whatever filtering applicable; for each row do
- Open a cursor on the CLASS & the ANNUAL separately WHERE STUDENT_ID = :STUDENT-ID (read from the STUDENT cursor) ORDER BY STUDENT_ID, EXAM_DATE FOR FETCH OF 1 ROW ONLY OPTIMIZE FOR 1 ROW
- Get 1 record and do all your logic
- Close CLASS & the ANNUAL cursor

OR

Run 2 separate CURSOR like (Add all the filtering on STUDENT)

Select all

SELECT E1.STUDENT_ID STUDENT_ID
,MAX(E1.EXAM_DATE) LAST_EXAM
FROM ANNUAL E1
,STUDENT S1
WHERE E1.STUDENT_ID = S1.STUDENT_ID
GROUP BY S1.STUDENT_ID
ORDER BY E1.STUDENT_ID;

and

Select all

SELECT E1.STUDENT_ID STUDENT_ID
,MAX(E1.EXAM_DATE) LAST_EXAM
FROM CLASS E1
,STUDENT S1
WHERE E1.STUDENT_ID = S1.STUDENT_ID
GROUP BY S1.STUDENT_ID
ORDER BY E1.STUDENT_ID;

Do a file matching logic and get your result set programatically

With trillion records to handle; you need a lot of trial to come up with the most optimized solution

Hope that helps,

BChat

IBM Mainframe Forum

Fetch with UNION ALL

Fetch with UNION ALL

Re: Fetch with UNION ALL

Re: Fetch with UNION ALL

Re: Fetch with UNION ALL

Re: Fetch with UNION ALL

Re: Fetch with UNION ALL

Re: Fetch with UNION ALL

Re: Fetch with UNION ALL

Re: Fetch with UNION ALL

Re: Fetch with UNION ALL