Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql server - define parameters from an Excel Cell for a CASE WHEN in SQL

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'

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

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

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

=" line 1 of query"

=" line 2 etc. "

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

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

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

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...