<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p>Bill,</p>
<p>Just a thought, Excel <i>Power Query</i> 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") </p>
<p>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. <br>
</p>
<p>You can also insert SQL statements <i>directly </i>into Power
Query:
<a class="moz-txt-link-freetext" href="https://support.office.com/en-us/article/Import-Data-from-Database-using-Native-Database-Query-Power-Query-F4F448AC-70D5-445B-A6BA-302DB47A1B00">https://support.office.com/en-us/article/Import-Data-from-Database-using-Native-Database-Query-Power-Query-F4F448AC-70D5-445B-A6BA-302DB47A1B00</a></p>
<p>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 "visual". <br>
</p>
<p>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. <br>
</p>
<p>There also may be a way to see the exact SQL statements that exit
from Power Query directly? (Perhaps someone here knows?)<br>
</p>
<p>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. <br>
</p>
<p>Rick<br>
</p>
<br>
<div class="moz-cite-prefix">On 10/28/2016 11:57 AM, Porter, Bill
wrote:<br>
</div>
<blockquote
cite="mid:0743dd25655d4501a23001ae715f7d10@EXCHG04.spartanburgcounty.local"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<meta name="Generator" content="Microsoft Word 12 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal">I am new to SQL. I’m using FREEANCE to
query and to render maps from ArcServer.
<o:p></o:p></p>
<p class="MsoNormal">I can create basic SQL queries, like one
to bring up a subdivision name if you can spell a portion of
it:
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">[SUBDIVISION].[SUBDIVNAME] like
'%#@^subdivname@#%'<o:p></o:p></p>
<p class="MsoNormal">
Then create ascii listing for it:<o:p></o:p></p>
<p class="MsoNormal">[SUBDIVISION].[SUBDIVNAME] asc<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">But I want to enter an address which will
query our Structures data, then take that point and collect
data from map layers:
<o:p></o:p></p>
<p class="MsoNormal">SchoolDistrict <o:p></o:p></p>
<p class="MsoNormal">City Council<o:p></o:p></p>
<p class="MsoNormal">USCongress<o:p></o:p></p>
<p class="MsoNormal">StateSenate<o:p></o:p></p>
<p class="MsoNormal">StateRep<o:p></o:p></p>
<p class="MsoNormal">Etc. <o:p></o:p></p>
<p class="MsoNormal">And return that data in an ASCII listing. <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Does anyone have an example of a query like
this? I have no idea how to nest requests in SQL.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
SHRUG-L mailing list
<a class="moz-txt-link-abbreviated" href="mailto:SHRUG-L@lists.dep.state.fl.us">SHRUG-L@lists.dep.state.fl.us</a>
<a class="moz-txt-link-freetext" href="http://lists.dep.state.fl.us/mailman/listinfo/shrug-l">http://lists.dep.state.fl.us/mailman/listinfo/shrug-l</a>
</pre>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Richard J. Labs, CFA, CPA
CL&B Capital Management, LLC
Phone: 315-637-0915
E-mail (preferred for efficiency): <a class="moz-txt-link-abbreviated" href="mailto:rick@clbcm.com">rick@clbcm.com</a>
Mailing address: 8 Laureldale Dr., Pittsford, NY 14534-3508</pre>
</body>
</html>