Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

GROUP BY Subcase_Number within several columns Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 11:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 12:49 PM
Points: 39, Visits: 278
I posted on forums.oracle a couple hours ago but haven't heard anything yet. Figured I'd try this board as well.


I have a query that I pulled from a Hyperion report and it is going to be used to create a report in SSRS. The query that I exported from the report does not include how the data was massaged in the report. There are several columns and two of them are SUBCASE_NUMBER and DURATION. The report sums the duration for each subcase number. In some cases, there is only one subcase number with one duration time, in other cases there are several of the same subcase numbers with varying duration times. When I run the following query, I get the results I want.

SELECT AL21.SUBCASE_NUMBER, SUM(AL31.DURATION/60) TOTAL_DURATION
FROM SUBCASE AL21, ONSITE_TIME_LOG AL31
WHERE 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


When I try to combine that query to the original query to include the other columns, I'm not entirely sure how to go about it. I am getting 'no rows selected'. Could someone tell me where I'm going wrong? (All of those OR subcase title like '% %' were copied exactly from the Hyperion report query. I realize that the CITS Audits are redundant.)

SELECT AL2.REGION_GFS, AL2.DIVISION_GFS, AL2.GFS_SITE_TYPE, AL1.CASE_TYPE_GROUP, AL1.X_CASE_TYPE_LEVEL1, AL1.X_CASE_TYPE_LEVEL2, 
AL1.X_CASE_TYPE_LEVEL3, AL2.SUBCASE_NUMBER, trunc((AL2.SUBCASE_CREATION_TIME)), trunc((AL2.SUBCASE_FIRST_CLOSE_DATE)),
AL2.FIRST_ON_SCHEDULE_DT, AL3.DURATION, AL3.START_TIME, AL3.OWNER_FIRST_NAME, AL3.OWNER_LAST_NAME, AL3.OWNER_WORK_GROUP,
AL2.SUBCASE_STATUS, AL3.TIME_TYPE, AL1.CASE_NUMBER, AL2.SUBCASE_CONDITION, AL1.CASE_OWNER_FIRST_NAME, AL1.CASE_OWNER_LAST_NAME,
AL1.CASE_OWNER_WORK_GRP, AL2.SUBCASE_TITLE
FROM CASE AL1, SUBCASE AL2, ONSITE_TIME_LOG AL3,
(SELECT AL21.SUBCASE_NUMBER, SUM(AL31.DURATION/60) TOTAL_DURATION
FROM SUBCASE AL21, ONSITE_TIME_LOG AL31
WHERE 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) GROUPED
WHERE ((AL2.SUBCASE_OBJID = AL3.SUBC_ONSITE2SUBCASE (+)
AND AL1.CASE_OBJID=AL2.SUBCASE2CASE)
AND (GROUPED.SUBCASE_NUMBER = AL2.SUBCASE_NUMBER
AND GROUPED.TOTAL_DURATION = AL3.DURATION))
AND (((AL2.SUBCASE_NUMBER LIKE '2049356%'
OR AL2.SUBCASE_NUMBER LIKE '2049462%'
OR AL2.SUBCASE_NUMBER LIKE '2057852%'
OR AL2.SUBCASE_NUMBER LIKE '2057877%'
OR AL2.SUBCASE_NUMBER LIKE '2057897%'
OR AL2.SUBCASE_NUMBER LIKE '2057930%'
OR AL2.SUBCASE_NUMBER LIKE '2057948%'
OR AL2.SUBCASE_NUMBER LIKE '2057963%'
OR AL2.SUBCASE_NUMBER LIKE '2057984%'
OR AL2.SUBCASE_NUMBER LIKE '2057996%'
OR AL2.SUBCASE_NUMBER LIKE '2058015%'
OR AL2.SUBCASE_NUMBER LIKE '2058032%'
OR AL2.SUBCASE_NUMBER LIKE '2058049%'
OR AL2.SUBCASE_NUMBER LIKE '2066771%')
OR (AL2.SUBCASE_TITLE LIKE '%CITS Audit%'
OR AL2.SUBCASE_TITLE LIKE '%CITS Audits%'
OR AL2.SUBCASE_TITLE LIKE '%CITS_AUDIT%'
OR AL2.SUBCASE_TITLE LIKE '%CITS_Audit%'
OR AL2.SUBCASE_TITLE LIKE '%CITS_AUDITS%'
OR AL2.SUBCASE_TITLE LIKE '%CITS_Audits%'
OR AL2.SUBCASE_TITLE LIKE '%CITS-Audit%'
OR AL2.SUBCASE_TITLE LIKE '%CITS-Audits%'
OR AL2.SUBCASE_TITLE LIKE '%CITSAUDIT%'
OR AL2.SUBCASE_TITLE LIKE '%CITSAudit%'
OR AL2.SUBCASE_TITLE LIKE '%CITSAUDITS%'
OR AL2.SUBCASE_TITLE LIKE '%CITSAudits%')))



"Nicholas"
Post #1438512
Posted Wednesday, April 3, 2013 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,282, Visits: 12,116
Given the lack of details it is very difficult to offer much assistance here. I don't really understand why you have the subquery at all. I would also recommend that use the newer join styles instead of the old styles. It is much easier to read and less likely to produce accidental cartesian products. Additionally I would suggest to use meaningful aliases instead of just numbering them.

