Looping the String in SQL Statement

Hi,
I am not sure if this has to be posted under SQL FORUM, but i think its something to do with excel VBA so I am posting here.

I would appreciate verymuch if you could help me on this.

I have VBA code which runs a sql query and gives me an output.

what it does now:-
take the store number entered in A3 and run the SQL query(which is in Red) which gives me the output correctly. ( it pastes the output in sheet2)

I change the store number in A3 and run the macro155 times for different stores.

Code:

---

Public cn As ADODB.Connection
Public CWrs As ADODB.Recordset
Public LWrs As ADODB.Recordset
Public CmdSQLData As ADODB.Command
Dim myStr As String

Sub Where_Else()
    Sheet1.Activate
    Sheet1.Range("A3").Select
    Dim stSQL, retailDate2 As String
    Dim Store, Store2 As String
    Dim i, j As Integer
    Dim readColumn As Long
    Store = Range("A3").value    -- ' Store Number
    Store2 = "(" & Store & ")"
    Set cn = New ADODB.Connection
    Set CWrs = New ADODB.Recordset
    Set CmdSQLData = New ADODB.Command
    cn.Open "DSN=DW MASTER; Driver=Teradata; Server = tdpm; Database=DXWI_PROD_ROI_VIEW_ACCESS; UID=roi_general; PWD=roi; OPTION=3"
    Set CmdSQLData.ActiveConnection = cn
    stSQL = "Select ron, store_name, spend,xrank " & _
            "From ( " & _
            "select ron, store_name, spend, rank(spend) As xrank " & _
            "From ( " & _
            "Select bsk.Retail_Outlet_Number As ron, store_name,Sum(sales_value) As spend " & _
            "From    dxwi_prod_roi_view_access.VWI0BSK_TXN_BASKET_SALE bsk Inner Join dxwi_prod_roi_view_access.vwi0cal_small_calendar cal " & _
            "On  cal.calendar_date = bsk.transaction_date " & _
            "Inner Join dxwi_prod_roi_view_access.vwi0rot_retail_outlet rot " & _
            "On  bsk.retail_outlet_number = rot.retail_outlet_number " & _
            "Where  household_number In " & _
            "(sel household_number " & _
            "From    dxwi_prod_roi_view_access.VWI0BSK_TXN_BASKET_SALE bsk Inner Join dxwi_prod_roi_view_access.vwi0cal_small_calendar cal " & _
            "On  cal.calendar_date = bsk.transaction_date " & _
            "where retail_outlet_number = " & "" & Store2 & "" & _
            "And year_week_number Between 201001 And 201026 Group  By 1) " & _
            "And year_week_number Between 201001 And 201026 " & _
            "And country_code = 7 " & _
            "And bsk.retail_outlet_number <> " & "" & Store2 & "" & _
            "And store_name Not Like '%pfs%' " & _
            "Group  By 1,2) x " & _
            ")A " & _
            "where xrank between 1 and 5 "
    CmdSQLData.CommandText = stSQL
    CmdSQLData.CommandType = adCmdText
    CmdSQLData.CommandTimeout = 0
    Set rs = CmdSQLData.Execute()
    Set rs = CmdSQLData.Execute()
    Sheet2.Activate
    For c = 0 To rs.Fields.Count - 1
        Sheet2.Cells(1, c + 1) = rs.Fields(c).Name
    Next c
    r = 2
    Do While Not rs.EOF
        For c = 0 To rs.Fields.Count - 1
            Sheet2.Cells(r, c + 1) = rs.Fields(c).value
        Next c
        r = r + 1
        rs.MoveNext
    Loop
    ' End If
    cn.Close
End Sub

---

Now what I need:-

Instead of taking one store number from Cell A3, I will enter all the store numbers one after the other from A3.

The code has to loop through all the store numbers in the query and pastes the output one after the other in sheet2.
that is, take the first store number and run the query, paste the output in sheet 2, and again run the query for 2nd store number, paste the output in sheet2 below the previous one.

Thanks for your help in advance
Cross post: http://www.mrexcel.com/forum/showthr...96#post2564496

Untesteed

Code:

---


Public cn As ADODB.Connection
Public CWrs As ADODB.Recordset
Public LWrs As ADODB.Recordset
Public CmdSQLData As ADODB.Command
Dim myStr As String

