shrug-l: SQL query to drill into mulitple layers and report back in tablular form

Rick Labs rick at
Fri Oct 28 13:12:38 EDT 2016


Just a thought, Excel /Power Query/ has a lot of horsepower for
Extract/Transform/Load (ETL) operations. It goes "step by step" with
each step logged in a history file that can be stepped through.  (Part
of it is essentially a SQL statement writer "on steroids")

Inside Excel Power Query its not SQL. (M$FT uses  a language called "M"
... which is based on the language "F".) However, at the end of the day,
plain old SQL statement exit Excel and heads to the SQL servers to draw
in data.

You can also insert SQL statements /directly /into Power Query:

Working in Excel Power Query first (or having someone in your office
familiar with it do that) may get you to a SQL solution relatively
quickly. Its easy to use "guess" and use "trial and error" in Power
Query. Its quick to iterate till you get what you want and its very

After you have a Power Query that does what you want you can always
"reverse engineer it" without getting into "M" at all. Just check the
SQL database system logs (on the database server), look at the audit
trail of what SQL queries were run on it from the Excel Power Query
user.  Viola - you then have your working SQL statements.

There also may be a way to see the exact SQL statements that exit from
Power Query directly? (Perhaps someone here knows?)

So, you can use Excel Power Query to construct SQL statements, find out
exactly what they are, and then cut Excel totally out of your loop.


On 10/28/2016 11:57 AM, Porter, Bill wrote:
> I am new to SQL.  I’m using FREEANCE to query and to render maps from
> ArcServer. 
> I can create basic SQL queries,  like one to bring up a  subdivision
> name if you can spell a portion of it:
> [SUBDIVISION].[SUBDIVNAME] like '%#@^subdivname@#%'
>                                              Then create ascii listing
> for it:
> But I want to enter an address which will query our Structures data,
> then take that point and collect data from map layers:
> SchoolDistrict    
> City Council
> USCongress
> StateSenate
> StateRep
> Etc.   
> And return that data in an ASCII listing.
> Does anyone have an example of a query like this? I have no idea how
> to nest requests in SQL.
> _______________________________________________
> SHRUG-L mailing list
> SHRUG-L at

Richard J. Labs, CFA, CPA
CL&B Capital Management, LLC
Phone: 315-637-0915
E-mail (preferred for efficiency): rick at
Mailing address: 8 Laureldale Dr., Pittsford, NY 14534-3508

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <>

More information about the SHRUG-L mailing list