Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unhappy Unanswered: Subquery returns more than one value

    Hi folks,

    I have a problem where my subquery is returning two values and I need it to return only one. I believe it is doing this because of the group by DocType clause as there are two DocTypes.

    Is there anyway I can sum these values in the subquery, to return only one value. This way the query should run ok.

    Thanks for any help you might be able to give,

    robinsql

    SELECT OCRD.Cardcode,
    OCRD.cardname,
    @YearStart AS YearStart,
    @YearEnd AS YearEnd,
    @PeriodStart AS PeriodStart,
    @PeriodEnd AS PeriodEnd,
    OCRD.County,
    (SELECT CASE WHEN OINV.DocType = 'I' AND SUM(INV1.Quantity) <>0 THEN COALESCE(SUM(INV1.Price * INV1.Quantity), 0) ELSE COALESCE(SUM(INV1.Price), 0) END FROM INV1, OINV WHERE INV1.DocEntry = OINV.DocEntry AND OINV.CardCode = OCRD.CardCode AND OINV.DocDate >= @YearStart AND OINV.DocDate <= @YearEnd GROUP BY OINV.DocType) AS YTDInvoice,
    FROM
    OCRD, OINV
    WHERE
    CardType='C'

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if there's more than one DocType, then yes, your subquery will return more than one row

    therefore, remove the GROUP BY

    you must also therefore change the CASE expression so that the DocType test occurs inside the SUM, not outside
    Code:
    SELECT SUM(INV1.Price * 
               CASE WHEN OINV.DocType = 'I' 
                    THEN INV1.Quantity
                    ELSE 1 END)
      FROM INV1 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Posts
    2
    Thank you very much for your help. This seems to have the problem sorted.

    Much appreciated,

    robinsql

Posting Permissions

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