This query should produce the same thing as the first query you posted. It is however a lot shorter and a lot easier to read.

SELECT sc.SUBCASE_NUMBER, SUM(otl.DURATION/60) AS TOTAL_DURATION
FROM SUBCASE sc
JOIN ONSITE_TIME_LOG otl on sc.SUBCASE_OBJID = otl.SUBC_ONSITE2SUBCASE
WHERE
LEFT(sc.SUBCASE_NUMBER, 7) IN
(
'2049356',
'2049462',
'2057852',
'2057877',
'2057897',
'2057930',
'2057948',
'2057963',
'2057984',
'2057996',
'2058015',
'2058032',
'2058049',
'2066771'
)
OR sc.SUBCASE_TITLE LIKE '%CITS%Audit%'
GROUP BY sc.SUBCASE_NUMBER
ORDER BY sc.SUBCASE_NUMBER

Now I realize I haven't actually helped with your issue. Are you doing this in Oracle or SQL?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438526
Posted Wednesday, April 3, 2013 12:47 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:17 AM
Points: 1,390, Visits: 6,325
Hello Nicolas,
Where does the condition GROUPED.TOTAL_DURATION = AL3.DURATION come from?

You could also try to use a "WITH clause"
WITH mysubquery AS
(SELECT ...)
SELECT EXTRA.*
FROM EXTRA
INNER JOIN mysubquery on extra.X=mysubquery.Y

My attempt to an rewritten query leads to (replacing ... = (+) with left join)
SELECT AL2.REGION_GFS, AL2.DIVISION_GFS, AL2.GFS_SITE_TYPE, AL1.CASE_TYPE_GROUP, AL1.X_CASE_TYPE_LEVEL1, AL1.X_CASE_TYPE_LEVEL2, 
AL1.X_CASE_TYPE_LEVEL3, AL2.SUBCASE_NUMBER, trunc((AL2.SUBCASE_CREATION_TIME)), trunc((AL2.SUBCASE_FIRST_CLOSE_DATE)),
AL2.FIRST_ON_SCHEDULE_DT, AL3.DURATION, AL3.START_TIME, AL3.OWNER_FIRST_NAME, AL3.OWNER_LAST_NAME, AL3.OWNER_WORK_GROUP,
AL2.SUBCASE_STATUS, AL3.TIME_TYPE, AL1.CASE_NUMBER, AL2.SUBCASE_CONDITION, AL1.CASE_OWNER_FIRST_NAME, AL1.CASE_OWNER_LAST_NAME,
AL1.CASE_OWNER_WORK_GRP, AL2.SUBCASE_TITLE
FROM CASE AL1
INNER JOIN SUBCASE AL2 ON AL1.CASE_OBJID=AL2.SUBCASE2CASE
LEFT JOIN ONSITE_TIME_LOG AL3 ON AL2.SUBCASE_OBJID = AL3.SUBC_ONSITE2SUBCASE
INNER JOIN
(SELECT AL21.SUBCASE_NUMBER, SUM(AL31.DURATION/60) TOTAL_DURATION
FROM SUBCASE AL21
LEFT JOIN ONSITE_TIME_LOG AL31
ON AL21.SUBCASE_OBJID = AL31.SUBC_ONSITE2SUBCASE
WHERE
(
(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) GROUPED
ON GROUPED.SUBCASE_NUMBER = AL2.SUBCASE_NUMBER
AND GROUPED.TOTAL_DURATION = AL3.DURATION


Post #1438539
Posted Wednesday, April 3, 2013 1:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 12:49 PM
Points: 39, Visits: 278
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.


"Nicholas"
Post #1438547
Posted Wednesday, April 3, 2013 1:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,282, Visits: 12,116
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.


No worries about training wheels. My guess is that when you used correct joins you accidentally fat fingered something. Also as for the two "groups" of OR sections there is no point. They are all OR so there is no need to use the extra conditions.

WHERE 
(
(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%'
)
)

The extra parenthesis make this more confusing trying to figure out the differences. I would much prefer the one I posted originally. It is the same thing but a ton easier on the eyes.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438553
Posted Wednesday, April 3, 2013 1:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438559
Posted Wednesday, April 3, 2013 1:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 12:49 PM
Points: 39, Visits: 278
Lynn,

I tried using that query and I canceled the query after waiting for about 8 minutes. It didn't like that version.


"Nicholas"
Post #1438563
Posted Wednesday, April 3, 2013 1:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
5280_Lifestyle (4/3/2013)
Lynn,

I tried using that query and I canceled the query after waiting for about 8 minutes. It didn't like that version.


It was worth a shot, but what I talked about is still valid. In the ANSI-89 style joins (everything in the HWERE clause) some of what you may think of as filter conditions, actually needs to move from the WHERE clause to the ON clause when converting to the ANSI-92 style outer joins.

I'd suggest working with a smaller test set of data if you can and go from there when converting from ANSI-89 to ANSI-92 style outer joins.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438566
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse