Hi everyone
Hopefully someone can help with me with please. I am using the name GIFTAID as part of a CASE query, but I also need to use GIFTAID in a SELECT statement as shown below. My SQL throws an error as GIFTAID doesn't exist at the point of SELECT. I know what is happening here but I can't work out how to remedy it!
SELECT DISTINCT
ph.STARTDATE, ph.SERIALNUMBER, dc.DESTINATIONGROUP, pi.PLEDGEID, pi.INSTALMENTID, pi.INSTALMENTSTATUS, pi.DATEDUE, pi.INSTALMENT, gd.GADStatus, CASE WHEN (gd.GADstatus)
= 'Active' THEN pi.instalment * (1.25) - pi.INSTALMENT ELSE '0' END AS GIFTAID, pi.INSTALMENT + GIFTAID AS TOTALWITHGIFTAID
FROM dbo.PLEDGEHEADER AS ph INNER JOIN
dbo.PLEDGEINSTALMENT AS pi ON pi.PLEDGEID = ph.PLEDGEID LEFT OUTER JOIN
dbo.GIFTAID_CURRENTDECLARATION AS gd ON ph.SERIALNUMBER = gd.SERIALNUMBER LEFT OUTER JOIN
dbo.DESTINATIONCODE AS dc ON ph.DESTINATIONCODE = dc.DESTINATIONCODE
WHERE (ph.STARTDATE >= '2019-09-01') AND (pi.INSTALMENTSTATUS IN ('Active', 'On Hold'))
Many thanks
Jon
You cannot reference the new alias as it does not exist yet.
Below is 1 way of accessing it
WITH cteBase AS (
SELECT DISTINCT
ph.STARTDATE
, ph.SERIALNUMBER
, dc.DESTINATIONGROUP
, pi.PLEDGEID
, pi.INSTALMENTID
, pi.INSTALMENTSTATUS
, pi.DATEDUE
, pi.INSTALMENT
, gd.GADStatus
, CASE
WHEN (gd.GADstatus) = 'Active' THEN pi.instalment * (1.25) - pi.INSTALMENT
ELSE 0
END AS GIFTAID
FROM dbo.PLEDGEHEADER AS ph
INNER JOIN dbo.PLEDGEINSTALMENT AS pi ON pi.PLEDGEID = ph.PLEDGEID
LEFT OUTER JOIN dbo.GIFTAID_CURRENTDECLARATION AS gd ON ph.SERIALNUMBER = gd.SERIALNUMBER
LEFT OUTER JOIN dbo.DESTINATIONCODE AS dc ON ph.DESTINATIONCODE = dc.DESTINATIONCODE
WHERE ( ph.STARTDATE >= '2019-09-01')
AND ( pi.INSTALMENTSTATUS IN ('Active', 'On Hold') )
)
SELECT cte.*
, cte.INSTALMENT + cte.GIFTAID AS TOTALWITHGIFTAID
FROM cteBase AS cte
September 25, 2019 at 2:19 pm
Alternatively, you can rewrite the query to repeat the CASE statement
SELECT DISTINCT
ph.STARTDATE
, ph.SERIALNUMBER
, dc.DESTINATIONGROUP
, pi.PLEDGEID
, pi.INSTALMENTID
, pi.INSTALMENTSTATUS
, pi.DATEDUE
, pi.INSTALMENT
, gd.GADStatus
, CASE
WHEN (gd.GADstatus) = 'Active' THEN pi.instalment * (1.25) - pi.INSTALMENT
ELSE 0
END AS GIFTAID
, pi.INSTALMENT + CASE
WHEN (gd.GADstatus) = 'Active' THEN pi.instalment * (1.25) - pi.INSTALMENT
ELSE 0
END AS TOTALWITHGIFTAID
FROM dbo.PLEDGEHEADER AS ph
INNER JOIN dbo.PLEDGEINSTALMENT AS pi ON pi.PLEDGEID = ph.PLEDGEID
LEFT OUTER JOIN dbo.GIFTAID_CURRENTDECLARATION AS gd ON ph.SERIALNUMBER = gd.SERIALNUMBER
LEFT OUTER JOIN dbo.DESTINATIONCODE AS dc ON ph.DESTINATIONCODE = dc.DESTINATIONCODE
WHERE ( ph.STARTDATE >= '2019-09-01')
AND ( pi.INSTALMENTSTATUS IN ('Active', 'On Hold') )
September 25, 2019 at 2:20 pm
Excellent, thank you Des!
September 25, 2019 at 2:31 pm
There are two solutions CTE or CROSS APPLY.
SELECT DISTINCT
ph.STARTDATE
,ph.SERIALNUMBER
,dc.DESTINATIONGROUP
,pi.PLEDGEID
,pi.INSTALMENTID
,pi.INSTALMENTSTATUS
,pi.DATEDUE
,pi.INSTALMENT
,gd.GADStatus
,ga.GIFTAID
,pi.INSTALMENT + ga.GIFTAID AS TOTALWITHGIFTAID
FROMdbo.PLEDGEHEADER AS ph
INNER JOIN dbo.PLEDGEINSTALMENT AS pi
ON pi.PLEDGEID = ph.PLEDGEID
LEFT OUTER JOIN dbo.GIFTAID_CURRENTDECLARATION AS gd
ON ph.SERIALNUMBER = gd.SERIALNUMBER
LEFT OUTER JOIN dbo.DESTINATIONCODE AS dc
ON ph.DESTINATIONCODE = dc.DESTINATIONCODE
CROSS APPLY
(
VALUES (
CASE
WHEN (gd.GADstatus) = 'Active' THEN
pi.instalment * (1.25) - pi.INSTALMENT
ELSE '0'
END
)
) ga(GIFTAID)
WHERE (ph.STARTDATE >= '2019-09-01')
AND (pi.INSTALMENTSTATUS IN ( 'Active', 'On Hold' ));
Also pi.instalment * (1.25) - pi.INSTALMENT is equivalent to pi.instalment * (0.25).
Drew
PS: You should use shorter lines when posting code. It makes it easier to read, because you don't have to scroll horizontally to read the entire line.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply