Consulting

Results 1 to 16 of 16

Thread: vba to sql

  1. #1

    vba to sql

    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

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

  2. #2
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location
    you can done it by UPDATE query

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

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

  4. #4
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location
    does Mortgage_Rel_Temp reflect table have the "wh_acc_no" field?

  5. #5
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location
    try this

    [vba]
    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

    [/vba]

  6. #6
    yes indeed, Mortgage_Rel_Temp has wh_acc_no field

  7. #7
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location
    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

  8. #8
    thanks, I'll try that. Cheers!

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

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

  11. #11
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location
    can you please explain your full scenario?

    Please attach sample data in xlsx format

    which verison of SQL do you use?

  12. #12
    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.
    Attached Files Attached Files

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

  14. #14
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location
    can you post sample data of source table?

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

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

    [VBA]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[/VBA]
    To Err is Human
    To Moo, Bovine

Posting Permissions

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