Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

using If exists

Posted on 2011-04-22
8
Medium Priority
?
277 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:VBdotnet2005
  • 4
  • 4
8 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35451017
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


0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35451020

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

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35451039

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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:VBdotnet2005
ID: 35451085
Incorrect syntax near '@Marker'.
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 35451090
sorry use set @Marker = 'Yes'


declare @Marker varchar(3)

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

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

Author Comment

by:VBdotnet2005
ID: 35451092
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
0
 

Author Comment

by:VBdotnet2005
ID: 35451094
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
0
 

Author Closing Comment

by:VBdotnet2005
ID: 35451141
thank you very much
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

578 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