CODEWITHSUNDEEP
sql-servert-sql
Slinky
Slinky

Reputation: 5832

How to use IF/ELSE in a SELECT - SQL SERVER

I am trying to remove the "s" from the word "years" when the COUNT() is < 2 but my syntax is not right for some reason:

Errors: Incorrect syntax near the keyword 'IF'. Incorrect syntax near the keyword 'convert'.

stuff(
        (
        select ',' + Related_name + ' (' + (select
        IF COUNT(begin_date) > 1 BEGIN convert(varchar(10), COUNT(begin_date))  + ' years)' END
        ELSE BEGIN convert(varchar(10), COUNT(begin_date))  + ' year)'
        from cus_relationship subInnerR
        where subInnerR.master_customer_id = c.master_customer_id
        and subInnerR.related_master_customer_id = innerR.related_master_customer_id
        and subInnerR.relationship_type = 'ADVSPR'
        and subInnerR.relationship_code = 'CLUB'
        and subInnerR.reciprocal_code = 'FACADV')
        from cus_relationship innerR
        where [...]

Upvotes: 1

Views: 518

Answers (4)

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

Try like this(As commented by gvee in comments as this reduces some repeated code!!):-

 select ',' + Related_name + ' (' + (select
    Convert(varchar(10), Count(begin_date)) + ' year' + 
    CASE WHEN Count(begin_date) > 1 THEN 's'    ELSE '' END + ')'
    from cus_relationship subInnerR
    where subInnerR.master_customer_id = c.master_customer_id
    and subInnerR.related_master_customer_id = innerR.related_master_customer_id
    and subInnerR.relationship_type = 'ADVSPR'
    and subInnerR.relationship_code = 'CLUB'
    and subInnerR.reciprocal_code = 'FACADV')
    from cus_relationship innerR
    where [...]

Upvotes: 2

Code Maverick
Code Maverick

Reputation: 20425

I am not a fan of reusing the same code, so I'd use CASE like this:

CONVERT(VARCHAR(10), COUNT(begin_date)) 
+ ' year' 
+ CASE WHEN COUNT(begin_date) > 1 THEN 's' ELSE '' END
+ ')'

split out on multiple lines for readability

Upvotes: 1

mhafellner
mhafellner

Reputation: 468

Maybe this helps

In tsql you use CASE instead of IF

Upvotes: 1

Joe Enos
Joe Enos

Reputation: 40431

You'll need to do this with a CASE statement instead of IF:

case
    when COUNT(begin_date) > 1 then
        convert(varchar(10), COUNT(begin_date))  + ' years)'
    else
        convert(varchar(10), COUNT(begin_date))  + ' year)'
end

Upvotes: 0

Related Questions