Prev: sql 2005 Shrinking User Database
Next: DBA document for an organization?
From: cheenujunk on 22 Jul 2010 16:06
I am using the following query

select 'level' =

case when dbo.function1 = 1 then

( case when ( dbo.function2(dbo.function3, 1) > 0) then 3

end )

end

it is showing error "Cannot find either column "dbo" or the user-defined
function or aggregate dbo.function3 "

dbo.function3 is TVF.

so i tried with this

select 'level' =

case when dbo.function1 = 1 then

( case when ( dbo.function2((select * from dbo.function3), 1) > 0) then 3

end )

end

then i have

"Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS."

ONLY function3 is TVF all others are scalar.

Let me know whats the right way to code this.

From: Erland Sommarskog on 22 Jul 2010 16:30
cheenujunk (u62347(a)uwe) writes:
> dbo.function3 is TVF.
>
> so i tried with this
>
> select 'level' =
>
> case when dbo.function1 = 1 then
>
> ( case when ( dbo.function2((select * from dbo.function3), 1) > 0) then 3
>
> end )
>
> end
>
> then i have
>
> "Only one expression can be specified in the select list when the
> subquery is not introduced with EXISTS."

What the message says. You cannot use SELECT * which can be many columns,
but you need a scalar value. I assume that there is one column you are
after, and you need to specify that one. Also, for the above to work, the
TVF must return exactly one row.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: sql 2005 Shrinking User Database
Next: DBA document for an organization?