Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2014
    Posts
    23

    Unanswered: Limiting a sql query to fetch one row from another file

    I'm looking and I'm kinda lost again. The most related thread I could find was this http://www.tek-tips.com/viewthread.cfm?qid=1173569

    So what I'm trying to do is get a select statement that reads the latest date and time from another table if the id and status matches.

    I've come across a few issues when I do this. I either time out because the query is too big, there's duplicate id's due to there being multiple date and times, or I have some kind of syntax error.

    The programmer created two logical files for me to use, since I was having so much trouble with the history file. The first logical contains the history with the status of 'CLO'. The second logical contains the history with the status of 'LOA'.

    But when I try to do an inner join with both logicals, using an aggregate function max() on date and time, it never finishes loading.

    And if I try fetch first 1 ROWS only in a subselect, the syntax is either wrong, or I end up with one row overall instead of the table I was trying to inner join.

    Just to clarify, the end result is that I want two separate columns joined to my main table. The Max(datetime) of 'CLO' and the max(datetime) of 'LOA'.
    Last edited by EiBot; 03-20-15 at 11:43.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    It should work with subqueries in FROM, like:

    SELECT Q1.CLO_datetime, Q2.loa_datetime
    FROM
    (subquery with MAX from CLO) Q1,
    (subquery with MAX from LOA) Q2
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jan 2014
    Posts
    23
    I'd like to avoid using MAX and just get one record...

    I've seen other sql to other databases doing it by order DESC and getting just the top row.

  4. #4
    Join Date
    May 2014
    Posts
    24
    You may try other query other than max() because its kinda complicates your code.

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    It should work using WITH:

    WITH
    temp_table1 AS
    (SELECT datetime FROM CLO_table
    ORDER BY datetime DESC
    FETCH FIRST 1 ROWS ONLY),
    temp_table2 AS
    (SELECT datetime FROM LOA_table
    ORDER BY datetime DESC
    FETCH FIRST 1 ROWS ONLY)
    SELECT T1.datetime AS datetime1, T2.datetime AS datetime2
    FROM temp_table1, temp_table2

    However, you should check the explain plan and see if there are significant differences betwen this query and the MAX query
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  6. #6
    Join Date
    Jan 2014
    Posts
    23
    @aflorin27
    I should check the... explain plan? I'm not sure what you mean.

    I did try out your query. I get this syntax error when I tried it.

    SQL0338 JOIN predicate not valid.

    Code:
                WITH temp_tableLOA AS
                (
                    SELECT temp_tableLOA.Date || ' ' || temp_tableLOA.Time AS oneDATETIMELOA
                    FROM /**/ temp_tableLOA
                    ORDER BY oneDATETIMELOA DESC
                    FETCH FIRST 1 ROWS ONLY
                ),
                temp_tableCLO AS
                (
                    SELECT temp_tableCLO.Date || ' ' || temp_tableCLO.Time AS oneDATETIMECLO
                    FROM /**/ temp_tableCLO
                    ORDER BY oneDATETIMECLO DESC
                    FETCH FIRST 1 ROWS ONLY
                )
                    SELECT 
                    /*insert other columns*/
                    temp_tableLOA.oneDATETIMELOA AS HISTORYDATETIMELOA,
                    CASE WHEN /**/.STAT = 'CLO ' THEN temp_tableCLO.oneDATETIMECLO ELSE NULL END AS HISTORYDATETIMECLO
    
                FROM /*insert main tbl name */ vu, temp_tableLOA, temp_tableCLO
                /* Insert other inner joins, where logic, group bys, yada yada */
    FETCH FIRST 100 ROWS ONLY
    I also have to match up the IDs from the logicals to the main table.
    Last edited by EiBot; 03-26-15 at 15:19.

  7. #7
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    db2 explain tells you the path that DB2 will use to acquire the data. It indicates the use of table scans, index scans etc and will give a 'cost'
    http://www-01.ibm.com/support/knowle.../c0005134.html
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  8. #8
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Something is wrong with your query:

    WITH temp_tableLOA AS
    (
    SELECT temp_tableLOA.Date || ' ' || temp_tableLOA.Time AS oneDATETIMELOA
    FROM /**/ temp_tableLOA

    at the FROM clause you should have one of your table - the one that has rows with status LOA
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  9. #9
    Join Date
    Jan 2014
    Posts
    23
    Okay, so DB2 Explain is a tool used through a command line that helps sees the performance of queries. I'll have to ask the programmer if we have said tool.

    I'm still getting a join error message. If it helps, I'm using the iSeries.

    Code:
                WITH cteLOA AS
                (
                    SELECT temp_tableLOA.Date || ' ' || temp_tableLOA.Time AS oneDATETIMELOA
                    FROM /**/ temp_tableLOA
                    ORDER BY oneDATETIMELOA DESC
                    FETCH FIRST 1 ROWS ONLY
                ),
                cteCLO AS
                (
                    SELECT temp_tableCLO.Date || ' ' || temp_tableCLO.Time AS oneDATETIMECLO
                    FROM /**/ temp_tableCLO
                    ORDER BY oneDATETIMECLO DESC
                    FETCH FIRST 1 ROWS ONLY
                )
                    SELECT 
                    /*other columns*/
                    cteLOA.oneDATETIMELOA AS HISTORYDATETIMELOA,
                    CASE WHEN /**/STAT = 'CLO ' THEN cteCLO.oneDATETIMECLO ELSE NULL END AS HISTORYDATETIMECLO
    
                FROM /**/ vu, cteLOA, cteCLO
    
                /* Insert other inner joins, where logic, group bys, yada yada */

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •