Re: How do I avoid this corelated subquery?

From: Michael D (sorengi_at_yahoo.com)
Date: 05/13/04 

Date: 13 May 2004 07:55:45 -0700

Why not use a function ?

CREATE FUNCTION sumLimitAmt (@Amt_Num int)
RETURNS int
AS
BEGIN
   DECLARE @RtnValue int

   if(@Amt_Num = 1)

        SELECT @RtnValue = SUM(l.amt1)
        FROM Limits l
        INNER JOIN TempLimits tl
                ON (
                        l.relid = tl.relid AND
                        l.prodid = tl.prodid
                        )
        WHERE tl.maxdays <= l.days

   if(@Amt_Num = 2)

        SELECT @RtnValue = SUM(l.amt2)
        FROM Limits l
        INNER JOIN TempLimits tl
                ON (
                        l.relid = tl.relid AND
                        l.prodid = tl.prodid
                        )
        WHERE tl.maxdays <= l.days

   RETURN(@RtnValue)
END
go

UPDATE Limits
SET amt1 = amt1 + dbo.sumLimitAmt(1),
  amt2 = amt2 + dbo.sumLimitAmt(2)

--select dbo.sumLimitAmt(1)
--select dbo.sumLimitAmt(2)

Michael D.

Relevant Pages

  • Re: How do I avoid this corelated subquery?
    ... Michael D writes: ... > CREATE FUNCTION sumLimitAmt ... > RETURNS int ... Books Online for SQL Server SP3 at ...
    (microsoft.public.sqlserver.programming)
  • Re: [PATCH 3/7] PCI PM: Fix saving of device state in pci_legacy_suspend
    ... so the icache is going to scream but gcc folks know ... I'll see Michael what exactly the situation is here and if we ... int smp_request_message_ipi ...
    (Linux-Kernel)
  • Re: problem with memcpy and pointers/arrays confusion - again
    ... forced to assume it returns int. ... copy/paste something that is "untested" into their compiler, just to look at the code and provide some comments so I can implement it myself. ... I thought of saving the memory occupation as integer (should actually be size_t, as Michael proposes). ... Not sure I got that part about not storing data in the last double, ...
    (comp.lang.c)
  • Re: Simple question about dllimport
    ... Michael C wrote: ... deviceNumber, int *pHandle); ...
    (microsoft.public.vc.language)
  • Re: Swapping
    ... In comp.lang.c Julie wrote: ... > Michael wrote: ... >> Dan you need to try stuff for yourself ... >> int main{ ...
    (comp.lang.cpp)