We help IT Professionals succeed at work.

Case error

Medium Priority
212 Views
Last Modified: 2010-03-19
incorrect syntax near "="??????

select TOP 10 A.*,
dbo.udf_EPMS_GenStreetAddressID(
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
Comment
Watch Question

Commented:
try this

select TOP 10 A.*,
dbo.udf_EPMS_GenStreetAddressID(
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
CERTIFIED EXPERT
Top Expert 2010

Commented:
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_GenStreetAddressID(
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_GenStreetAddressID(
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
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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_GenStreetAddressID(
LASTNAME,
ORIStreetAddress1,
ORICity,
ORIState,
ORIZip5 ,
STDHouseNumber,
STDStreetName,
STDUnitNumber,
STDCity,
STDState,
STDZip5,
STDErrorCode,
STDCASSRecType) AS FUNC_TEST,
from dbo.DP_StreetAddresses AS A
) l

Author

Commented:
whoops, should just be select A.*

no top ten needed
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:

select l.*
, CASE WHEN l.StreetAddressID = l.FUNC_TEST THEN 1 else 0 end FUNC_COMP
FROM (
select A.*,
dbo.udf_EPMS_GenStreetAddressID(
LASTNAME,
ORIStreetAddress1,
ORICity,
ORIState,
ORIZip5 ,
STDHouseNumber,
STDStreetName,
STDUnitNumber,
STDCity,
STDState,
STDZip5,
STDErrorCode,
STDCASSRecType) AS FUNC_TEST,
from dbo.DP_StreetAddresses AS A
) l

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.