Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25

    Unanswered: How to do this the T-SQL way.

    I have this code in VB...
    Code:
    OpenOrdSQL = "SELECT JPO, QtyDlv, OutStandVal FROM ActiveOrders WHERE JPO < '5000000' " & _
                 "ORDER BY JPO ASC"
    Set OpenOrdRS = New ADODB.Recordset
    OpenOrdRS.Open OpenOrdSQL, DB
    
    Do Until OpenOrdRS.EOF
       ActiveSQL = "SELECT * FROM fn_ActiveOrders('" & OpenOrdRS!JPO & "')"
       Set ActiveRS = New ADODB.Recordset
       ActiveRS.Open ActiveSQL, DB
       
       If ActiveRS.EOF Then
          SQL = "DELETE FROM ActiveOrders WHERE JPO = '" & OpenOrdRS!JPO & "'"
          DB.Execute SQL
       Else
          If (Int(OpenOrdRS!QtyDlv) - Int(ActiveRS!QtyDlv)) Or (Int(OpenOrdRS!OutStandVal) - Int(ActiveRS!OpenVal)) Then
             SQL = "UPDATE ActiveOrders SET OutStandVal = '" & ActiveRS!OpenVal & _
                   "', SQM = '" & ActiveRS!OpenSQM & "', QtyDlv = '" & ActiveRS!QtyDlv & _
                   "' WHERE JPO = '" & OpenOrdRS!JPO & "'"
             DB.Execute SQL
          End If
       End If
          
       OpenOrdRS.MoveNext
    Loop
    ...that I'm trying to imitate in SQL server so that I can remove this module from my VB project and use SQL Server Agent. And this is what I've come up...
    Code:
    DECLARE @JPO NUMERIC, @OpenVal FLOAT, @OpenSQM FLOAT, @OpenQty NUMERIC
    SELECT @JPO = JPO,
           QtyDlv,
           OutStandVal
    FROM ActiveOrders
    WHERE CASE
          WHEN NOT EXISTS (SELECT a.AUF_NR AS JPO,
                                  SUM(a.RG_OFFEN * b.SUM_NETTO) AS OpenVal,
                                  SUM(a.RG_OFFEN * b.VER_M2) AS OpenSQM,
                                  SUM(a.RG_OFFEN) AS OpenQty
                           FROM liorder..LIORDER.AUF_STAT a,
                                liorder..LIORDER.AUF_POS b
                           WHERE a.AUF_NR = b.AUF_NR AND
                                 a.AUF_POS = b.AUF_POS AND
                                 a.RG_OFFEN != 0 AND
                                 a.AUF_NR = @JPO
                           GROUP BY a.AUF_NR) THEN (DELETE FROM ActiveOrders WHERE JPO = @JPO)
          WHEN EXISTS (SELECT a.AUF_NR AS JPO,
                              @OpenVal =  SUM(a.RG_OFFEN * b.SUM_NETTO) AS OpenVal,
                              @OpenSQM =  SUM(a.RG_OFFEN * b.VER_M2) AS OpenSQM,
                              @OpenQty =  SUM(a.RG_OFFEN) AS OpenQty
                       FROM liorder..LIORDER.AUF_STAT a,
                            liorder..LIORDER.AUF_POS b
                       WHERE a.AUF_NR = b.AUF_NR AND
                              a.AUF_POS = b.AUF_POS AND
                              a.RG_OFFEN != 0 AND
                             a.AUF_NR = @JPO
                       GROUP BY a.AUF_NR) THEN (UPDATE ActiveOrders
                                                SET OutStandVal = @OpenVal,
                                                    QtyDlv = @OpenQty,
                                                    SQM = @OpenQty
                                                WHERE JPO = @JPO)
          END
    ...Now this is just experimental. I've tried running it and it gave me errors. I was thingking of using cursors but I think that's not efficient enough. You might notice the "fn_ActiveOrders". It's a function I created and this is the content...
    Code:
    CREATE FUNCTION fn_ActiveOrders(@JPO numeric)
    RETURNS TABLE
    AS RETURN
    SELECT OrdStat.AUF_NR AS JPO,
           SUM(OrdStat.RG_OFFEN * OrdPos.SUM_NETTO) AS OpenVal, 
    
           SUM(OrdStat.RG_OFFEN * OrdPos.VER_M2) AS OpenSQM,
           SUM(OrdStat.RG_OFFEN) AS QtyDlv
    FROM liorder..LIORDER.AUF_POS OrdPos INNER JOIN
         liorder..LIORDER.AUF_STAT OrdStat ON 
    
            OrdPos.AUF_NR = OrdStat.AUF_NR AND
            OrdPos.AUF_POS = OrdStat.AUF_POS
    WHERE OrdStat.RG_OFFEN <> 0 AND
          OrdStat.AUF_NR = @JPO
    GROUP BY OrdStat.AUF_NR
    I hope that somebody could help me with this...

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by trojanz
    I was thingking of using cursors but I think that's not efficient enough.
    Hi

    That is defo correct - you don't need cursors for this. I rewrote your middle block of code - it looks about right but I haven't tested. Please post DDL and sample data if it fails. Please test in a test environment of course

    Code:
    Delete
    FROM     ActiveOrders
    WHERE   NOT EXISTS  (SELECT NULL
                        FROM    liorder..LIORDER.AUF_STAT a
                                    INNER JOIN liorder..LIORDER.AUF_POS b ON
                                a.AUF_NR = b.AUF_NR
                                AND a.AUF_POS = b.AUF_POS
                        WHERE a.RG_OFFEN <> 0
                                AND a.AUF_NR = ActiveOrders..JPO)
    Update ActiveOrders
    SET     OutStandVal = SumA,
            QtyDlv = SumB,
            SQM = SumC
    FROM    (SELECT SUM(a.RG_OFFEN * b.SUM_NETTO) AS SumA,
                    SUM(a.RG_OFFEN * b.VER_M2) AS SumB,
                    SUM(a.RG_OFFEN) AS SumC,
                    a.AUF_NR AS JPO
            FROM    liorder..LIORDER.AUF_STAT a
                        INNER JOIN liorder..LIORDER.AUF_POS b ON
                    a.AUF_NR = b.AUF_NR
                    AND a.AUF_POS = b.AUF_POS
            WHERE a.RG_OFFEN <> 0
            Group BY
                    a.AUF_NR) AS c
                    INNER JOIN ActiveOrders ON
            ActiveOrders.JPO = c.JPO
    Group BY
            c.JPO
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Posts
    269
    This what I understood from ur VB code.I havent tested with data,so no guaranty from my side .Test it before applying to production server.
    Code:
    CREATE  PROC Update_ActiveOrders_sp
    as
    declare @err int
    set nocount on
    begin tran
    -- creating temp table inserting record---
    SELECT OrdStat.AUF_NR AS JPO,
           SUM(OrdStat.RG_OFFEN * OrdPos.SUM_NETTO) AS OpenVal, 
           SUM(OrdStat.RG_OFFEN * OrdPos.VER_M2) AS OpenSQM,
           SUM(OrdStat.RG_OFFEN) AS QtyDlv
    INTO #TEMP
    FROM    
          liorder..LIORDER.AUF_POS OrdPos INNER JOIN
          liorder..LIORDER.AUF_STAT OrdStat ON 
          OrdPos.AUF_NR = OrdStat.AUF_NR AND
         OrdPos.AUF_POS = OrdStat.AUF_POS INNER JOIN
         ActiveOrders  ON
         ActiveOrders.JPO=OrdStat.AUF_NR 
    WHERE 
          OrdStat.RG_OFFEN <> 0 AND
          ActiveOrders.JPO < '5000000'
    GROUP BY 
          OrdStat.AUF_NR
     
    ---update ActiveOrders which exists in #TEMP table--
    UPDATE ActiveOrders 
          SET OutStandVal = tm.OpenVal,  
          SQM =tm.OpenSQM ,  
          QtyDlv =tm.QtyDlv 
    FROM  
          #TEMP AS tm
    WHERE 
          JPO =tm.JPO 
     
    set @err=@@error
    if(@err <> 0)  goto quitWithError
     
    --- delete records not exists in #TEMP table and ActiveOrders.JPO<'5000000'--
    DELETE ActiveOrders 
    where not exists (
       select 
       null 
       from #TEMP 
       where ActiveOrders.JPO=#TEMP.JPO
       )
    and ActiveOrders.JPO<'5000000'
    set @err=@@error
    if(@err <> 0)  goto quitWithError
    -- drop #TEMP table
    DROP TABLE #TEMP
    goto EndSave
    quitWithError:
     if (@@trancount >0) rollback
     return @err
    EndSave:
     if (@@trancount >0) commit tran
     return 0
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by pootle flump
    Hi

    That is defo correct - you don't need cursors for this. I rewrote your middle block of code - it looks about right but I haven't tested. Please post DDL and sample data if it fails. Please test in a test environment of course

    Code:
    Delete
    FROM     ActiveOrders
    WHERE   NOT EXISTS  (SELECT NULL
                        FROM    liorder..LIORDER.AUF_STAT a
                                    INNER JOIN liorder..LIORDER.AUF_POS b ON
                                a.AUF_NR = b.AUF_NR
                                AND a.AUF_POS = b.AUF_POS
                        WHERE a.RG_OFFEN <> 0
                                AND a.AUF_NR = ActiveOrders..JPO)
    Update ActiveOrders
    SET     OutStandVal = SumA,
            QtyDlv = SumB,
            SQM = SumC
    FROM    (SELECT SUM(a.RG_OFFEN * b.SUM_NETTO) AS SumA,
                    SUM(a.RG_OFFEN * b.VER_M2) AS SumB,
                    SUM(a.RG_OFFEN) AS SumC,
                    a.AUF_NR AS JPO
            FROM    liorder..LIORDER.AUF_STAT a
                        INNER JOIN liorder..LIORDER.AUF_POS b ON
                    a.AUF_NR = b.AUF_NR
                    AND a.AUF_POS = b.AUF_POS
            WHERE a.RG_OFFEN <> 0
            Group BY
                    a.AUF_NR) AS c
                    INNER JOIN ActiveOrders ON
            ActiveOrders.JPO = c.JPO
    Group BY
            c.JPO
    HTH
    Pootle,u missed one condition here , "ActiveOrders.JPO<'5000000'"
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mallier
    Pootle,u missed one condition here , "ActiveOrders.JPO<'5000000'"
    You are right I think you took a bit more time about it - I didn't bother translating the vb solution - I just reworked his T-SQL stab at it. The 5000000 didn't make a showing there (in my defence )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25
    Thanks guys! Both your solutions worked!!! Now I've removed the module from my VB and all updates now work via SQL Server Agent. By the way, since we've talked about cursors, is there a best way to use it? I mean, I've only tried it twice and it takes shitload of time before it finishes execution. I'm sorry though this is off topic, but I just wanted to learn more about T-SQL as it helps me a lot in VB programming. And the stuff that I've learnt so far I got from forums as well.

  7. #7
    Join Date
    Jun 2003
    Posts
    269

    Smile

    Use cursor when there is no other option.Most of time u can solve the problem without cursors.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cursors are useful in about two situations -
    1) looping through system tables to perform commands on, for example, each table in the database. There are variations on this theme.
    2) Camparing values in a column to values from different rows in the same column.

    Quote Originally Posted by trojanz
    it takes shitload of time before it finishes execution
    terribly perceptive - most cursor devotees don't appear to notice this rather important point.
    Last edited by pootle flump; 03-28-06 at 07:26.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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