vba to sql

[menssanvi]

Hi all, is there anyone out there that can help me replicate the below VBA code into SQL? Obviously I have no problem with the select statement but it the looping that I'm trying to recreate in SQL in vain.
Thanks in advance
--------------
sqlstring2 = "SELECT M_Rel_Ttemp.wh_acc_no, M_Rel_Ttemp.wh_cust_no, M_Rel_Ttemp.cluster_no FROM Mortgage_Rel_Temp ORDER BY M_Rel_Ttemp.wh_acc_no ASC, M_Rel_Ttemp.cluster_no ASC"
Set rst2 = thedb.OpenRecordset(sqlstring2, dbOpenDynaset)

rst2.MoveFirst
prev_fac = rst2!wh_acc_no
prev_cust = rst2!wh_cust_no
prev_clus = rst2!cluster_no
rst2.MoveNext

For i = 2 To rwnbr
If rst2!cluster_no <> prev_clus Then
If rst2!wh_acc_no = prev_fac Then
rst2.Edit
rst2!cluster_no = prev_clus
ChangeCount = ChangeCount + 1
rst2.Update
Else
End If
Else
End If
prev_fac = rst2!wh_acc_no
prev_cust = rst2!wh_cust_no
prev_clus = rst2!cluster_no

rst2.MoveNext
Next i

rst2.Close

-----------------------

[mohanvijay]

you can done it by UPDATE query

do you have any unique number field such as "Row Number" in M_Rel_Ttemp table?

[menssanvi]

No, I dont have any other column in M_Rel_Ttemp. The original column cluster_no in Mortgage_Rel_Temp reflect row number before going to the transformation per posting.

[mohanvijay]

does Mortgage_Rel_Temp reflect table have the "wh_acc_no" field?

[mohanvijay]

try this


UPDATE t0 SET t0.cluster_no=(SELECT TOP(1) cluster_no FROM M_Rel_Ttemp WHERE wh_acc_no=t0.wh_acc_no ORDER BY wh_acc_no,cluster_no) FROM M_Rel_Ttemp t0

[menssanvi]

yes indeed, Mortgage_Rel_Temp has wh_acc_no field

[mohanvijay]

i assume that Mortgage_Rel_Temp has the row number filed

this query will faster than subquery

UPDATE t0 SET t0.cluster_no=t1.cluster_no FROM M_Rel_Ttemp t0 INNER JOIN
(SELECT t2.wh_acc_no,t2.cluster_no FROM Mortgage_Rel_Temp t2 INNER JOIN
(SELECT wh_acc_no,MIN(row number field) AS rw_id FROM Mortgage_Rel_Temp GROUP BY wh_acc_no)
t3 ON t2.row number filed=t3.rw_id) t1 ON t0.wh_acc_no=t1.wh_acc_no

[menssanvi]

thanks, I'll try that. Cheers!

[menssanvi]

Thanks for that, it hasn't quite given me the full result as in my vba code.

[menssanvi]

Maybe it will be better if I post the full vba code as below:


-----------------------------------
thedb.Execute ("INSERT INTO Mortgage_Rel_Temp ( wh_acc_no, wh_cust_no ) SELECT Mortgage_Rel.wh_acc_no, Mortgage_Rel.wh_cust_no FROM Mortgage_Rel")

sqlstring1 = "SELECT Mortgage_Rel_Temp.wh_acc_no, Mortgage_Rel_Temp.wh_cust_no, Mortgage_Rel_Temp.cluster_no FROM Mortgage_Rel_Temp ORDER BY Mortgage_Rel_Temp.wh_acc_no"
Set rst1 = thedb.OpenRecordset(sqlstring1, dbOpenDynaset)
rst1.MoveLast
rwnbr = rst1.RecordCount
rst1.MoveFirst
ChangeCount = 1
For i = 1 To rwnbr
rst1.Edit
rst1!cluster_no = i
rst1.Update
rst1.MoveNext
Next i
Do While ChangeCount <> 0
ChangeCount = 0


sqlstring2 = "SELECT Mortgage_Rel_Temp.wh_acc_no, Mortgage_Rel_Temp.wh_cust_no, Mortgage_Rel_Temp.cluster_no FROM Mortgage_Rel_Temp ORDER BY Mortgage_Rel_Temp.wh_acc_no ASC, Mortgage_Rel_Temp.cluster_no ASC"
Set rst2 = thedb.OpenRecordset(sqlstring2, dbOpenDynaset)

