k j p
04-21-2014, 10:49 AM
I will retry posting since the other post is raising an error on the page.
I have a SELECT statement which uses OPENQUERY calling another SELECT statement...runs fine in a standard query tool:
SELECT OEE_PartNumber,
AVG(OEE_LineOverallOEE) as
OEE_AVG ,
SUM(OEE_GoodFootage) as FT_Good,
SUM(OEE_BadFootage) as OEE_FT_Bad
FROM
OPENQUERY(INSQL,
"SELECT DateTime, [OEE_LineID_String], [OEE_PartNumber],
[OEE_ProductFamily],
[OEE_GoodFootage], [OEE_BadFootage], [OEE_LineOverallOEE]
FROM WideHistory
WHERE
[OEE_ProductionShift] = 3
AND [OEE_PartNumber] =
'Part2'
AND
wwRetrievalMode = 'Cyclic'
AND wwVersion = 'Latest'
AND DateTime >=
'2014-03-01'")
group by OEE_PartNumber
However, when attempting to embed this query in VBA (tied to a command button in Excel for example), I usually build the SQL statement using a temporary string variable: For example:
Dim sqlstr as string
sqlstr = ""
sqlstr = sqlstr & "SELECT OEE_PartNumber, "
sqlstr = sqlstr & "AVG(OEE_LineOverallOEE) as OEE_AVG , "
sqlstr = sqlstr & "SUM(OEE_GoodFootage) as FT_Good, "
sqlstr = sqlstr & "SUM(OEE_BadFootage) as OEE_FT_Bad "
sqlstr = sqlstr & "FROM "
sqlstr = sqlstr & "OPENQUERY(INSQL, "SELECT DateTime, [OEE_LineID_String], [OEE_PartNumber], "
sqlstr = sqlstr & "[OEE_ProductFamily], [OEE_GoodFootage], [OEE_BadFootage], OEE_LineOverallOEE]"
sqlstr = sqlstr & "FROM WideHistory " sqlstr = sqlstr & " WHERE [OEE_ProductionShift] = 3 "
sqlstr = sqlstr & " AND [OEE_PartNumber] = 'Part2' "
sqlstr = sqlstr & " AND wwRetrievalMode = 'Cyclic' "
sqlstr = sqlstr & " AND wwVersion = 'Latest' "
sqlstr = sqlstr & " AND DateTime >= '2014-03-01'") "
sqlstr = sqlstr & "group by OEE_PartNumber "
This SQL string causes an error on execute due to the use of double quotes which start off the SELECT in the OPENQUERY section. Replacing with single, and double single quotes doesn't work. Any suggestions on how to overcome this?
I have a SELECT statement which uses OPENQUERY calling another SELECT statement...runs fine in a standard query tool:
SELECT OEE_PartNumber,
AVG(OEE_LineOverallOEE) as
OEE_AVG ,
SUM(OEE_GoodFootage) as FT_Good,
SUM(OEE_BadFootage) as OEE_FT_Bad
FROM
OPENQUERY(INSQL,
"SELECT DateTime, [OEE_LineID_String], [OEE_PartNumber],
[OEE_ProductFamily],
[OEE_GoodFootage], [OEE_BadFootage], [OEE_LineOverallOEE]
FROM WideHistory
WHERE
[OEE_ProductionShift] = 3
AND [OEE_PartNumber] =
'Part2'
AND
wwRetrievalMode = 'Cyclic'
AND wwVersion = 'Latest'
AND DateTime >=
'2014-03-01'")
group by OEE_PartNumber
However, when attempting to embed this query in VBA (tied to a command button in Excel for example), I usually build the SQL statement using a temporary string variable: For example:
Dim sqlstr as string
sqlstr = ""
sqlstr = sqlstr & "SELECT OEE_PartNumber, "
sqlstr = sqlstr & "AVG(OEE_LineOverallOEE) as OEE_AVG , "
sqlstr = sqlstr & "SUM(OEE_GoodFootage) as FT_Good, "
sqlstr = sqlstr & "SUM(OEE_BadFootage) as OEE_FT_Bad "
sqlstr = sqlstr & "FROM "
sqlstr = sqlstr & "OPENQUERY(INSQL, "SELECT DateTime, [OEE_LineID_String], [OEE_PartNumber], "
sqlstr = sqlstr & "[OEE_ProductFamily], [OEE_GoodFootage], [OEE_BadFootage], OEE_LineOverallOEE]"
sqlstr = sqlstr & "FROM WideHistory " sqlstr = sqlstr & " WHERE [OEE_ProductionShift] = 3 "
sqlstr = sqlstr & " AND [OEE_PartNumber] = 'Part2' "
sqlstr = sqlstr & " AND wwRetrievalMode = 'Cyclic' "
sqlstr = sqlstr & " AND wwVersion = 'Latest' "
sqlstr = sqlstr & " AND DateTime >= '2014-03-01'") "
sqlstr = sqlstr & "group by OEE_PartNumber "
This SQL string causes an error on execute due to the use of double quotes which start off the SELECT in the OPENQUERY section. Replacing with single, and double single quotes doesn't work. Any suggestions on how to overcome this?