How to do this the T-SQL way.

I have this code in VB...

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...

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...

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...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 :)


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|||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.

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|||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 :)


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'"|||Pootle,u missed one condition here , "ActiveOrders.JPO<'5000000'"You are right :o 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 :D )|||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.|||Use cursor when there is no other option.Most of time u can solve the problem without cursors.|||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.

it takes shitload of time before it finishes execution
terribly perceptive - most cursor devotees don't appear to notice this rather important point.