IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Fine Tuning SQL


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Anand Kumar

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai

PostPosted: Thu Nov 12, 2009 2:10 pm
Reply with quote

Hi Experts,

I am having performance a performance issue in a query. Please can you give me your suggestions. This should be performed only with SQL and is a part of a main query.

The data in table is as follows:-

Table Name GWACTD

Code:
Act-No(Decimal 16)   Day-key(Int 4)   Time(Dec12)  Debit-amt(Dec18)
---------------------------------------------------------------------------------
1111                        4            123456          80000
1111                        5            111234          90000
3333                        1            999999          12242
3333                        2            343434          98779
3333                        2            122123          99923



Required result should be one record for every Act-No, with the latest transaction alone. i.e. record with max(day key) and if more than one transaction happens on the same day, the record with the highest time stamp is required.

Hence the result of the select should be as follows:-
Result:-

Code:
Act-No(Decimal 16)   Day-key(Int 4)   Time(Dec12)  Debit-amt(Dec18)
---------------------------------------------------------------------------------
1111                        5            111234          90000
3333                        2            343434          98779


Please can suggest me a simple query which provides the required result.


Regards,
Back to top
View user's profile Send private message
Steve Davies

New User


Joined: 15 Oct 2009
Posts: 32
Location: UK

PostPosted: Thu Nov 12, 2009 2:44 pm
Reply with quote

Your query doesn't sound like a 'Fine Tuning SQL' query or an SQL performance Issue - it sounds like a 'How do I write the SQL to get this output' sort of question !

Have you tried anything yourself, if so please post the SQL you have tried.
Back to top
View user's profile Send private message
Anand Kumar

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai

PostPosted: Thu Nov 12, 2009 7:52 pm
Reply with quote

Hi Davies,
Please find the query


Code:
 
SELECT
 B.ACT-NO
,B.DAY-KEY
,B.DEBIT-AMT
,B.TIME
FROM(
        SELECT
        A.ACT-NO
       ,MAX(CONCAT(CAST(DIGITS(A.DAY-KEY) AS CHAR(10)),CAST(DIGITS(A.TIME) AS CHAR(12)))) AS DAY_KEY_COM
        FROM GWACTD
        GROUP BY
        A.ACT-NO)AS AA
INNER JOIN GWACTD B
ON B.ACT-NO        = AA.ACT-NO
AND B.DAY-KEY      = CAST(SUBSTR(AA.DAY_KEY_COM,1,10)  AS INT)
AND B.TIME         = CAST(SUBSTR(AA.DAY_KEY_COM,11,12) AS DECIMAL(12,0))


Please let me know if there is any other way simpler way.

Regards,
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Thu Nov 12, 2009 8:15 pm
Reply with quote

I don't know if it's SIMPLER ( or even if it's more EFFICIENT ), but I would probably code it thusly:
Code:
SELECT A.ACT-NO
      ,A.DAY-KEY
      ,A.DEBIT-AMT
      ,A.TIME
  FROM GWACTD A
 WHERE A.TIME =
      (SELECT MAX(B.TIME)
         FROM GWACTD B
        WHERE B.ACT-NO = A.ACT-NO
          AND B.DAY-KEY =
             (SELECT MAX(C.DAY-KEY)
                FROM GWACTD C
               WHERE C.ACT-NO = A.ACT-NO)))
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts COBOL Performance Tuning COBOL Programming 6
No new posts a copybook getting improper values in... COBOL Programming 7
No new posts Workload Manager definitions for Tuni... All Other Mainframe Topics 0
No new posts Batch job tuning Testing & Performance 6
No new posts PL/I code tuning/Performance improvement PL/I & Assembler 4
Search our Forums:

Back to Top