Home Forums SQL Server 2008 Working with Oracle GROUP BY Subcase_Number within several columns RE: GROUP BY Subcase_Number within several columns

  • April 3, 2013 at 1:25 pm

    #1603357

    5280_Lifestyle (4/3/2013)

    As for the ANSI joins, when I tried switching to ANSI some of the rows were left out. With the old outer joins, the output was 243 rows. With the ANSI joins, the output was 239 rows. That's why I left those annoying outer joins in the query. I have roughly 8 months of sql experience, so I'm definitely still wearing my training wheels. I wasn't thinking this (GROUPED.TOTAL_DURATION = AL3.DURATION) all the way through, so bye-bye Duration.

    Jo, I like the way you have the query coded and I will try that out right away.

    Going back to the original query, when converting an ANSI-89 style outer join to an ANSI-92 style outer join some of what you think may be filter criteria and need to stay in the WHERE clause is actually part of the join criteria.

    Based on this, this may be your original query rewritten as an ANSI-92 style join.

    SELECT

    AL21.SUBCASE_NUMBER,

    SUM(AL31.DURATION/60) TOTAL_DURATION

    FROM

    SUBCASE AL21

    LEFT OUTER JOIN ONSITE_TIME_LOG AL31

    ON (AL21.SUBCASE_OBJID = AL31.SUBC_ONSITE2SUBCASE

    AND ((AL21.SUBCASE_NUMBER LIKE '2049356%'

    OR AL21.SUBCASE_NUMBER LIKE '2049462%'

    OR AL21.SUBCASE_NUMBER LIKE '2057852%'

    OR AL21.SUBCASE_NUMBER LIKE '2057877%'

    OR AL21.SUBCASE_NUMBER LIKE '2057897%'

    OR AL21.SUBCASE_NUMBER LIKE '2057930%'

    OR AL21.SUBCASE_NUMBER LIKE '2057948%'

    OR AL21.SUBCASE_NUMBER LIKE '2057963%'

    OR AL21.SUBCASE_NUMBER LIKE '2057984%'

    OR AL21.SUBCASE_NUMBER LIKE '2057996%'

    OR AL21.SUBCASE_NUMBER LIKE '2058015%'

    OR AL21.SUBCASE_NUMBER LIKE '2058032%'

    OR AL21.SUBCASE_NUMBER LIKE '2058049%'

    OR AL21.SUBCASE_NUMBER LIKE '2066771%')

    OR (AL21.SUBCASE_TITLE LIKE '%CITS Audit%'

    OR AL21.SUBCASE_TITLE LIKE '%CITS Audits%'

    OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDIT%'

    OR AL21.SUBCASE_TITLE LIKE '%CITS_Audit%'

    OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDITS%'

    OR AL21.SUBCASE_TITLE LIKE '%CITS_Audits%'

    OR AL21.SUBCASE_TITLE LIKE '%CITS-Audit%'

    OR AL21.SUBCASE_TITLE LIKE '%CITS-Audits%'

    OR AL21.SUBCASE_TITLE LIKE '%CITSAUDIT%'

    OR AL21.SUBCASE_TITLE LIKE '%CITSAudit%'

    OR AL21.SUBCASE_TITLE LIKE '%CITSAUDITS%'

    OR AL21.SUBCASE_TITLE LIKE '%CITSAudits%')))

    GROUP BY

    AL21.SUBCASE_NUMBER

    ORDER BY

    AL21.SUBCASE_NUMBER

    Test it and see if it returns the same result set as the original query.