在SQL中从CASE定义Excel单元格中的参数 [英] define parameters from an Excel Cell for a CASE WHEN in SQL

问题描述

我希望能够在下面的代码中通过Excel中的单元格定义参数SA.FiscalWeek

I want to be able to define my parameter SA.FiscalWeek in the below code from a cell in Excel

SUM(CASE WHEN SA.FISCALYEAR = 2017 
AND SA.FISCALWEEK = 19 THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) AS 'Week'

我将在整体代码中使用多个Case When，并且每个参数都带有单个参数

I will be using multiple Case When in my overall code all with individual parameters

我已经找到了一种方法，可以使用WHERE语句

I have found a way this is possible using a WHERE statement,

但是想知道在CASE WHEN语句中是否可能?

But was wondering if it is possible in a CASE WHEN statement ?

推荐答案

您可以使用vba将查询传递给命令文本， 使用帮助表，将查询逐行列出

You can use vba to pass the query to the command text, Use a helper sheet with your query listed line by line

=查询的第1行"

=第2行，等等.

=第3行和E4行和其余行等".

=" line 3&E4&"rest of line etc. "

E4将是一个单元格引用，以使查询从Excel动态化 然后将这些行连接到一个单元格中，

E4 would be a cell ref to make the query dynamic from Excel then concatenate these lines into one cell,

使用vba类似于以下内容，我的范围"SQLCOUNTRY"是级联查询的单元格

use vba similiar to the below, my range "SQLCOUNTRY" is the cell of concatenated query

 With ActiveWorkbook.Connections("EUUKSQL01 dashboard StockSalesAggregateWeek2") _
        .OLEDBConnection
    .CommandText = Sheets("CountryScript").Range("SQLCOUNTRY").Value
    End With

这样，查询的任何部分都可以从现有的excel单元中删除

This way any part of the query can be driven off an existing excel Cell

这篇关于在SQL中从CASE定义Excel单元格中的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！