donnatronious
asked on
Case error
incorrect syntax near "="??????
select TOP 10 A.*,
dbo.udf_EPMS_GenStreetAddr essID(
LASTNAME,
ORIStreetAddress1,
ORICity,
ORIState,
ORIZip5 ,
STDHouseNumber,
STDStreetName,
STDUnitNumber,
STDCity,
STDState,
STDZip5,
STDErrorCode,
STDCASSRecType) AS FUNC_TEST,
CASE FUNC_COMP
WHEN A.StreetAddressID = FUNC_TEST THEN 1 else 0 end
from dbo.DP_StreetAddresses AS A
select TOP 10 A.*,
dbo.udf_EPMS_GenStreetAddr
LASTNAME,
ORIStreetAddress1,
ORICity,
ORIState,
ORIZip5 ,
STDHouseNumber,
STDStreetName,
STDUnitNumber,
STDCity,
STDState,
STDZip5,
STDErrorCode,
STDCASSRecType) AS FUNC_TEST,
CASE FUNC_COMP
WHEN A.StreetAddressID = FUNC_TEST THEN 1 else 0 end
from dbo.DP_StreetAddresses AS A
Hello donnatronious,
I do not think you can refer to the column alias in the CASE expression. Probably needs
to be:
select TOP 10 A.*,
dbo.udf_EPMS_GenStreetAddr essID(
LASTNAME,
ORIStreetAddress1,
ORICity,
ORIState,
ORIZip5 ,
STDHouseNumber,
STDStreetName,
STDUnitNumber,
STDCity,
STDState,
STDZip5,
STDErrorCode,
STDCASSRecType) AS FUNC_TEST,
CASE FUNC_COMP
WHEN A.StreetAddressID = dbo.udf_EPMS_GenStreetAddr essID(
LASTNAME,
ORIStreetAddress1,
ORICity,
ORIState,
ORIZip5 ,
STDHouseNumber,
STDStreetName,
STDUnitNumber,
STDCity,
STDState,
STDZip5,
STDErrorCode,
STDCASSRecType) THEN 1 else 0 end
from dbo.DP_StreetAddresses AS A
Regards,
Patrick
I do not think you can refer to the column alias in the CASE expression. Probably needs
to be:
select TOP 10 A.*,
dbo.udf_EPMS_GenStreetAddr
LASTNAME,
ORIStreetAddress1,
ORICity,
ORIState,
ORIZip5 ,
STDHouseNumber,
STDStreetName,
STDUnitNumber,
STDCity,
STDState,
STDZip5,
STDErrorCode,
STDCASSRecType) AS FUNC_TEST,
CASE FUNC_COMP
WHEN A.StreetAddressID = dbo.udf_EPMS_GenStreetAddr
LASTNAME,
ORIStreetAddress1,
ORICity,
ORIState,
ORIZip5 ,
STDHouseNumber,
STDStreetName,
STDUnitNumber,
STDCity,
STDState,
STDZip5,
STDErrorCode,
STDCASSRecType) THEN 1 else 0 end
from dbo.DP_StreetAddresses AS A
Regards,
Patrick
top 10 in regards to what order?
select l.*
, CASE WHEN l.StreetAddressID = l.FUNC_TEST THEN 1 else 0 end FUNC_COMP
FROM (
select TOP 10 A.*,
dbo.udf_EPMS_GenStreetAddr essID(
LASTNAME,
ORIStreetAddress1,
ORICity,
ORIState,
ORIZip5 ,
STDHouseNumber,
STDStreetName,
STDUnitNumber,
STDCity,
STDState,
STDZip5,
STDErrorCode,
STDCASSRecType) AS FUNC_TEST,
from dbo.DP_StreetAddresses AS A
) l
select l.*
, CASE WHEN l.StreetAddressID = l.FUNC_TEST THEN 1 else 0 end FUNC_COMP
FROM (
select TOP 10 A.*,
dbo.udf_EPMS_GenStreetAddr
LASTNAME,
ORIStreetAddress1,
ORICity,
ORIState,
ORIZip5 ,
STDHouseNumber,
STDStreetName,
STDUnitNumber,
STDCity,
STDState,
STDZip5,
STDErrorCode,
STDCASSRecType) AS FUNC_TEST,
from dbo.DP_StreetAddresses AS A
) l
ASKER
whoops, should just be select A.*
no top ten needed
no top ten needed
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select TOP 10 A.*,
dbo.udf_EPMS_GenStreetAddr
LASTNAME,
ORIStreetAddress1,
ORICity,
ORIState,
ORIZip5 ,
STDHouseNumber,
STDStreetName,
STDUnitNumber,
STDCity,
STDState,
STDZip5,
STDErrorCode,
STDCASSRecType) AS FUNC_TEST,
CASE FUNC_COMP WHEN FUNC_TEST THEN 1 else 0 end
from dbo.DP_StreetAddresses AS A