Re: Problem with alias/case in query

On Mon, 7 Feb 2005, T. Steneker wrote:

> 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.

You may want to look at moving the determination of "selectedSegmentID"
into a subquery in the from clause. It looks like you could join with a
subquery that joins CampaignSegment and CampaignSegment_Affiliate which
provides all of their columns plus the selectedSegmentID and then
reference that in the select clause and other joins.