Subquery returned more than 1 value.

  • I get this error when trying to run this query:

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    SELECT MAX(DayKey),Port_ID

    FROM [USTG_ODS].dbo.tblSTAGE_INT_Portfolio WHERE Port_ID =

    (

    SELECT DISTINCT Port_ID

    FROM USTG_ODS.dbo.tblSTAGE_INT_Portfolio

    )

    GROUP BY port_id

    I am trying to get the row which has the MAX DayKey and its associated Port_ID. Otherwise, I end up with duplicate Port_IDs. I know there must be something to do with a JOIN here, but I can't seem to get it.

  • You need to use an in keyword instead of =

    Your sub query is return more then one row, but your where clause before the sub query has an =.

    If you replace it with an in it should work fine.

    Ben

  • duanecwilson (9/21/2011)


    I get this error when trying to run this query:

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    SELECT MAX(DayKey),Port_ID

    FROM [USTG_ODS].dbo.tblSTAGE_INT_Portfolio WHERE Port_ID =

    (

    SELECT DISTINCT Port_ID

    FROM USTG_ODS.dbo.tblSTAGE_INT_Portfolio

    )

    GROUP BY port_id

    I am trying to get the row which has the MAX DayKey and its associated Port_ID. Otherwise, I end up with duplicate Port_IDs. I know there must be something to do with a JOIN here, but I can't seem to get it.

    How are you getting duped port_ids if you do this:

    SELECT port_ID, MAX(DayKey) AS MaxDayKey

    FROM [USTG_ODS].dbo.tblSTAGE_INT_Portfolio

    GROUP BY port_id

    ?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • That was it. I was close and didn't see it. Thanks.

  • The above post solved my problem. To answer your question, the inner query gave me more than one port_id. I misspoke when I said duplicates because I had been working on a larger query that surrounded this one and it was giving me duplicates. This particular inner query just gave me the error message I spoke of because it gave me a list of port_ids.

    Thank you for the challenge, though, because it made me think, and I need to more accurately word my questions.

  • Glad I could help... if a bit from the side-like. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • duanecwilson (9/21/2011)


    I had been working on a larger query that surrounded this one

    In that case, you might consider using a CROSS APPLY instead of Max().

    SELECT DayKey, Port_ID

    FROM <<Main Query>>

    CROSS APPLY (

    SELECT TOP (1) DayKey, Port_ID

    FROM USTG_ODS.dbo.tblStage_INT_Portfolio

    WHERE <<conditions>>

    ORDER BY DayKey DESC

    ) AS DayKey

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply