Problem with alias/case in query
От | T. Steneker |
---|---|
Тема | Problem with alias/case in query |
Дата | |
Msg-id | 20050207142920.7F9C68B9E6E@svr1.postgresql.org обсуждение исходный текст |
Ответы |
Re: Problem with alias/case in query
(Stephan Szabo <sszabo@megazone.bigpanda.com>)
|
Список | pgsql-novice |
Hello, I'm having a problem with the following pretty large and complicated (for me at least ;-)) query: SELECT Campaign.ID, Campaign.name, CampaignCategory.ID AS categoryID, CampaignCategory.name AS categoryName, (MAX(CampaignProductCommission.impressionCommissionFixed) * 1000) AS cpmCommissionFixed, MAX(CampaignProductCommission.clickCommissionFixed) AS clickCommissionFixed, MAX(CampaignProductCommission.leadCommissionFixed) AS leadCommissionFixed, MAX(CampaignProductCommission.saleCommissionFixed) AS saleCommissionFixed, MAX(CampaignProductCommission.saleCommissionVariable) AS saleCommissionVariable, CASE WHEN CampaignSegment_Affiliate.campaignSegment_AffiliateStatusID >= 2 THEN ( CampaignSegment_Affiliate.campaignSegment.ID ) ELSE ( SELECT CampaignSegment.ID FROM CampaignSegment WHERE CampaignSegment.isGeneral AND CampaignSegment.campaignID = Campaign.ID ) END AS "selectedSegmentID" FROM Campaign LEFT JOIN CampaignCategory ON (CampaignCategory.ID = Campaign.campaignCategoryID) LEFT JOIN CampaignSegment ON (CampaignSegment.campaignID = Campaign.ID) LEFT JOIN CampaignSegment_Affiliate ON (CampaignSegment_Affiliate.campaignSegmentID = "selectedSegmentID" AND CampaignSegment_Affiliate.affiliateID = '" . $this->user->ID . "') LEFT JOIN CampaignSegment_CampaignProduct ON (CampaignSegment_CampaignProduct.campaignSegmentID = "selectedSegmentID") LEFT JOIN CampaignProduct ON (CampaignProduct.ID IN ( SELECT campaignProductID FROM CampaignSegment_Affiliate WHERE campaignSegmentID = "selectedSegmentID" )) LEFT JOIN CampaignProductCommmission ON (CampaignProductCommission.campaignProductID = CampaignProduct.ID) WHERE Campaign.campaignStatusID = '2' GROUP BY Campaign.ID, Campaign.name, Campaign.date, CampaignCategory.ID, CampaignCategory.name ORDER BY Campaign.date DESC, Campaign.ID DESC LIMIT 5 OFFSET 0 So what it should do is this: Select campaign ID, campaign name, campaign category ID, campaign category name and the maximum cpm/click/lead and sale commission for the products in the segment the affiliate belongs to. It is possible that the affiliate doesn't belong to a segment yet (CampaignSegment_Affiliate table). In that case it should use the "general" segment, identified by the "isGeneral" field in the "CampaignSegment" table. The problem with this query is that I cannot use "selectedSegmentID" (in the CASE) in the LEFT JOIN beneath. Is there any other way I can do this in one PGSQL query? Or is there anyone with a hint for me ;-)? I already searched google and postgresql's documentation, but didn't find a way to do it yet. Thanks a bunch in advance! I really appreciate your time :). Kind regards, Tim
В списке pgsql-novice по дате отправления: