Problem with alias/case in query

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