asked on

using If exists

Why am I keep getting error on if exists? sorry new to "if exists" , if exists, i just needs NewClient to say Yes, if not then No

select id,
clientid,
filename,
filedate ,
      (if exists(select clientcode from clientname where clientcode = 'bofa')
      'yes'
      else
      'No'
      ) as NewClient
from order_shipment
where order_name is null
order by filedate desc

Ephraim Wangoya

Use case instead

select id,
clientid,   
filename, 
filedate ,
      case
        when exists(select clientcode from clientname where clientcode = 'bofa')
          'yes'
        else
          'No'
      end as NewClient
from order_shipment
where order_name is null 
order by filedate desc

Open in new window

Ephraim Wangoya

Missed then

select id,
clientid,   
filename, 
filedate ,
      case
        when exists(select clientcode from clientname where clientcode = 'bofa') then
          'yes'
        else
          'No'
      end as NewClient
from order_shipment
where order_name is null 
order by filedate desc

Open in new window

Ephraim Wangoya

Though the syntax is correct, the query does not seem to be proper.
What is the relationship between clientname table and order_shipment?

Wouldn't be better if you found out if the client exists first before you run the query

eg
declare @Marker varchar(3)

if exists(select clientcode from clientname where clientcode = 'bofa')
@Marker = 'Yes'
else
@Marker = 'No'

select id,
clientid,
filename,
filedate ,
@Marker as NewClient
from order_shipment
where order_name is null
order by filedate desc

VBdotnet2005

ASKER

Incorrect syntax near '@Marker'.

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VBdotnet2005

ASKER

using this one

declare @Marker varchar(3)

if exists(select clientcode from clientname where clientcode = 'bofa')
@Marker = 'Yes'
else
@Marker = 'No'

select id,
clientid,
filename,
filedate ,
@Marker as NewClient
from order_shipment
where order_name is null
order by filedate desc

VBdotnet2005

ASKER

this is good

select id,
clientid,
filename,
filedate ,
case
when exists(select clientcode from clientname where clientcode = 'bofa') then
'yes'
else
'No'
end as NewClient
from order_shipment
where order_name is null
order by filedate desc

VBdotnet2005

ASKER

thank you very much