rst2.MoveFirst
prev_fac = rst2!wh_acc_no
prev_cust = rst2!wh_cust_no
prev_clus = rst2!cluster_no
rst2.MoveNext

For i = 2 To rwnbr
If rst2!cluster_no <> prev_clus Then
If rst2!wh_acc_no = prev_fac Then
rst2.Edit
rst2!cluster_no = prev_clus
ChangeCount = ChangeCount + 1
rst2.Update
Else
End If
Else
End If
prev_fac = rst2!wh_acc_no
prev_cust = rst2!wh_cust_no
prev_clus = rst2!cluster_no

rst2.MoveNext
Next i

rst2.Close

sqlstring3 = "SELECT Mortgage_Rel_Temp.wh_acc_no, Mortgage_Rel_Temp.wh_cust_no, Mortgage_Rel_Temp.cluster_no FROM Mortgage_Rel_Temp ORDER BY Mortgage_Rel_Temp.wh_cust_no ASC, Mortgage_Rel_Temp.cluster_no ASC"
Set rst3 = thedb.OpenRecordset(sqlstring3, dbOpenDynaset)

rst3.MoveFirst
prev_fac = rst3!wh_acc_no
prev_cust = rst3!wh_cust_no
prev_clus = rst3!cluster_no
rst3.MoveNext

For i = 2 To rwnbr
If rst3!cluster_no <> prev_clus Then
If rst3!wh_cust_no = 50798 Then
Debug.Print rst3!wh_cust_no
End If

If rst3!wh_cust_no = prev_cust Then
rst3.Edit
rst3!cluster_no = prev_clus
ChangeCount = ChangeCount + 1
rst3.Update
Else
End If
Else
End If



prev_fac = rst3!wh_acc_no
prev_cust = rst3!wh_cust_no
prev_clus = rst3!cluster_no

rst3.MoveNext
Next i


Loop
---------------------------------

[mohanvijay]

can you please explain your full scenario?

Please attach sample data in xlsx format

which verison of SQL do you use?

[menssanvi]

I've attached a sample result.
"result from my current sql" is basically what I'm getting when I try to write it in sql
"result from VBA" is what it should be.

the yellow section is where my data is not matching for this particular case when cluster no is 14.

[menssanvi]

I'm just trying to create a loop or cursor in sql if you like.

[mohanvijay]

can you post sample data of source table?

[menssanvi]

I have done the row numbering in section 1 of the vba code (SQL 1). Getting stock with tne looping to assign clusters_no. I've tried writing an update but I seem to be missing some account that were picked by the vba (SQL 2

--------SQL1-------
INSERT INTO MORTG_REL_TEMP
(WH_ACC_NO
,WH_CUST_NO
,CLUSTER_NO
)
SELECT
b.wh_acc_no
,b.wh_cust_no
,MIN(a.cluster_n) AS CLUSTER_NO
FROM
(
SELECT wh_acc_no, wh_cust_no
,ROW_NUMBER() OVER(ORDER BY wh_ACC_no, cluster_no ASC) AS cluster_N
FROM MORTGAGE_REL
GROUP BY wh_acc_no, wh_cust_no
)a
,MORTGAGE_REL b

WHERE b.wh_acc_no = a.wh_acc_no

GROUP BY
b.wh_acc_no
,b.wh_cust_no


----------SQL 2------------
UPDATE MORTG_REL_TEMP
FROM
(
SELECT
Wh_cust_No ,
MIN(cluster_no)
FROM
MORTG_REL_TEMP
GROUP BY 1
) AS TEST (D1,D2)
SET cluster_no = d2
WHERE
Wh_cust_No = D1
AND CLUSTER_NO <> D2

[Pogla]

You don't really want to loop through each row in SQL if it can be avoided.

Row by agonising row (RBAR) is not what SQL is intended for.

Trying to pick apart you code, it looks like you want to insert records based on the cluster_no (which I'm assuming doesn't go up incrementally)

I would suggest something like

UPDATE t0

Set t0.cluster_no=M_Rel_Ttemp.Num
FROM (SELECT MAX(cluster_no) AS Num,
wh_acc_no
FROM M_Rel_Ttemp
GROUP BY wh_acc_no) M_Rel_Ttemp
INNER JOIN t0
ON M_Rel_Ttemp.wh_acc_no = tO.wh_acc_no
WHERE M_Rel_Ttemp.Num < t0.cluster_no