Sub Where_Else()
    Const stSQL As String = _
    "SELECT ron, store_name, spend,xrank " & _
    "FROM ( " & _
    "SELECT ron, store_name, spend, rank(spend) As xrank " & _
    "FROM ( " & _
    "SELECT bsk.Retail_Outlet_Number As ron, store_name,Sum(sales_value) As spend " & _
    "FROM    dxwi_prod_roi_view_access.VWI0BSK_TXN_BASKET_SALE bsk Inner Join dxwi_prod_roi_view_access.vwi0cal_small_calendar cal " & _
    "ON  cal.calendar_date = bsk.transaction_date " & _
    "INNER JOIN dxwi_prod_roi_view_access.vwi0rot_retail_outlet rot " & _
    "ON  bsk.retail_outlet_number = rot.retail_outlet_number " & _
    "WHERE  household_number In " & _
    "(sel household_number " & _
    "FROM    dxwi_prod_roi_view_access.VWI0BSK_TXN_BASKET_SALE bsk Inner Join dxwi_prod_roi_view_access.vwi0cal_small_calendar cal " & _
    "ON  cal.calendar_date = bsk.transaction_date " & _
    "WHERE retail_outlet_number = " & "<store>" & _
    "AND year_week_number Between 201001 AND 201026 GROUP BY 1) " & _
    "AND year_week_number Between 201001 AND 201026 " & _
    "AND country_code = 7 " & _
    "AND bsk.retail_outlet_number <> " & "" & Store2 & "" & _
    "AND store_name Not Like '%pfs%' " & _
    "GROUP BY 1,2) x " & _
    ")A " & _
    "WHERE xrank BETWEEN 1 AND 5 "
    Dim stSQL, retailDate2 As String
    Dim Store, Store2 As String
    Dim cell As Range
    Dim i, j As Integer
    Dim readColumn As Long
    Sheet1.Activate
    Sheet1.Range("A3").Select
    Set cn = New ADODB.Connection
    Set CWrs = New ADODB.Recordset
    Set CmdSQLData = New ADODB.Command
    cn.Open "DSN=DW MASTER; Driver=Teradata; Server = tdpm; Database=DXWI_PROD_ROI_VIEW_ACCESS; UID=roi_general; PWD=roi; OPTION=3"
    Set CmdSQLData.ActiveConnection = cn
    For Each cell In Range(Range("A3"), Range("A3").End(xlDown))
        Store = cell.Value    ' Store Number
        Store2 = "(" & Store & ")"
        CmdSQLData.CommandText = Replace(stSQL, "<store>", Store)
        CmdSQLData.CommandType = adCmdText
        CmdSQLData.CommandTimeout = 0
        Set rs = CmdSQLData.Execute()
        Set rs = CmdSQLData.Execute()
        Sheet2.Activate
        For c = 0 To rs.Fields.Count - 1
            Sheet2.Cells(1, c + 1) = rs.Fields(c).Name
        Next c
        r = 2
        Do While Not rs.EOF
            For c = 0 To rs.Fields.Count - 1
                Sheet2.Cells(r, c + 1) = _
                rs.Fields(c).Value
            Next c
            r = r + 1
            rs.MoveNext
        Loop
    Next cell
    ' End If
    cn.Close
End Sub

---

Thanks for your quick help,

when i paste the code and run it it prompts

Code:

---

Compile Error:
Constant expression required
with the "Store2" word selected in the below line
"AND bsk.retail_outlet_number <> " & "" & Store2 & "" & _

---

Can you tell me why all the connection, declaration of variables strings etc are done after the sql code?

Code:

---

Public cn As ADODB.Connection
Public CWrs As ADODB.Recordset
Public LWrs As ADODB.Recordset
Public CmdSQLData As ADODB.Command
Dim myStr As String
 
