Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    87

    Thumbs up Unanswered: How to Simplify this Query

    hi,



    SELECT SET_MST.SET_ID, SET_MST.SET_DESCRIPTION, t1.cnt, t2.cnt
    FROM BSDMASTER.TBSD_SET_MST SET_MST,
    (SELECT SET_ID, count(*) cnt from tbsd_call_report_mst
    WHERE MANDATORY='Y' GROUP BY SET_ID) t1,
    (SELECT SET_ID, count(*) cnt from tbsd_call_report_mst GROUP BY SET_ID ) t2
    WHERE INST_TYPE_CODE='001' AND OPR_CODE='002' AND FUNC_CODE='001' AND FREQ_CODE='002'
    AND t1.SET_ID = SET_MST.SET_ID AND t2.SET_ID = SET_MST.SET_ID


    This query is working properly. Is there any way to simplify this query?

    Thanx for all kind of suggestion.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to Simplify this Query

    Try this:
    Code:
    SELECT SET_MST.SET_ID, SET_MST.SET_DESCRIPTION, t1.cnt1, t1.cnt2
      FROM BSDMASTER.TBSD_SET_MST SET_MST,
           (SELECT SET_ID, sum(decode(mandatory, 'Y', 1, 0 )) cnt1, count(*) cnt2
              from tbsd_call_report_mst
             GROUP BY SET_ID) t1
     WHERE INST_TYPE_CODE='001'
       AND OPR_CODE='002'
       AND FUNC_CODE='001'
       AND FREQ_CODE='002'
       AND t1.SET_ID = SET_MST.SET_ID
    Last edited by andrewst; 01-29-04 at 11:46.

  3. #3
    Join Date
    Nov 2003
    Posts
    87
    thanx for your reply.

    I wanna know which query will be the fastest?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well you should try it and see! My money is on the later query, because it only has to scan tbsd_call_report_mst once not twice.

  5. #5
    Join Date
    Aug 2001
    Posts
    66
    Nice approach TA (as usual).

    For the sake of correctness you have an unwanted comma after 't1' table alias.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Thanks, Padderz - I have corrected it now.

Posting Permissions

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