Sub-query with Case..When..Else (SQL Server 2000)

  • I have a stored procedure with a sub-query which uses Case..When..Else as follows:

    CREATE PROCEDURE StoredProcedure1

    AS

    SELECT Id, EntryDate, Product, ProdPrice, Qty, Rtn,

    (SELECT CASE [X].Rtn

    WHEN 0 THEN

    SUM([X].[Qty] * [X].[ProdPrice])

    ELSE

    SUM([X].[Qty] * [X].[ProdPrice] * -1)

    FROM dbo.Table1 AS X WHERE X.Product = dbo.Table1.Product AND X.EntryDate <= dbo.Table1.EntryDate) AS ProductRunningSum

    FROM dbo.Table1

    RETURN

    When I try to save this stored procedure, I get:

    ADO error: Incorrect syntax near the keyword 'FROM'

    As I've been staring at this for a while now can someone please put me out of my misery ! :crying: (I expect it's really simple to correct).

  • You're missing the END from the CASE

    CASE

    WHEN ...

    ELSE ...

    END

    CREATE PROCEDURE StoredProcedure1

    AS

    SELECT

    Id, EntryDate, Product, ProdPrice, Qty, Rtn,

    (SELECT

    CASE [X].Rtn

    WHEN 0 THEN SUM([X].[Qty] * [X].[ProdPrice])

    ELSE SUM([X].[Qty] * [X].[ProdPrice] * -1)

    END

    FROM dbo.Table1 AS X WHERE X.Product = dbo.Table1.Product AND X.EntryDate <= dbo.Table1.EntryDate)

    AS ProductRunningSum

    FROM dbo.Table1

    GO

    Just as an aside, this is not going to perform well on larger row counts (and by 'large' I mean a few thousand rows). That triangular join kills performance.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you Gail,

    I'm trying to create a running sum in the stored procedure, which I've managed to do in an Access .mdb as follows:

    SELECT Table1.Id, Table1.EntryDate, Table1.Product, Table1.ProdPrice, Table1.Qty, Table1.Rtn, (SELECT Sum(IIf(X.Rtn=False,[X].[Qty]*[X].[ProdPrice],[X].[Qty]*[X].[ProdPrice]*-1)) FROM Table1 AS X WHERE X.Product = Table1.Product AND X.EntryDate <= Table1.EntryDate) AS ProductRunningSum

    FROM Table1

    ORDER BY Table1.EntryDate;

    Which works fine.

    Can you offer any advice on how to do this in a Stored Procedure and maintain performance when there is a large row count ?

    Even with the End added, I've found that I have to group by [X].Rtn to be able to save the SP and even then, it fails to run due to there being more than one row returned by the subquery for some reason (the data is exactly the same as it is in the .mdb file).

    TIA.

    EDIT:

    I've now managed to get the SP working as it should:

    ALTER PROCEDURE StoredProcedure1

    AS

    SELECT Id, EntryDate, Product, ProdPrice, Qty, Rtn,

    (SELECT Sum(CASE [X].Rtn

    WHEN 0 THEN

    [X].[Qty] * [X].[ProdPrice]

    ELSE

    [X].[Qty] * [X].[ProdPrice] * -1

    END)

    FROM dbo.Table1 AS X WHERE X.Product = dbo.Table1.Product AND X.EntryDate <= Table1.EntryDate) AS ProductRunningSum

    FROM dbo.Table1

    RETURN

    All I need to know now is how it should be done !

    TIA for any advice

Viewing 3 posts - 1 through 3 (of 3 total)

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