On 8/7/07, michael tsang <[EMAIL PROTECTED]> wrote:
> I get this error with SQL listed below.
>
> "Subquery returned more than 1 value. This is not permitted when the
> subquery
> follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
>
>
> I am trying to run the query below but I get the error above.
>
> SELECT
> AM.dbo.PE.REP_ACT_ID,
> AM.dbo.AC.access,
> AM.dbo.AC.userid
> FROM
> AM.dbo.PE_FMS,AM.dbo.AC
> WHERE
> (
> (AM.dbo.PE.REP_ACT_ID= (Case when 'all' =ltrim (rtrim (UPPER ('all')))and (
> (select
> access
> from
> AC
> where
> AM.dbo.AC.access = 'all'
> and
> AM.dbo.AC.USERID ='michael')is not null )then '62' else '' end)
>
> ) )
>
The problem is the way you are referencing the tables in the subquery.
You are not fully qualifying the table name you are selecting from,
but then you are fully qualifying it in the WHERE...so it assumes you
are referring to the table in the upper query where you did fully
qualify it. This is one place where always using aliases can
help...if you have distinct aliases for your table names in both parts
of the query, you are sure where your data comparison is coming
from...your sub query could be re-written:
select access from AM.dbo.AC where AM.dbo.AC.access = 'all' and
AM.dbo.AC.USERID ='michael'
and the query should work.
Also, you have this:
'all' = ltrim(rtrim(UPPER('all')))
which will always resolve to false. Not sure what you are going for
there, but perhaps we are not seeing the whole query.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion is delivering applications solutions at at top companies
around the world in government. Find out how and where now
http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2905
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
