HELP!!-Y am i getting this error?

biznez · #1 (**permalink**) 01-20-12, 14:46

I am having a difficult time understanding why im getting this error.
when i try to run this query, i get this error..."Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

how to fix this?

select * from dbo.nte21t_notetranpmt a
where a.pmt_id=(select pmt_id,max(prcs_dt) as maxn21t from dbo.nte21t_notetranpmt group by pmt_id)

Thanks

r937 · #2 (**permalink**) 01-20-12, 14:57

the reason is, the subquery is allowed to return only one column

you've got two

select * from dbo.nte21t_notetranpmt a
where a.pmt_id=(select pmt_id,max(prcs_dt) as maxn21t from dbo.nte21t_notetranpmt group by pmt_id)

biznez · #3 (**permalink**) 01-20-12, 15:04

Quote:

Originally Posted by r937

the reason is, the subquery is allowed to return only one column

you've got two

select * from dbo.nte21t_notetranpmt a
where a.pmt_id=(select pmt_id,max(prcs_dt) as maxn21t from dbo.nte21t_notetranpmt group by pmt_id)

how do i fix this cause when i remove pmt_id.. i get this error

"Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(0 row(s) affected)"

r937 · #4 (**permalink**) 01-20-12, 15:08

when used with an equal sign, like you have, the subquery is allowed to return only one row (and only one column)

how you fix it is, you rewrite it

i guess i should point out that nobody can help you because you haven't explained what you're trying to achieve with this query

homerow · #5 (**permalink**) 01-21-12, 07:34

It is difficult to guess what you need, but, maybe the following query is something you can expand upon toward acheiving the desired results:

Code:

select a.* ,p.*
from dbo.nte21t_notetranpmt a
 ,(select pmt_id ,max(prcs_dt) as maxn21t
   from dbo.nte21t_notetranpmt
   group by pmt_id
  ) p
where p.pmt_id = a.pmt_id

r937 · #6 (**permalink**) 01-21-12, 08:02

excellent guess, homerow, i had a feeling that's what he wanted as well

i would have only a.* in the SELECT clause, not p.*, and i would join on the max date as well...

Code:

SELECT a.* 
  FROM ( SELECT pmt_id 
              , MAX(prcs_dt) AS maxn21t
           FROM dbo.nte21t_notetranpmt
         GROUP 
             BY pmt_id ) AS p
INNER
  JOIN dbo.nte21t_notetranpmt AS a             
    ON a.pmt_id = p.pmt_id
   AND a.prcs_dt = p.maxn21t

Internet Services

Web Development

Digital Marketing

Consumer Tech