Sub Where_Else()
    Const stSQL As String = _
    "SELECT ron, store_name, spend,xrank " & _
    "FROM ( " & _
    "SELECT ron, store_name, spend, rank(spend) As xrank " & _
    "FROM ( " & _
    "SELECT bsk.Retail_Outlet_Number As ron, store_name,Sum(sales_value) As spend " & _
    "FROM    dxwi_prod_roi_view_access.VWI0BSK_TXN_BASKET_SALE bsk Inner Join dxwi_prod_roi_view_access.vwi0cal_small_calendar cal " & _
    "ON  cal.calendar_date = bsk.transaction_date " & _
    "INNER JOIN dxwi_prod_roi_view_access.vwi0rot_retail_outlet rot " & _
    "ON  bsk.retail_outlet_number = rot.retail_outlet_number " & _
    "WHERE  household_number In " & _
    "(sel household_number " & _
    "FROM    dxwi_prod_roi_view_access.VWI0BSK_TXN_BASKET_SALE bsk Inner Join dxwi_prod_roi_view_access.vwi0cal_small_calendar cal " & _
    "ON  cal.calendar_date = bsk.transaction_date " & _
    "WHERE retail_outlet_number = " & "<store>" & _
    "AND year_week_number Between 201001 AND 201026 GROUP BY 1) " & _
    "AND year_week_number Between 201001 AND 201026 " & _
    "AND country_code = 7 " & _
    "AND bsk.retail_outlet_number <> " & "(<store>)" & _
    "AND store_name Not Like '%pfs%' " & _
    "GROUP BY 1,2) x " & _
    ")A " & _
    "WHERE xrank BETWEEN 1 AND 5 "
    Dim stSQL, retailDate2 As String
    Dim Store, Store2 As String
    Dim cell As Range
    Dim i, j As Integer
    Dim readColumn As Long
    Sheet1.Activate
    Sheet1.Range("A3").Select
    Set cn = New ADODB.Connection
    Set CWrs = New ADODB.Recordset
    Set CmdSQLData = New ADODB.Command
    cn.Open "DSN=DW MASTER; Driver=Teradata; Server = tdpm; Database=DXWI_PROD_ROI_VIEW_ACCESS; UID=roi_general; PWD=roi; OPTION=3"
    Set CmdSQLData.ActiveConnection = cn
    For Each cell In Range(Range("A3"), Range("A3").End(xlDown))
        Store = cell.Value ' Store Number
        Store2 = "(" & Store & ")"
        CmdSQLData.CommandText = Replace(stSQL, "<store>", Store)
        CmdSQLData.CommandType = adCmdText
        CmdSQLData.CommandTimeout = 0
        Set rs = CmdSQLData.Execute()
        Set rs = CmdSQLData.Execute()
        Sheet2.Activate
        For c = 0 To rs.Fields.Count - 1
            Sheet2.Cells(1, c + 1) = rs.Fields(c).Name
        Next c
        r = 2
        Do While Not rs.EOF
            For c = 0 To rs.Fields.Count - 1
                Sheet2.Cells(r, c + 1) = rs.Fields(c).Value
            Next c
            r = r + 1
            rs.MoveNext
        Loop
    Next cell
    ' End If
    cn.Close
End Sub

---

Hi,

The code runs now but it gives the output only for the first store entered in A3, but doesnt get data for the next store in A4 and so on...

Can you step through it and see what the CommandText looks likein each of the first two loop iterations.

there are a couple of easy ways to achieve your desired result, the easiest is probably to make your Store variable contain a comma delimited list of the store numbers you're wanting to match, then change your where clause so that it's checking for retail_outlet_number IN <store> rather than equals. then you can just sort the output based on store number after pasting. That approach would work whether you're looking for 1 store number or a hundred, and you shouldn't see any huge increase in processing time like if you ran the query again for each store number

Thanks Sean,

Using IN conditio in my code doesnt give me the correct result.
if you look at the code,
its taking the household_number from a store1 and the house hold numbers which are there in other stores except store 1
if I give in condition then i will not get the right result, this particular SQL code has to be run only for one store at any point of time.

Thanks for your help.. I am chekcing with xld's code

Xld,

I stepped through the code and I noticed that the output for the first store is pasted in Column A in sheet2, but the output for next store overwrites the data of first store.

output should be pasted one after the other for all stores.

See if this sorts it

Code:

---

Option Explicit

Public cn As ADODB.Connection
Public CWrs As ADODB.Recordset
Public LWrs As ADODB.Recordset
Public CmdSQLData As ADODB.Command
Dim myStr As String
 
