0

Trying to set up this sql as a data connection in excel 2007:

select sv.accountid, sv.companyName, sv.siteid, sv.siteName + '('+sv.siteCity + '-' + sv.siteState + ')' as siteNameWithLocation,
datefromparts(datepart(yyyy,r.logdate),datepart(mm,r.logdate),1) as monthDate, 
sum('controllerEstKGal') / 1000 as siteEstimatedTotalKgals
from RuntimeDay r WITH (READUNCOMMITTED) 
join controller c on c.id = r.controllerid
join siteView sv on sv.siteid = c.siteid
join (
SELECT r.controllerid, r.logdate, SUM((ISNULL(r.RuntimeSec, 0)/60.0) * ISNULL(s.EsrfGpm, 0)) as 'controllerEstKGal'
  FROM RuntimeDay r WITH (READUNCOMMITTED)
  JOIN CdcView c    WITH (READUNCOMMITTED) ON r.ControllerId = c.ControllerId
  JOIN StationFlowConfig s WITH (READUNCOMMITTED) 
       ON r.ControllerId = s.ControllerId AND r.StationNumber = s.StationId
 WHERE c.siteid in (8547, 8299, 8556, 8541, 8292, 8600, 8551, 5487, 8555, 8216, 8342, 8557, 8287, 8542, 8221, 5509, 8218, 8543, 8336, 8343)
   AND logDate between '2016-01-01' and '2016-12-31'
   AND r.RuntimeSec > 0
   AND s.EsrfGpm > 0
    group by r.controllerid, r.logdate
) eu on eu.controllerid = r.controllerid and eu.logDate = r.logdate
where sv.siteid in (8547, 8299, 8556, 8541, 8292, 8600, 8551, 5487, 8555, 8216, 8342, 8557, 8287, 8542, 8221, 5509, 8218, 8543, 8336, 8343) and r.logDate between '2016-01-01' and '2016-12-31'
group by sv.accountid, sv.companyName, sv.siteid, sv.siteName + '('+sv.siteCity + '-' + sv.siteState + ')',
datefromparts(datepart(yyyy,r.logdate),datepart(mm,r.logdate),1)

And I get the following error message from Microsoft Query:

No Column name was specified for column 3 of 'eu'.

Statement(s) could not be prepared.

I believe that "column 3" is referring to this specific part:

SUM((ISNULL(r.RuntimeSec, 0)/60.0) * ISNULL(s.EsrfGpm, 0)) as 'controllerEstKGal'

I've found cases where this question was asked before, but the replies say to put the name of column 3 in quotes. However (as you can see) I've done that and I'm still getting this error.

Help me StackExchange, you're my only hope! (and thank you!)

EDIT: To clarify, I get the error both with and without single/double quotes surrounding controllerEstKgal. Backticks result in the following error message:

Incorrect syntax near '`'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Statement(s) could not be prepared.

Community
  • 1
  • 1
M.V.
  • 1
  • 2
  • 2
    Possible duplicate of [When to use single quotes, double quotes, and backticks?](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) _tldr; do not use single quotes for aliases_ – Uueerdo Aug 10 '16 at 22:47
  • I didn't notice yesterday. What is your **real** database server type? _MySQL does not support `WITH` as far as I know. (The `+`'s for concatenation should've also been an indicator it was not MySQL.)_ I am guessing MS SQL Server? – Uueerdo Aug 11 '16 at 18:30
  • Hi Uueerdo! Thanks for your help. Our real database server type is SQL Server 2012. – M.V. Aug 11 '16 at 22:54
  • Sorry about that phrasing; maybe it is just me but it looks a bit snarky now... it was early. – Uueerdo Aug 11 '16 at 23:48
  • Have you tried putting the whole query inside a `SELECT * FROM (`...`)`. I think the MSQuery parser it trying to see if it can render a graphical representation. This will force the parser to give up when it hits the first bracket. – Unoembre Aug 12 '16 at 08:15
  • Thanks for the suggestion Unoembre! I just tried that and it returned a "could not add the table" error. You might be on to something though - I forgot to mention that the error in my initial posting is the first of two errors. The first error is "SQL Query can't be represented graphically. Continue anyway?" I neglected to post this since it seemed to be allowing me to continue. Thanks again for this suggestion! – M.V. Aug 16 '16 at 22:21
  • FOUND THE SOLUTION! THANK YOU @WCWEDIN http://stackoverflow.com/questions/4831667/using-parameters-in-sql-query-with-sub-query – M.V. Aug 16 '16 at 22:38

0 Answers0