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.1k views
in Technique[技术] by (71.8m points)

excel - Using Parameters in SQL query with sub-query

I have a fairly complicated SQL query with a nested subquery. When I try to use parameters in Microsoft Query is say I can use parameters in queries that cant be represented graphically. So I need another option. I think you can place your SQL query in a cell as a string then have a Macro run it. Any ideas how I could do this?

Thanks

-Jesse

See Question&Answers more detail:os

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

1 Answer

0 votes
by (71.8m points)

Here's what I do to work around the limitations of Microsoft Query in Excel 2007:

  1. A produce a dummy query (SELECT NULL AS Test, for example) in Microsoft Query and insert it into the worksheet.
  2. Right-click on the table that MS Query just inserted and click Table->Edit External Data Properties.
  3. Click on the Connection Properties button, then click the Definition tab.
  4. In the Command Text section, write out or paste in the query that you want, using the usual '?' convention for parameters, then click OK.
  5. Click OK to exit the External Data Properties window.
  6. Right click on the table again, and select Table->Parameters to bind the parameters in the usual way.

The idea is the bypass the GUI that MS Query provides, which has some arbitrary limitations that the underlying engine does not.

This works for many complex queries, but not all. When I encounter a query that MS Query refuses to digest at all, I either refactor the query (when feasible) or create a VIEW on the SQL server and query against that.


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

2.1m questions

2.1m answers

60 comments

57.0k users

...