Looping the String in SQL Statement

[aravindhan_3]

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 pasts the outpout in sheet2)

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

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

[Bob Phillips]

Untesteed

[vba]

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

[aravindhan_3]

Thanks for your quick help,

when i paste the code and run it it prompts

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?

[Bob Phillips]

[vba]

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

[aravindhan_3]

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

[Bob Phillips]

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

[Sean.DiSanti]

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

[aravindhan_3]

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

[aravindhan_3]

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.

[Bob Phillips]

See if this sorts it

[vba]

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

[aravindhan_3]

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

"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

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

and changed the line as:

"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?

[aravindhan_3]

Any Suggestions?