这是我的客户参数表
let
Source = Excel.CurrentWorkbook(){[Name="Customer"]}[Content],
Customer1 = Source{0}[Customer]
in
Customer1
和我的查询
let
Customer = Excel.CurrentWorkbook(){[Name="Customer"]}[Content],
Customer_Name = Customer{0}[Customer],
Source = Sql.Database("SERVER", "DATABASE", [Query="SELECT i.PriorityType as 'PRIORITY','COUNT' = COUNT(i.IncidentID)#(lf)FROM ININ_ISupport_S_Incident_Search2 (NULL) i#(lf)WHERE IncidentType IN ('Customer Support','Managed Services')#(lf)AND Organization = Customer_Name#(lf)AND IsResolved = 0#(lf)AND Active = 1#(lf)AND StateType = 'Open'#(lf)GROUP BY i.PriorityType#(lf)ORDER BY CASE#(lf) WHEN i.PriorityType = 'Code Red' THEN 1#(lf) WHEN i.PriorityType = 'Code Red RCA' THEN 2#(lf) WHEN i.PriorityType = 'High' THEN 3#(lf) WHEN i.PriorityType = 'Medium' THEN 4#(lf) WHEN i.PriorityType = 'Low' THEN 5#(lf)END ASC"])
in
Source
我正在设置Organization=Customer_Name,但我一直得到以下
Message=Invalid column name 'Customer_Name'
有没有办法实现这个
Customer_Name不是SQL表中的列,因此它会失败并显示该消息。如果要在查询中使用Customer_Name,则需要将其添加到字符串中。这是使用&操作人员在你的情况下,你会做一些类似的事情
"...AND Organization = '" & Customer_Name & "'#(lf)AND IsResolved...
如果客户名称中有报价(如O'Neill),这将导致问题,因此您可以进行
"...AND Organization = '" & Text.Replace(Customer_Name, "'", "''") & "'#(lf)AND IsResolved...
尝试转义引号。
转义引号并不总是足够的,因此,如果您在外部人员可以创建客户的数据库上运行此查询,则应避免使用上述方法。如果您按Customer_Name(通过Table.SelectRows(tableName, each [Organization] = Customer_Name)
)筛选列,您应该会得到相同的结果,而不会有任何安全问题。