help needed for a query.

Hi

MailMethodId Is an integer, and it has 2 possible values 1 which means 'Mail to Participant' or 2 which means 'Mail To client'

select

cp.PlanId,

cp.ClientPlanId,

psi.MailMethodId,

psi.StatementTypeId,

cp.PlanName,

cp.ClientId ,

c.ClientName

from ClientPlan cp

Join PlanStatementInfo psi on cp.PlanId = psi.PlanId

Join Client c on cp.ClientId = c.ClientId

Where cp.ClientId = @.ClientId

Union

Select

cp.PlanId,

cp.ClientPlanId,

2,

1,

cp.PlanName,

cp.ClientId,

c.ClientName

From

ClientPlan cp

innerjoin Client c on cp.ClientId = c.ClientId

where

cp.ClientId = @.ClientId

and

cp.PlanId NotIN

(Select psi.PlanId from PlanStatementinfo psi)

So how can i get the Mail methodId to display 'Mail to Participant' if the data is 1 and 'Mail to Client' if the Data is 2

i tried doing this

case When psi.MailMethodId = 1 then 'Mail to Participant' Else 'Mail to Client' End,

instead of psi.MailmethodId, but i am getting a error message that says

Syntax error converting the varchar value 'Mail to Participant' to a column of data type int.

Any Help will be appreciated

Regards

KAren

Karen:

My knee-jerk reaction to this is that you probably have an incompatibility between how the "MailMethodID" column is used in each separate SELECT that comprises the UNION. Try it like this:

Code Snippet

select
cp.PlanId,
cp.ClientPlanId,
case When psi.MailMethodId = 1 then 'Mail to Participant' Else 'Mail to Client' End,
psi.StatementTypeId,
cp.PlanName,
cp.ClientId ,
c.ClientName
from ClientPlan cp
Join PlanStatementInfo psi on cp.PlanId = psi.PlanId
Join Client c on cp.ClientId = c.ClientId
Where cp.ClientId = @.ClientId
Union
Select
cp.PlanId,
cp.ClientPlanId,
'Mail to Client',
1,
cp.PlanName,
cp.ClientId,
c.ClientName
From
ClientPlan cp
inner join Client c on cp.ClientId = c.ClientId
where
cp.ClientId = @.ClientId
and
cp.PlanId Not IN
(Select psi.PlanId from PlanStatementinfo psi)

|||

Thanks a lot Ken, that worked.