I want to run the following query:

SELECT DISTINCT EBase.EbClock, EPerson.EpStreet1,
EPerson.EpStreet2, EPerson.EpCity, EPerson.EpState,
EPerson.EpZip,
EPerson.EpCountry,
(SELECT EPerson.EpHomePhone = CASE
WHEN EPerson.EpHomePhoneUnlisted = 'y' THEN ''
WHEN EPerson.EpHomePhoneUnlisted = 'Y' THEN ''
ELSE EPerson.EpHomePhone END) as EpHomePhone,
'HOME' AS 'TYPE', '1' AS 'IND' ,'Home' AS 'Label'
FROM EBase, EPerson, EEmploy
WHERE EBase.EbFlxID = EPerson.EpFlxIDEb AND EBase.EbFlxID
=
EEmploy.EeFlxIDEb AND (EEmploy.EeStatus = 'active') AND
(EEmploy.EeDateEnd
IS NULL) ORDER BY ebase.ebclock

I recently modified it by adding:
(SELECT EPerson.EpHomePhone = CASE
WHEN EPerson.EpHomePhoneUnlisted = 'y' THEN ''
WHEN EPerson.EpHomePhoneUnlisted = 'Y' THEN ''
ELSE EPerson.EpHomePhone END) as EpHomePhone,

(previously I had just selected EpPerson.epHomePhone)
When I added the CASE statement I get an error saying that
the syntax is wrong in the CASE statement. I had used
similar case statements in other SQL Query tasks in other
DTS Packages. I can't see what is wrong with the query.
I thought someone else's eyes might.

Thanks in advance.
Mary

Aren't you missing some brackets here and a FROM clause in the outer
statement?
I am not sure why you need the inner statement either. Maybe if you gave us
DDL, sample data , what you want as the result and an explanation of what
you are trying to do we could help you find a different way.


--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Mary" <mmethven (AT) lindquist (DOT) com> wrote

My previous message might have been confusing. The short
SELECT ... CASE statement I put towards the end of my
message really exists within the bigger query. That was
the part I had recently added when it stopped working and
that is why I typed it again later in my message.

What I am doing is pulling employee information from our
HR database from fields in two tables (EBase and
EPerson). The third table (EEmploy) I use in my where
clause to see if the employee is still active.

Today they asked me to determine if the
EpHomePhoneUnlisted field in EPerson contained a 'Y'. If
it does that means the employee doesn't want their home
phone displayed in our contact management database and on
our intranet. That is what the CASE statement is trying
to do and that is the code I just added.

I have done alot of similar case statements and I have
checked and rechecked the syntax in this one (because my
error says I have a syntax error) and it looks fine. So I
thought there might be something simple I am missing.

Thanks for your response.

Mary

Can you post your DDL. and a few lines of data?

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Mary" <mmethven (AT) lindquist (DOT) com> wrote

Here are a few lines of data...

Ebclock EpStreet1 EpStreet2 EpCity EpState
00018 1022 2nd Street Minneapolis MN
03100 1120 S. 4th Str. #205 St. Paul MN

EpZip EpCountry EpHomePhone EpHomePhoneUnlisted
55402 USA 6127792343 Y
55112 USA 5919098909

Type Ind Label
HOME 1 HOME
HOME 1 HOME


I am not sure what a DDL is? Please explain. Thanks!

Mary

DDL is Data Definition language.

What does the structure of your table lok like?

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Mary" <mmethven (AT) lindquist (DOT) com> wrote