直接从Excel选择AS400查询记录 [英] Choose AS400 query records directly from Excel
问题描述
我已经在互联网上搜索了数小时，试图弄清是否甚至可能发生以下情况：
要直接从Excel中选择AS400查询记录。
我还没有找到任何解决方案或说明如何实现此目标，这使我猜测这根本不可能。但是，我还没有看到任何人确认这是不可能的。
所以我的问题是：这可能吗？如果是这样，您能为我指明正确的方向以便我开始学习如何做吗？
我知道可以从Excel运行查询，然后通过SQL语句添加参数，但就我而言，这提出了一些可以通过在执行查询之前选择记录来避免的问题。
示例：
我有一个查询，该查询的一列（可以称为 ColVal ）可以容纳值1和/或2。在AS400程序的处理查询菜单下，然后在选择记录下，我可以根据 ColVal 中的值指定查询运行时应包含哪些记录。 。这意味着我在运行查询时可以得到三种不同的情况（A，B和C）：
A）查询仅包含记录，其中 ColVal 为1
B）查询仅包含 ColVal 中值为2
的记录
C）查询包含记录，其中 ColVal 的值是1或2
目标可以从Excel中选择我想要的情况，以避开并使用AS400程序。
但是，使用情况C，然后在Excel中使用不能使用SQL语句来模拟情况A或B，因为这意味着查询仍然包含不需要的记录。
整个过程归结为以下几点：是否甚至有可能从Excel运行查询以本质上更改其包含的数据，而不仅仅是将其输出到excel？如果可能，那么是否可以将参数传递给AS400系统并使用它来创建情况A，B或C？
我希望这个例子有意义。
编辑-新示例
说我有不同的客户A和B我可以打开AS400程序并运行一个查询，在该查询中我已指定只希望获取客户A的数据。然后，我可以打开Excel并在查询中使用过滤器（如Hambone所述），以确定要输出的记录。但是，如果要使用客户B的数据，则必须再次打开AS400并使用不同的参数运行查询。我希望能够从Excel将客户A的数据集更改为B，而不必同时将两者都包含在我的记录集中，然后过滤掉其中的一个。
我认为如果可以将参数传递给AS400，这是可行的。然后，AS400使用此参数作为在记录中应存储记录的条件来运行查询。这意味着，如果参数为客户B，则无法从客户A访问数据，而无需再次通过AS400运行查询。
任何想法都会受到赞赏：）
按照我的评论，这是如何使用MS Excel直接在MS Excel中运行ODBC查询的快速入门Microsoft查询。这与您引用的Power Query有很大不同，因为MS Query是Excel的标准功能-它不是插件。这很重要，因为这意味着每个人都拥有它。如果您要将解决方案部署到其他人，则这是一个重要的考虑因素。
要在Excel中启动MS查询，请转到数据标签，选择来自其他来源- > Microsoft Query。
它可能会或可能不会要求您登录（取决于您使用的是哪个ODBC连接以及其配置方式）。
下一部分很重要。 MS Query将尝试让您使用其生成器来创建查询。如果您有SQL，请跳过此部分。这太糟糕了。单击查询向导上的取消，然后单击 SQL按钮以输入您自己的SQL。如果可以，请确保结果集较小（例如在查询中使用其中1 = 2 ）。
当MS Query返回结果时，请单击 SQL按钮旁边的按钮，以将结果返回到电子表格。看起来就像是一扇小门。
从这里开始，任何时候只要要刷新查询，都可以在Excel中的数据表上单击鼠标右键，然后选择刷新。 或者，您可以转到功能区上的数据选项卡，然后选择刷新。
如果已链接数据透视表和图表，则可以使用全部刷新选项
要随时编辑查询，请右键单击Excel中的表，然后转到表外部数据属性。 ：
然后单击连接属性图标（以下突出显示）
点击第二个选项卡（定义）并直接编辑SQL。
可以简单地通过插入裸露的？来声明参数。
换句话说，如果您的查询看起来像这样：
select *
来自用户
，其中user_id ='hambone'
只需将其更改为：
select *
来自用户
其中user_id =吗？
Excel将在运行查询之前提示您输入用户ID。从这里，您还可以选择将参数值放在电子表格内的单元格中，并让查询从那里读取它。右键单击表格并转到参数菜单选项，您将看到这些内容。
让我知道是否有帮助或不清楚。
-编辑7/23/2018-
要跟进您的最新编辑，可以处理您所描述的情况，在该情况下您希望能够根据值进行过滤，或者如果未给出任何值，然后没有过滤器。在向用户提供多个过滤器选项并且您希望空格代表无过滤器时，您会看到很多东西，这显然与SQL的工作方式相反。
但是，您可以修改SQL使其仍然起作用：
select *从活动
中，其中
（活动=？或？为空）和
（能量=？或？为空）
在此示例中，您必须声明四个参数，而不是两个，每个参数两个。
您可能还必须使用数据类型，具体取决于RDBMS（例如对于数字，您可能不得不说？= 0 而不是？是空的甚至是？ =''表示文本。）
这里是一个工作示例，在上面的查询中应用了一个过滤器，您可以清楚地看到第二个没有影响。
I've been searching the internet for hours trying to figure out if the following is even possible:
To choose the AS400 query records directly from Excel.
I haven't found any solution or description of how this could be achieved, which makes me guess that it's simply not possible. However, I haven't seen anyone confirm that it is impossible.
So my question is: Is this possible? And if it is, could you point me in the right direction in order for me to start learning how to do it?
I know its possible to run a query from Excel, and then adding parameters via SQL statements, but in my case, this presents several problems that could be avoided by choosing the records before the query is executed.
Example:
I have a query with a column (lets call it ColVal) that can hold the values 1 and/or 2. In the AS400 program under the menu "Work with queries" and then "Choose records" I can specify which records the query should contain when it has run based on the value in ColVal. This means i can get three different situations (A, B and C) when i run the query:
A) The query only contains records where the value in ColVal is 1
B) The query only contains records where the value in ColVal is 2
C) The query contains records where the value in ColVal is either 1 or 2
The goal is to be able to choose which situation I want from Excel in order to circumvent opening and using the AS400 program.
However, using situation C and then editing the query in Excel with an SQL statement to mimic situation A or B is not an option, as this means the query still contains undesired records.
This whole thing boils down to the following: Is it even possible to run the query from Excel essentially changing the data it contains and not just outputting it to excel? If this is possible, is it then possible to pass a parameter to the AS400 system and use it to create situation A, B or C?
I hope this example makes sense.
Edit - New example
Say i have different customers A and B. I can open the AS400 program and run a query in which i have specified that I only want data on customer A. I can then open Excel and use filters (as Hambone described) on the query to determine which records I want to output. However, if I want to work with data from customer B, I have to open the AS400 again and run the query with different parameters. I would like to be able to "change" my dataset from customer A to B from Excel, without having to include both in my recordset and then filter out one of them.
I imagined this is doable if you could pass a parameter to the AS400. The AS400 then runs the query using this parameter as the criteria for which records should be stored in the query. This means that if the parameter is Customer B, then there is no way to acces data from customer A, without running the query through AS400 again.
Any ideas are greatly appreciated :)
Follow up to my comment, here is a quick primer on how to run an ODBC query directly in MS Excel using Microsoft Query. This is very different than Power Query, which you referenced, in that MS Query is standard with Excel -- it's not a plug-in. This is relevant because it means everyone has it. If you are deploying a solution to others, that's an important consideration.
To start an MS Query in Excel, go to the data tab, select "From Other Sources" -> "Microsoft Query."
A list of your ODBC connections will come up. Pick the one that you want and select "OK."
It may or may not ask you for a login (depending on which ODBC connection you use and how its configured).
The next part is important. MS Query is going to try to have you use its builder to create the query. If you have the SQL, skip this part. It's horrible. Click "Cancel" on the query wizard, and then click the "SQL" button to enter your own SQL. If you can, make sure the result set is small (like use where 1 = 2 in the query).
When MS Query returns results, click the button next to the SQL Button to have it return the results to the spreadsheet. It looks like a little door.
From here, any time you want to refresh the query, you can simply right-click the data table in Excel and select "refresh." Alternatively you can go to the data tab on the ribbon and select "Refresh."
By the way if you have linked pivot tables and charts, the "Refresh All" option will refresh those as well, in the correct order.
To edit your query at any time, right-click on the table in Excel, go to Table-External Data Properties:
Then Click on the Connection Properties icon (highlighted below)
Click on the second tab (Definition) and edit the SQL Directly.
Parameters can be declared simply by inserting a bare "?" in place of your literal.
In other words, if your query looks like this:
select *
from users
where user_id = 'hambone'
Just change it to:
select *
from users
where user_id = ?
Excel will prompt you for a user id before it runs the query. From here, you also have the option of putting the parameter value in a cell within the spreadsheet and having the query read it from there. You'll see these when you right-click the table and go to the "Parameters" menu option.
Let me know if this helps or is unclear.
-- EDIT 7/23/2018 --
To follow up on your latest edit, it is possible to handle the scenario you describe, where you want to be able to filter on a value, or if none is given, then not have a filter. You see this a lot when you present multiple filter options to the user and you want a blank to mean "no filter," which is obviously counter to the way SQL works.
However, you can hack SQL to still make it work:
select * from activities
where
(activity = ? or ? is null) and
(energy = ? or ? is null)
In this example you have to declare four parameters instead of two, two for each.
You might also have to play with datatypes, depending on the RDBMS (for example for numerics you might have to say ? = 0 instead of ? is null or even ? = '' for text).
Here is a working example where a single filter was applied on the query above and you can clearly see the second one did not have an impact.
这篇关于直接从Excel选择AS400查询记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
