Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    4

    Unanswered: Subquery is not introduced with EXISTS

    Can anyone help me on this?

    When I try to save this stored procedure in VS.NET to SQL Server 2000 database, I keep getting the following error and have no idea how to correct it: "ADO error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS"

    ALTER PROCEDURE
    dbo.sProcRevenueForTerminalByAcocuntManager
    (
    @fromDate datetime,
    @toDate datetime
    )

    AS
    SET NOCOUNT ON
    RETURN
    ( SELECT TOP 100 PERCENT
    T2.TerminalCode AS GroupName1,
    T3.TerminalName AS GroupName2,
    T2.AccountManagerName AS DetailName,
    SUM(T1.InvoiceDEDTotalAmount) AS DetailRevenue
    FROM dbo.TBLINVOICEDED T1
    INNER JOIN dbo.TBLCUSTOMERS T2 ON T1.CustomerId = T2.CustomerId
    INNER JOIN dbo.TBLTERMINALDATA T3 ON T2.TerminalCode = T3.TerminalCode
    WHERE (T1.InvoiceDEDDate >= @fromDate) AND (T1.InvoiceDEDDate <= @toDate)
    GROUP BY T2.AccountManagerName, T2.TerminalCode, T3.TerminalName
    HAVING (NOT (SUM(T1.InvoiceDEDTotalAmount) IS NULL))
    ORDER BY T2.TerminalCode, T2.AccountManagerName )
    Last edited by pmpjr; 07-30-04 at 15:37.

  2. #2
    Join Date
    Jul 2004
    Posts
    4

    Smile Things have been resolved

    I deleted the RETURN word which came from the wizard and all was right with the world again!

  3. #3
    Join Date
    Jul 2004
    Posts
    4
    Finally, this is what I used!

    ALTER PROCEDURE dbo.sProcRevenueForTerminalByAcocuntManager

    (
    @fromDate datetime,
    @toDate datetime
    )

    AS
    SET NOCOUNT ON

    SELECT T2.TerminalCode + ' ' + T3.TerminalName AS GroupName, T2.AccountManagerName AS DetailName, SUM(T1.InvoiceDEDTotalAmount)
    AS DetailRevenue
    FROM TBLINVOICEDED T1 INNER JOIN
    TBLCUSTOMERS T2 ON T1.CustomerId = T2.CustomerId INNER JOIN
    TBLTERMINALDATA T3 ON T2.TerminalCode = T3.TerminalCode
    WHERE (T1.InvoiceDEDDate >= @fromDate) AND (T1.InvoiceDEDDate <= @toDate)
    GROUP BY T2.TerminalCode + ' ' + T3.TerminalName, T2.AccountManagerName
    HAVING (NOT (SUM(T1.InvoiceDEDTotalAmount) IS NULL))
    ORDER BY T2.TerminalCode + ' ' + T3.TerminalName, T2.AccountManagerName

    RETURN

  4. #4
    Join Date
    Jul 2004
    Posts
    4
    Thank you for your assistance!
    Last edited by pmpjr; 07-30-04 at 18:59.

Posting Permissions

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