sql-server - 用户为访问 SQL 代码选择变量

Question

创建一个查询，分析整个工厂需要生产的成品预测。每月需求被拉入，但试图弄清楚如何轻松选择要查看的月份，因为它们是单独的列。

下面的 SQL 是我目前拥有的，我想从用户输入中对变量进行 DIM 处理，以选择加粗的数字（月份）。前任。用户输入“4”，它变为[MPS 客户预测]！[4]。提前感谢您的帮助。

SELECT [MPS Customer Forecast]![Part] AS [Finished Good]
, [MPSCustomer Forecast].Part
, ***[MPS Customer Forecast]![3]*** AS [MonthDemand]
, BOMs.[Component No]
, BOMs.[Component Name]
, BOMs.[BOMQuantity]
, ([BOMs]![BOM Quantity][MPS Customer Forecast]![3]***) AS [Comp Month Dem]
, [Active Routings - Primary].[Approved WorkcenterRates]
,[MPS Customer Forecast]![3][BOMs]![BOM Quantity]/[Active
Routings - Primary]![Approved Workcenter Rates] AS [Needed Hours]
,[Active Routings - Primary].[Workcenter Code]

标签： sql-serverms-access

Answer 1

这是不可能的。您不能将参数传递给查询来决定要返回的列。

如果您无法访问用于计算 12 个月值的基础记录，我看到有两个机会可以实现您想要的：

1.使用UNION查询

不是查询表[MPS Customer Forecast]，而是查询定义为 UNION 查询 ( [MPS Customer Forecast UNION]) 的查询，如下所示（如果需要，添加更多字段）：

SELECT 1 AS [Month], [1] AS [MonthDemand], [Part] FROM [MPS Customer Forecast]
UNION ALL
SELECT 2 AS [Month], [2] AS [MonthDemand], [Part] FROM [MPS Customer Forecast]
UNION ALL
...
UNION ALL
SELECT 12 AS [Month], [12] AS [MonthDemand], [Part] FROM [MPS Customer Forecast]

主查询可能如下所示（您忘记了MonthDemandand之间的算术运算符BOMQuantity，我也是）：

PARAMETERS MonthParameter Short;
SELECT [MPS Customer Forecast UNION]![Part] AS [Finished Good]
, [MPS Customer Forecast UNION].Part
, [MPS Customer Forecast UNION].[MonthDemand]
, BOMs.[Component No]
, BOMs.[Component Name]
, BOMs.[BOMQuantity]
, ([BOMs]![BOM Quantity][MPS Customer Forecast UNION]![MonthDemand]) AS [Comp Month Dem]
, [Active Routings - Primary].[Approved WorkcenterRates]
,[MPS Customer Forecast UNION].[MonthDemand][BOMs]![BOM Quantity]/[Active
Routings - Primary]![Approved Workcenter Rates] AS [Needed Hours]
,[Active Routings - Primary].[Workcenter Code]
...
WHERE ((([MPS Customer Forecast UNION].[Month]) = [MonthParameter]))

2. 使用Choose函数选择 12 个值之一：

不要访问与月份相关的列，而是根据查询的参数选择值：

PARAMETERS MonthParameter Short;
...
Choose([MonthParameter], [MPS Customer Forecast]![1], [MPS Customer Forecast]![2], [MPS Customer Forecast]![3], [MPS Customer Forecast]![4], [MPS Customer Forecast]![5], [MPS Customer Forecast]![6], [MPS Customer Forecast]![7], [MPS Customer Forecast]![8], [MPS Customer Forecast]![9], [MPS Customer Forecast]![10], [MPS Customer Forecast]![11], [MPS Customer Forecast]![12])
...