Sub Where_Else()
    Const stSQL As String = _
    "SELECT ron, store_name, spend,xrank " & _
    "FROM ( " & _
    "SELECT ron, store_name, spend, rank(spend) As xrank " & _
    "FROM ( " & _
    "SELECT bsk.Retail_Outlet_Number As ron, store_name,Sum(sales_value) As spend " & _
    "FROM    dxwi_prod_roi_view_access.VWI0BSK_TXN_BASKET_SALE bsk Inner Join dxwi_prod_roi_view_access.vwi0cal_small_calendar cal " & _
    "ON  cal.calendar_date = bsk.transaction_date " & _
    "INNER JOIN dxwi_prod_roi_view_access.vwi0rot_retail_outlet rot " & _
    "ON  bsk.retail_outlet_number = rot.retail_outlet_number " & _
    "WHERE  household_number In " & _
    "(sel household_number " & _
    "FROM    dxwi_prod_roi_view_access.VWI0BSK_TXN_BASKET_SALE bsk Inner Join dxwi_prod_roi_view_access.vwi0cal_small_calendar cal " & _
    "ON  cal.calendar_date = bsk.transaction_date " & _
    "WHERE retail_outlet_number = " & "<store>" & _
    "AND year_week_number Between 201001 AND 201026 GROUP BY 1) " & _
    "AND year_week_number Between 201001 AND 201026 " & _
    "AND country_code = 7 " & _
    "AND bsk.retail_outlet_number <> " & "(<store>)" & _
    "AND store_name Not Like '%pfs%' " & _
    "GROUP BY 1,2) x " & _
    ")A " & _
    "WHERE xrank BETWEEN 1 AND 5 "
    Dim stSQL, retailDate2 As String
    Dim Store, Store2 As String
    Dim cell As Range
    Dim i, j As Integer
    Dim readColumn As Long
    Dim NextRow As Long
    Sheet1.Activate
    Sheet1.Range("A3").Select
    Set cn = New ADODB.Connection
    Set CWrs = New ADODB.Recordset
    Set CmdSQLData = New ADODB.Command
    cn.Open "DSN=DW MASTER; Driver=Teradata; Server = tdpm; Database=DXWI_PROD_ROI_VIEW_ACCESS; UID=roi_general; PWD=roi; OPTION=3"
    Set CmdSQLData.ActiveConnection = cn
    For Each cell In Range(Range("A3"), Range("A3").End(xlDown))
        Store = cell.Value ' Store Number
        Store2 = "(" & Store & ")"
        CmdSQLData.CommandText = Replace(stSQL, "<store>", Store)
        CmdSQLData.CommandType = adCmdText
        CmdSQLData.CommandTimeout = 0
        Set rs = CmdSQLData.Execute()
        Set rs = CmdSQLData.Execute()
        With Sheet2
            .Activate
            If .Range("A1").Value = "" Then
                NextRow = 1
            Else
                NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            End If
            For c = 0 To rs.Fields.Count - 1
                .Cells(1, c + 1) = rs.Fields(c).Name
            Next c
            r = NextRow + 1
            Do While Not rs.EOF
                For c = 0 To rs.Fields.Count - 1
                    .Cells(r, c + 1) = rs.Fields(c).Value
                Next c
                r = r + 1
                rs.MoveNext
            Loop
        End With
    Next cell
    ' End If
    cn.Close
End Sub

---

Thanks xld,

your code works for me if I run as it is. but If i want to change the week number, then i need to change that in the code.

I am facing a problem with this line

Code:

---

"AND year_week_number Between 201001 AND 201026 GROUP BY 1) " & _
 "AND year_week_number Between 201001 AND 201026 " & _

---

instead of hardcoding the week number I want to get the values from a Cell B5.
I Delcared as

Code:

---

Dim Startwk, Endwk as Integer
Startwk = Range("B5").value
Endwk = Range("B6").value

---

and changed the line as:

Code:

---

"AND year_week_number Between " & startwk & " And " & Endwk &"GROUP BY 1) " & _
    "AND year_week_number Between 201001 AND 201026 " & _

---

but it throws an error " Constant expression is required" with Startwk and Endwk selected

Can you please help me on this?

Any Suggestions?