|
|
Posted by Chris H on 10/23/07 14:05
I'm pulling multiple campaigns associated with companies so that I
have a result set with company name, campaign1, campaign2, campaign3,
campaign4, campaign5 where the campaigns are listed in alpha order (by
campaign_code) and each campaign column includes only the one campaign
(or null). When I got to the 5th level of MIN's, the query seemed to
expand way beyond what was required for the 4th level join and I
believe there's "bloat" in the expression. The query works as is, but
I was wondering if anyone has a suggestion for optimizing? I tried to
retain the tabulation when I pasted from TOAD, but had to do some
cleanup anyways so sorry if I didn't get it exactly.
Again, this works, but I think it could be better.
Not show are the joins for campaigns 1-4.
Basic tables are: oncd_company: company_id, company_name_1
oncd_company_campaign: company_campaign_id, company_id, campaign_code
onca_campaign: campaign_code, description, use_for_company
SELECT
oncd_company.company_id,
oncd_company.company_name_1,
campaign_desc5.description d5
FROM oncd_company
LEFT OUTER JOIN oncd_company_campaign campaign5 ON
campaign5.company_id = oncd_company.company_id
AND campaign5.campaign_code =
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <> /* Not like Min */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <> /* Not like Min 2 */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'))
AND campaign_code <> /* Not like Min 3 */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')))
AND campaign_code <> /* Not like Min 4 Subset */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <> /* Not like Min */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <> /* Not like Min 2 */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'))
AND campaign_code <> /* Not like Min 3 */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <> (SELECT
MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')))
AND campaign_code <> /* Not like Min 4 */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'))
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')))
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')))))))
LEFT OUTER JOIN onca_campaign campaign_desc5 ON
campaign_desc5.campaign_code = campaign5.campaign_code
ORDER BY oncd_company.company_name_1
[Back to original message]
|