带JOIN的SQL/Excel查询参数 [英] SQL / Excel Query Parameter with a JOIN

问题描述

我正在学习如何在Excel驱动的SQL查询中使用参数(实际上，我仍在学习SQL).感谢

I am learning how to use parameters in an excel-driven SQL query (in fact I am still learning SQL in general). Thanks to the nice people that helped me build my query to modify the results as I need, I want to take this a step further and supply a parameter in Excel to filter the results.

这是我的查询:

SELECT 
    fun.FUNCTION_ID
    ,COALESCE(fun.parent_function, fun2.function_id) as PARENT_FUNCTION
    ,fun.MODULE_ID
    ,fun.DESCRIPTION
    ,fun.FUNCTION_PURPOSE
    ,fun.PB_OBJECT
    ,sec.GROUP_ID
    ,sec.ACCESS_LEVEL
from 
    MODULE_FUNCTION fun

    LEFT JOIN MODULE_FUNCTION fun2
    ON fun.function_id = fun2.function_id
    AND fun2.function_id IN (SELECT parent_function FROM MODULE_FUNCTION)

    LEFT OUTER JOIN FUNCTION_SECURITY sec
    ON fun.FUNCTION_ID = sec.FUNCTION_ID
    AND sec.GROUP_ID = 'GROUP_NAME'

我需要做的是允许团队中的人们在excel工作表中运行此查询，并在第二个JOIN中为其"GROUP_NAME"提供其组名.不幸的是，我无法使用语法WHERE (sec.GROUP_ID = ?)(在此处找到)，因为我需要从MODULE_FUNCTION表中提取所有结果，并且仅在所提供的组中存在匹配项时才在FUNCTION_SECURITY表的右侧插入结果(没有匹配项时为null).

What I need to do is allow people from a team to run this query in the excel sheet and supply their group name for the "GROUP_NAME" in the second JOIN. Unfortunately I cannot use the syntax WHERE (sec.GROUP_ID = ?) (found here) as I need to pull all results from the MODULE_FUNCTION table and only insert results on the right from the FUNCTION_SECURITY table when there is a match on the supplied group (leaving null when there is no match).

当我尝试最后使用AND (sec.GROUP_ID = ?)时，我在Excel中得到一个无效的参数编号" .从我的聚集中，?"只能与WHERE一起使用(并且可以在测试查询中为我找到).

When I try to use AND (sec.GROUP_ID = ?) at the end I get a "Invalid Parameter Number" in Excel. From what I have gathered, the "?" can only be use with WHERE (and works find for me in test queries).

我尝试了很多事情，包括声明@parameter，但无济于事.

I have tried many things, including declaring a @parameter, but no avail.

我很想尝试这项技术，但是如果可能的话，我想避免使用VB.

I'm tempted to try this technique but I'd like to avoid VB if possible.

推荐答案

我知道您说过要避免使用VB，但是对于您想做的事情并不太复杂.

I know you said you want to avoid VB, but it isn't too complicated for what you want to do.

您可以让工作表的组名称具有一个单元格，然后是一个调用宏的按钮，您可以在其中更改sql查询以针对group_id进行调整.

You can have the sheet have a cell for the group name, then a button that calls a macro where you would change the sql query to adjust for the group_id.

类似的东西:

Dim sql As String

sql = "select ... from ... and sec.GROUP_ID = '?'"
sql = Replace(sql, "?", Worksheets("Analysis").Range("A1").Value)

With ActiveWorkbook.Connections("connection name").OLEDBConnection
    .CommandText = sql
    .Refresh
End With

位置:

Worksheets("Analysis").Range("A1").Value

是Group_ID.您可以将其设置为工作簿中任何工作表中的特定单元格.我会在其旁边创建一个名为刷新表"或类似名称的按钮.

is the Group_ID. You can set this to a specific cell in any sheet in your workbook. I would create a button right next to it called "Refresh table" or something like that.

如果您已经制作了一个链接到数据库的表，则Excel中有一个连接对象.转到数据"选项卡，然后单击连接".将会弹出一个新窗口.查找与SQL查询匹配的连接.单击该连接，然后单击属性"，然后将连接名称更改为简单的名称(根据您连接到的服务器/表，它通常是一个长名称).将其用于

If you already made a table that links to a database, then there is a connection object in Excel. Go to the Data tab, then click "Connections". A new window will pop up. Find the connection that matches to the SQL query. Click on that connection and click "Properties" then change the connection name to something easy (it's usually some long name based on the server/table you connect to). Use that for the

ActiveWorkbook.Connections("connection name")

部分.

链接到工作表上的创建"按钮并链接到宏:

Link to create button on worksheet and link to macro:

查看全文