?
Solved

Case error

Posted on 2007-07-20
5
Medium Priority
?
177 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
0
Comment
Question by:donnatronious
5 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 19531911
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
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19531928
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19531950
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

0
 

Author Comment

by:donnatronious
ID: 19531975
whoops, should just be select A.*

no top ten needed
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 19532024

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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question