Fetch with UNION ALL



IBM's flagship relational database management system

Fetch with UNION ALL

Postby 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 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 ..!!!!!!
rajas.abcdefg
 
Posts: 38
Joined: Thu Jul 09, 2009 8:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Fetch with UNION ALL

Postby 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).
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Fetch with UNION ALL

Postby 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
rajas.abcdefg
 
Posts: 38
Joined: Thu Jul 09, 2009 8:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Fetch with UNION ALL

Postby 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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Fetch with UNION ALL

Postby 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
rajas.abcdefg
 
Posts: 38
Joined: Thu Jul 09, 2009 8:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Fetch with UNION ALL

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

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
rajas.abcdefg
 
Posts: 38
Joined: Thu Jul 09, 2009 8:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Fetch with UNION ALL

Postby 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?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Fetch with UNION ALL

Postby 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.
rajas.abcdefg
 
Posts: 38
Joined: Thu Jul 09, 2009 8:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Fetch with UNION ALL

Postby 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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Fetch with UNION ALL

Postby 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   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 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 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
BChat
 
Posts: 19
Joined: Thu Jun 11, 2009 8:20 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post