How to enter this SQL into Excel VBA script?-CodePudding

I'm trying to convert this SQL statement to VBA in excel, but i can't seem to get the syntax right.

This is the Query:

    with cte as ( 
    select
        [Job #]
        ,[Date]
        ,[Variance Amt]
        ,[Job QTY] 
        ,[OpenQty] 
        ,[Part #] 
        ,[Material] 
        ,[PCS #] 
        ,[Matrl$$] 
        ,[Date Last Issue] 
        ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders] 
        ,[PO$$] 
        ,[Date Last Rcvd] 
        ,[Wip Total] 
        ,[per pc] 
        ,[Standard Cost] 
        ,[DIFF] 
        ,[% of Profit] 
        ,ROW_NUMBER() OVER(PARTITION BY [Job #] ORDER BY [Job #]) AS rn
        ,count(*) over(partition by [Job #]) as maxrn
        ,sum([Matrl$$]) over(partition by [Job #]) as [Job Matrl$$]
        ,sum([PO$$]) over(partition by [Job #]) as [Job PO$$]
        FROM [CompanyR].[dbo].[WIPVarianceRptView]
)

SELECT  [Job #]
        ,[Date]
        ,[Variance Amt]
        ,[Job QTY] 
        ,[OpenQty] 
        ,[Part #] 
        ,[Material] 
        ,[PCS #] 
        ,[Matrl$$]
        ,[Date Last Issue] 
        ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders] 
        ,case when rn <> maxrn then 0 else [PO$$]           end as [PO$$]
        ,[Date Last Rcvd] 
        ,case when rn <> maxrn then 0 when rn = maxrn then ([Job PO$$]   [Job Matrl$$]) else 0      end as [Wip Total]
        ,case when rn <> maxrn then 0 else [per pc]         end as [per pc] 
        ,case when rn <> maxrn then 0 else [Standard Cost]  end as [Standard Cost]
        ,case when rn <> maxrn then 0 else [DIFF]           end as [DIFF] 
        ,case when rn <> maxrn then 0 else [% of Profit]    end as [% of Profit] 
        ,maxrn as [MAX of ROW NO by JOB]
        FROM cte
        Order By [Job #]

And here is what I'm trying to put into Excel VBA

    sSQL = "with cte as" & _
"(" & _
"       SELECT [Job #]" & _
"       [Date], [Variance Amt], [Job QTY], [OpenQty], [Part #], [Material], [PCS #], [Mtrl$$], [Date Last Issue]" & _
"       ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders]" & _
"       ,[PO$$], [Date Last Rcvd], [Wip Total], [per pc], [Standard Cost], [DIFF], [% of Profit]" & _
"       ,ROW_NUMBER() OVER(PARTITION BY [Job #] ORDER BY [Job #]) AS rn" & _
"       ,count(*) over(partition by [Job #]) as maxrn" & _
"       ,sum([Matrl$$]) over(partition by [Job #]) as [Job Matrl$$]" & _
"       ,sum([PO$$]) over(partition by [Job #]) as [Job PO$$]" & _
"  FROM [CompanyR].[dbo].[WIPVarianceRptView]" & _
")" & _
"       SELECT  [Job #]" & _
"       ,[Date], [Variance Amt], [Job QTY], [OpenQty], [Part #], [Material], [PCS #], [Mtrl$$], [Date Last Issue]" & _
"       ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders]" & _
"       ,case when rn <> maxrn then 0 else [PO$$]           end as [PO$$]" & _
"       ,[Date Last Rcvd]" & _
"       ,case when rn <> maxrn then 0 when rn = maxrn then ([Job PO$$]   [Job Matrl$$]) else 0      end as [Wip Total]" & _
"       ,case when rn <> maxrn then 0 else [per pc]         end as [per pc]" & _
"       ,case when rn <> maxrn then 0 else [Standard Cost]  end as [Standard Cost]" & _
"       ,case when rn <> maxrn then 0 else [DIFF]           end as [DIFF]" & _
"       ,case when rn <> maxrn then 0 else [% of Profit]    end as [% of Profit]" & _
"   FROM cte" & _
"   Order By [Job #]"

You'll notice I also condensed some lines because i got a warning about too many line continuations. Where am I going wrong in this conversion?

CodePudding user response：

Nothing wrong with your SQL - the VBA editor simply doesn't allow to have more than 25 line continues. Simple workaround: Split the assignment into pieces.

sSQL = "with cte as" & _
   "(" & _
   "   SELECT [Job #]" & _
       (...)
   "  FROM [CompanyR].[dbo].[WIPVarianceRptView]" & _
   ")"
 sSQL = sSQL & _
    "SELECT  [Job #]" & _
    "       ,[Date], [Variance Amt], [Job QTY], [OpenQty], [Part #], [Material], [PCS #], [Mtrl$$], [Date Last Issue]" & _
           (...)
    "   FROM cte" & _
    "   Order By [Job #]"

CodePudding user response：

Not a direct answer to the OPs problem, but a way of simplifying the creation of the query. The trick is to use an ArrayList or Scripting.Dictionary to assemble the statement and then to VBA.Join the array of the members using vbNullstring.

The ArrayList version is given below. If a Scripting.Dictionary is used then you need to provide a key for each add. The simplest way to do this is to use .count

.Add .count,"with cte as"

    ' Requires a reference to mscorlib
    Dim myQuery As ArrayList
    Set myQuery = New ArrayList

    ' or use late bound object
    Dim myQuery As Object
    Set myQuery = CreateObject("ArrayList")
    
    With myQuery
        .Add "with cte as"
        .Add "("
        .Add "       SELECT [Job #]"
        .Add "       [Date], [Variance Amt], [Job QTY], [OpenQty], [Part #], [Material], [PCS #], [Mtrl$$], [Date Last Issue]"
        .Add "       ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders]"
        .Add "       ,[PO$$], [Date Last Rcvd], [Wip Total], [per pc], [Standard Cost], [DIFF], [% of Profit]"
        .Add "       ,ROW_NUMBER() OVER(PARTITION BY [Job #] ORDER BY [Job #]) AS rn"
        .Add "       ,count(*) over(partition by [Job #]) as maxrn"
        .Add "       ,sum([Matrl$$]) over(partition by [Job #]) as [Job Matrl$$]"
        .Add "       ,sum([PO$$]) over(partition by [Job #]) as [Job PO$$]"
        .Add "  FROM [CompanyR].[dbo].[WIPVarianceRptView]"
        .Add ")"
        .Add "       SELECT  [Job #]"
        .Add "       ,[Date], [Variance Amt], [Job QTY], [OpenQty], [Part #], [Material], [PCS #], [Mtrl$$], [Date Last Issue]"
        .Add "       ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders]"
        .Add "       ,case when rn <> maxrn then 0 else [PO$$]           end as [PO$$]"
        .Add "       ,[Date Last Rcvd]"
        .Add "       ,case when rn <> maxrn then 0 when rn = maxrn then ([Job PO$$]   [Job Matrl$$]) else 0      end as [Wip Total]"
        .Add "       ,case when rn <> maxrn then 0 else [per pc]         end as [per pc]"
        .Add "       ,case when rn <> maxrn then 0 else [Standard Cost]  end as [Standard Cost]"
        .Add "       ,case when rn <> maxrn then 0 else [DIFF]           end as [DIFF]"
        .Add "       ,case when rn <> maxrn then 0 else [% of Profit]    end as [% of Profit]"
        .Add "   FROM cte"
        .Add "   Order By [Job #]"

    sSQL = vba.Join(myQuery.Toarray, vbNullString)

Some of the spacing from the original may not be required in the above code.