Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2016
    Posts
    3

    Unanswered: Sub-query Question, Please

    Hi Folks,

    Please help on filtering with a sub-query. I have so many joins and am lost here...

    Code:
    SELECT ATE_SERIAL, DATA_DATA, DYN_VALUE FROM ATE_TESTS 
    LEFT JOIN ATE_TEST_PROCEDURE ON ATE_TESTS.ATE_PK = 
    ATE_TEST_PROCEDURE.PROC_ATE_TEST_FK 
    LEFT JOIN ATE_TEST_DATA ON ATE_TEST_PROCEDURE.PROC_PK = ATE_TEST_DATA.DATA_ATE_TEST_PROCEDURE_FK 
    LEFT JOIN TM_TEST_PROCEDURE ON ATE_TEST_PROCEDURE.PROC_TEST_PROCEDURE = TM_TEST_PROCEDURE.PROC_PK 
    LEFT JOIN TM_TEST_SPECIFICATION ON ATE_TEST_DATA.DATA_SPECIFICATION = TM_TEST_SPECIFICATION.SPEC_PK 
    LEFT JOIN TM_TEST_CONDITION_DYNAMIC ON TM_TEST_SPECIFICATION.SPEC_CONDITION_SET_FK = TM_TEST_CONDITION_DYNAMIC.DYN_CONDITION_SET_FK 
    LEFT JOIN TM_TEST_SEQUENCES ON ATE_TESTS.ATE_SEQUENCE_FK = 
    TM_TEST_SEQUENCES.SEQ_PK 
    LEFT JOIN LU_TM_PRODUCTS_MODEL ON TM_TEST_SEQUENCES.SEQ_MODEL = LU_TM_PRODUCTS_MODEL.LUMOD_PK 
    WHERE 
    UPPER(SPEC_NAME)='POWER'
    AND LUMOD_MODEL='AMP'
    AND DYN_VALUE ='136'
    AND ATE_YIELD=1 
    AND UPPER(PROC_PROCEDURE_NAME)='FINAL TEST' 
    AND PROC_REPORT=1 
    AND PROC_STATUS=1 
    ORDER BY ATE_SERIAL, DYN_VALUE
    My problem of course, is that there may be many rows in ATE_TEST_DATA and I only want the latest. I want to use the incremented Primary Key, ATE_TEST_DATA.DATA_PK to snag the last entry only and know a sub-query will do what I need. But my experience with sub-queries has been limited to one table or a single-join. This query has many joins, I have no idea where to insert the sub.

    Could someone help me with this, please?

    Thanks,
    -John

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,520
    With out your tables I have no idea if this would work but check it out.

    Code:
      SELECT Ate_serial, Data_data, Dyn_value
        FROM (SELECT Ate_serial,
                     Data_data,
                     Dyn_value,
                     ROW_NUMBER ()
                     OVER (PARTITION BY Ate_serial, Data_data, Dyn_value
                           ORDER BY Ate_test_data.Data_pk DESC)
                         Rn
                FROM Ate_tests
                     LEFT JOIN Ate_test_procedure
                         ON Ate_tests.Ate_pk = Ate_test_procedure.Proc_ate_test_fk
                     LEFT JOIN Ate_test_data
                         ON Ate_test_procedure.Proc_pk =
                                Ate_test_data.Data_ate_test_procedure_fk
                     LEFT JOIN Tm_test_procedure
                         ON Ate_test_procedure.Proc_test_procedure =
                                Tm_test_procedure.Proc_pk
                     LEFT JOIN Tm_test_specification
                         ON Ate_test_data.Data_specification =
                                Tm_test_specification.Spec_pk
                     LEFT JOIN Tm_test_condition_dynamic
                         ON Tm_test_specification.Spec_condition_set_fk =
                                Tm_test_condition_dynamic.Dyn_condition_set_fk
                     LEFT JOIN Tm_test_sequences
                         ON Ate_tests.Ate_sequence_fk = Tm_test_sequences.Seq_pk
                     LEFT JOIN Lu_tm_products_model
                         ON Tm_test_sequences.Seq_model =
                                Lu_tm_products_model.Lumod_pk
               WHERE     UPPER (Spec_name) = 'POWER'
                     AND Lumod_model = 'AMP'
                     AND Dyn_value = '136'
                     AND Ate_yield = 1
                     AND UPPER (Proc_procedure_name) = 'FINAL TEST'
                     AND Proc_report = 1
                     AND Proc_status = 1)
       WHERE Rn = 1
    ORDER BY Ate_serial, Dyn_value
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2016
    Posts
    3
    Hi beilstwh.

    Your code returns the same records as the original query (including the unwanted row from ATE_DATA).

    Thank you very much for the try, though!

    -John

  4. #4
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    try something along these lines:

    Code:
    LEFT JOIN table(select columns you need from this table, max(guess_a_date_col_that_you_want_the_most_recent_of)
                              from ATE_TEST_DATA
                           where ATE_TEST_PROCEDURE.PROC_PK = ATE_TEST_DATA.DATA_ATE_TEST_PROCEDURE_FK
                           group by columns you are selecting here) as test_data
     ON ATE_TEST_PROCEDURE.PROC_PK = TEST_DATA.DATA_ATE_TEST_PROCEDURE_FK
    You can do without the WHERE clause in the above if you are going to be getting most/all of the rows from this table

  5. #5
    Join Date
    Dec 2016
    Posts
    3
    Quote Originally Posted by DNance View Post
    try something along these lines:

    Code:
    LEFT JOIN table(select columns you need from this table, max(guess_a_date_col_that_you_want_the_most_recent_of)
                              from ATE_TEST_DATA
                           where ATE_TEST_PROCEDURE.PROC_PK = ATE_TEST_DATA.DATA_ATE_TEST_PROCEDURE_FK
                           group by columns you are selecting here) as test_data
     ON ATE_TEST_PROCEDURE.PROC_PK = TEST_DATA.DATA_ATE_TEST_PROCEDURE_FK
    You can do without the WHERE clause in the above if you are going to be getting most/all of the rows from this table
    Thank you. I believe you rescued me once before. But I do not understand what you are telling me here. Your snippet does not make sense to me and I have no idea where it should be inserted into the original query.

  6. #6
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    in place of this outer join
    LEFT JOIN Ate_test_data
    ON Ate_test_procedure.Proc_pk =
    Ate_test_data.Data_ate_test_procedure_fk

Posting Permissions

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