SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


GROUP BY Subcase_Number within several columns


GROUP BY Subcase_Number within several columns

Author
Message
5280_Lifestyle
5280_Lifestyle
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 325
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"
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26540 Visits: 17557
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 Modens 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)
Jo Pattyn
Jo Pattyn
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2814 Visits: 9903
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



5280_Lifestyle
5280_Lifestyle
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 325
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"
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26540 Visits: 17557
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. :-P

_______________________________________________________________

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 Modens 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)
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40446 Visits: 38567
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.

Cool
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)
5280_Lifestyle
5280_Lifestyle
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 325
Lynn,

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

"Nicholas"
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40446 Visits: 38567
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.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search