Case statement in select

Transact-SQL
1

I am havign trouble to get around the syntax error with alias names.

select case when exists(select * from x where isComp = 'Y')
then select "1"
Else
select "0" as accntId
From A
cross apply (select isComp from dbo.Fn_GetComp(a.id) as x)
where a.id > 100.

it throws error x is not defined at select clause; can u pls help me with it?

2

should be:

cross apply (select isComp from dbo.Fn_GetComp(a.id)) as x

it would probably help if you format the code in a structured fashion, e.g.

cross apply
( 
    select isComp
    from dbo.Fn_GetComp(a.id)
) as x

so you can see which parenthesis line up with what ...

3 
select case when exists(select * from x where isComp = 'Y')
   then select "1"
Else
   select "0" 
end as accntId
From A
cross apply (
select isComp from dbo.Fn_GetComp(a.id)
 )   as x



where a.id > 100

Exactly I have the same that you mentioned but still ti says "invalid object name 'x'"

4

OK, you've fixed that since O/P.

The error is refetring to

(select * from x where isComp = 'Y')

Presumably you have no table / view X. Looks like you are trying to refer to the results of the CROSS APPLY but you can't do that, you can only refer to the CURRENT ROW in the X cross apply, not ALL ROWS.

You could do

OUTER apply (
select isComp from dbo.Fn_GetComp(a.id)
 )   as x

and then

select case when isComp IS NULL
   then select '1'
Else
   select '0' 
end as accntId

but that will only work if dbo.Fn_GetComp() NEVER returns NULL for [isComp]

5

A CASE must yield a single ("scalar" in tech terms) value;SQL Server uses single quotes for literals, not double; and you must specify END. To match those rules, you can do this:

select case when exists(select * from x where isComp = 'Y')
then '1 ' Else '0' end
From A

Or this:

select case when exists(select * from x where isComp = 'Y')
then (select '1') Else (select '0') end
From A