Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    24

    Unanswered: HELP!!-Y am i getting this error?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    24
    Quote Originally Posted by r937 View Post
    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)"

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: HELP!!-Y am i getting this error?

    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •