[iamjesus1342027]

alright this is kinda complicated so ill do my best to explain it. i have two queries that i am using to display some data. the first query is a basic query with some joins where i get a lot of data. my problem occurs in the second query. i want to use some of the results of the first query as parameters in the second query.

<cfquery name="getTable" datasource="#session.sysdb#">
	SELECT DictionaryTable.TableKey, DictionaryTable.DisplayOrder, DictionaryTable.ReportKey, DictionaryField.ReportKey, DictionaryField.TableKey,   
							DictionaryField.DisplayOrder, DictionaryField.LookupTable, DictionaryField.LookupType, DictionaryField.IsPickList, DictionaryTable.TableShortName,
							DictionaryField.FieldName
	FROM DictionaryTable
	INNER JOIN DictionaryField ON DictionaryTable.TableKey = DictionaryField.TableKey
	WHERE DictionaryTable.ReportKey = '#url.reportkey#'
	AND DictionaryField.ReportKey = '#url.reportkey#'
	ORDER BY DictionaryTable.DisplayOrder, DictionaryField.DisplayOrder
</cfquery>

thats the first query. nothing complicated there. problems arise with my second query. i need to use DictionaryField.LookupTable and DictionaryField.LookupType from the first query in the second.

<cfquery name="Lookup" datasource="#session.sysdb#">
	SELECT LookupType, LookupValue, DisplayOrder
	FROM #DictionaryField.LookupTable#
	WHERE LookupType = #DictionaryField.LookupType#
	ORDER BY DisplayOrder, LookupValue
</cfquery>

the reason i am using 2 queries is because i have to loop each query separately to get the data i need and to have it displayed properly. any ideas would be greatly appreciated!! i am kinda stumped

thanks!!

i just realized this might help too. here is the section where i loop the queries. i am displaying all of the data in a spreadsheet.

<cfoutput query="getTable">
		<ss:Row>
				<ss:Cell ss:Index="1" ss:StyleID="TopLeft"><ss:Data ss:Type="String">#FieldName#</ss:Data></ss:Cell>
	<ss:Cell ss:Index="2" ss:StyleID="Top"><ss:Data ss:Type="String">#TableShortName#</ss:Data></ss:Cell>
		</ss:Row>
		<ss:Row>
	<cfif #IsPickList# eq "0">
		<ss:Cell ss:Index="1" ss:StyleID="BottomLeft"><ss:Data ss:Type="String">Lookup Values:</ss:Data></ss:Cell>
		<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="BottomRight"><ss:Data ss:Type="String">This field is not a picklist</ss:Data></ss:Cell>
		</ss:Row>
		<ss:Row></ss:Row>
	<cfelse>
		<ss:Cell ss:Index="1" ss:StyleID="BottomLeft"><ss:Data ss:Type="String">Lookup Values:</ss:Data></ss:Cell>
		<cfloop query="Lookup">
				  <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="BottomRight"><ss:Data ss:Type="String">#LookupValue#, </ss:Data></ss:Cell>
		</cfloop>
		</ss:Row>
		<ss:Row></ss:Row>
	</cfif>
</cfoutput>	

[sansclue]

So the first query gives you the name of the table you have to query? To refer to a value from the first query, just use the query name. But also check the recordCount to make sure the first query returned results. Otherwise the value of #getTable.LookupTable# will be blank, which will cause a syntax error.

<cfquery name="Lookup" datasource="#session.sysdb#">
	SELECT LookupType, LookupValue, DisplayOrder
	FROM #getTable.LookupTable#
	WHERE LookupType = #getTable.LookupType#
	ORDER BY DisplayOrder, LookupValue
</cfquery>

[iamjesus1342027]

sansclue, on 14 Apr, 2009 - 09:48 AM, said:

So the first query gives you the name of the table you have to query? To refer to a value from the first query, just use the query name. But also check the recordCount to make sure the first query returned results. Otherwise the value of #getTable.LookupTable# will be blank, which will cause a syntax error.

<cfquery name="Lookup" datasource="#session.sysdb#">
	SELECT LookupType, LookupValue, DisplayOrder
	FROM #getTable.LookupTable#
	WHERE LookupType = #getTable.LookupType#
	ORDER BY DisplayOrder, LookupValue
</cfquery>

oh man i knew that lol. thanks for reminding me but for some reason it says i am getting a syntax error near the WHERE clause. any ideas for the cause of this?? i did set up an if statment that makes sure both getTable.LookupTable and getTable.LookupType are not null before running the Lookup query.

[sansclue]

iamjesus1342027, on 14 Apr, 2009 - 10:08 AM, said:

oh man i knew that lol. thanks for reminding me but for some reason it says i am getting a syntax error near the WHERE clause. any ideas for the cause of this?? i did set up an if statment that makes sure both getTable.LookupTable and getTable.LookupType are not null before running the Lookup query.

I don't see anything obvious, unless "LookupType" is a string maybe? In which case you need to add quotes around it. If that is not it, enable debugging or add a cfoutput to see what the sql being sent to the database looks like.


Debugging: This is the final sql statement<br>
<cfoutput>
SELECT LookupType, LookupValue, DisplayOrder
FROM #getTable.LookupTable#
WHERE LookupType = #getTable.LookupType#
ORDER BY DisplayOrder, LookupValue
</cfoutput>

[iamjesus1342027]

hmmmm im still not getting this to work. im receiving the same syntax error. here's the code i now have. any ideas would be great!!

<cfquery name="getTable" datasource="#session.sysdb#">
	SELECT DictionaryTable.TableShortName, DictionaryTable.TableKey, DictionaryTable.TableDescription, DictionaryTable.DisplayOrder, DictionaryTable.ReportKey, DictionaryField.ReportKey, DictionaryField.TableKey, DictionaryField.FieldName, DictionaryField.FieldDataType, DictionaryField.IsPickList, DictionaryField.FieldLength, DictionaryField.DisplayOrder, DictionaryField.LookupTable, DictionaryField.LookupType, DictionaryField.FieldRequired, DictionaryField.FieldDescription 
	FROM DictionaryTable
	INNER JOIN DictionaryField ON DictionaryTable.TableKey = DictionaryField.TableKey
	WHERE DictionaryTable.ReportKey = '#url.reportkey#'
	AND DictionaryField.ReportKey = '#url.reportkey#'
	ORDER BY DictionaryTable.DisplayOrder, DictionaryField.DisplayOrder
</cfquery>

<cfquery name="Lookup" datasource="#session.sysdb#">
	SELECT LookupType, LookupValue, DisplayOrder
	FROM #getTable.LookupTable#
	WHERE LookupType = '#getTable.LookupType#'
	ORDER BY DisplayOrder, LookupValue
</cfquery>

<cfoutput query="getTable">
				<ss:Row>
					<ss:Cell ss:Index="1" ss:StyleID="TopLeft"><ss:Data ss:Type="String">#FieldName#</ss:Data></ss:Cell>
					<ss:Cell ss:Index="2" ss:StyleID="Top"><ss:Data ss:Type="String">#TableShortName#</ss:Data></ss:Cell>
					<ss:Cell ss:Index="3" ss:StyleID="Top"><ss:Data ss:Type="String">#FieldDataType#</ss:Data></ss:Cell>
					<ss:Cell ss:Index="4" ss:StyleID="Top"><ss:Data ss:Type="Number">#FieldLength#</ss:Data></ss:Cell>
					<cfif #FieldRequired# eq "0">
						<ss:Cell ss:Index="5" ss:StyleID="TopRight"><ss:Data ss:Type="String">No</ss:Data></ss:Cell>
					<cfelse>
						<ss:Cell ss:Index="5" ss:StyleID="TopRight"><ss:Data ss:Type="String">Yes</ss:Data></ss:Cell>
					</cfif>
				</ss:Row>
				<ss:Row>
					<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String">Description:</ss:Data></ss:Cell>
					<cfif #FieldDescription# eq "">
						<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="DescriptionRight"><ss:Data ss:Type="String">No Description Entered</ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row>
							<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
							<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="Right"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
						</ss:Row>
					<cfelse>
						<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:MergeDown="10" ss:StyleID="DescriptionRight"><ss:Data ss:Type="String">#FieldDescription#</ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row>
							<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row>
							<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row>
							<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row>
							<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row>
							<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String">type - #LookupType#</ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row>
							<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String">table - #LookupTable#</ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row>
							<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row>
							<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row>
							<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row>
							<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
						</ss:Row>
					</cfif>
				<ss:Row>
					<cfif #IsPickList# eq "0">
						<ss:Cell ss:Index="1" ss:StyleID="BottomLeft"><ss:Data ss:Type="String">Lookup Values:</ss:Data></ss:Cell>
						<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="BottomRight"><ss:Data ss:Type="String">This field is not a picklist</ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row></ss:Row>
					<cfelse>
						<ss:Cell ss:Index="1" ss:StyleID="BottomLeft"><ss:Data ss:Type="String">Lookup Values:</ss:Data></ss:Cell>
						<cfif #LookupType# neq "" & #LookupTable# neq "">
							<cfloop query="Lookup">
							<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="BottomRight"><ss:Data ss:Type="String">#LookupValue#, </ss:Data></ss:Cell>
							</cfloop>
						<cfelse>
							<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="BottomRight"><ss:Data ss:Type="String">The lookup table and type are not defined</ss:Data></ss:Cell>
						</cfif>
						</ss:Row>
						<ss:Row></ss:Row>
					</cfif>
			</cfoutput>	

[sansclue]

Unfortunately, I can't tell you anything from just looking the CF code ;-) That is always the way with dynamic queries. With those you have to look at the actual sql that is generated, not just the cf code. In other words, you need to know what is the final sql string looks like after the variables are replaced/

Did you enable debugging? That will show the actual sql being sent to your database. Usually looking at the sql is all it takes to spot the problem.

This post has been edited by sansclue: 14 April 2009 - 01:27 PM

[iamjesus1342027]

sansclue, on 14 Apr, 2009 - 12:25 PM, said:

Unfortunately, I can't tell you anything from just looking the CF code ;-) That is always the way with dynamic queries. With those you have to look at the actual sql that is generated, not just the cf code. In other words, you need to know what is the final sql string looks like after the variables are replaced/

Did you enable debugging? That will show the actual sql being sent to your database. Usually looking at the sql is all it takes to spot the problem.

i cant seem to get debugging to work. i am using eclipse as my code editing program and im new to it. does anyone know how it works??

[iamjesus1342027]

i have changed some things and am getting a very strange output now. i decided to use cfset to declare some variables and use them in the sql statement.

<cfquery name="Lookup" datasource="#session.sysdb#">
	SELECT *
	FROM #theTable#
	WHERE LookupType = '#theType#'
	ORDER BY DisplayOrder, LookupValue
</cfquery>

<cfset theType = "#LookupType#">
<cfset theTable = "#LookupTable#">

i thought maybe this would work but no luck. i dont get an error though. all the code runs fine and a spreadsheet opens with one line of output. it says this
<!-- The queries to get all of the data for the spreadsheets -->
which is actually a commented out section of my code. i really dont understand this at all. any ideas??

[sansclue]

iamjesus1342027, on 15 Apr, 2009 - 05:33 AM, said:

i cant seem to get debugging to work. i am using eclipse as my code editing program and im new to it. does anyone know how it works??

Assuming you are working locally, you enable debugging in the CF Administrator. Under Debugging & Logging -> Debug Output Settings: Make sure Enable Robust Exception Information and Database Activity are checked. Then CF will display the sql query information (and other variables) at the bottom of every page.
http://livedocs.adob...s/Debug_02.html

If you don't have access to the CF Admin, you can also "do it yourself". Just copy the text in the cfquery and put it inside cfoutput tags to see what the sql sent to the database looks like

Do it yourself debugging: This is what the final sql statement will look like:<br>
<cfoutput>
SELECT LookupType, LookupValue, DisplayOrder
FROM #getTable.LookupTable#
WHERE LookupType = #getTable.LookupType#
ORDER BY DisplayOrder, LookupValue
</cfoutput>

[sansclue]

iamjesus1342027, on 15 Apr, 2009 - 05:33 AM, said:

i cant seem to get debugging to work. i am using eclipse as my code editing program and im new to it. does anyone know how it works??

Assuming you are working locally, you enable debugging in the CF Administrator. Under Debugging & Logging -> Debug Output Settings: Make sure Enable Robust Exception Information and Database Activity are checked. Then CF will display the sql query information (and other variables) at the bottom of every page.
http://livedocs.adob...s/Debug_02.html

If you don't have access to the CF Admin, you can also "do it yourself". Just copy the text in the cfquery and put it inside cfoutput tags to see what the sql sent to the database looks like

Do it yourself debugging: This is what the final sql statement will look like:<br>
<cfoutput>
SELECT LookupType, LookupValue, DisplayOrder
FROM #getTable.LookupTable#
WHERE LookupType = #getTable.LookupType#
ORDER BY DisplayOrder, LookupValue
</cfoutput>

As far as why the spreadsheet is blank, check the simple/obvious causes. First dump the "Lookup" query. Does it even contain any data? If it doesn't then that's your problem.

<cfdump var="#Lookup#">

Also the comment line "<-- ... -->" line shows up because it does not have enough dashes. CF comments use three dashes "-", not two:

<!--- this is a CF Comment. it will not be included in any output --->
<!-- this is an html comment. it will be included in any ouput -->

[iamjesus1342027]

alright i did the diy debugging and found out that the values of getTable.LookupTable and getTable.LookupType are empty. so thats obviously why i was getting the syntax error but im not sure where to go from here. i have used these values elsewhere and they are not empty. for example.

<ss:Row>
<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String">type - #LookupType#</ss:Data></ss:Cell>
</ss:Row>
<ss:Row>
<ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String">table - #LookupTable#</ss:Data></ss:Cell>
</ss:Row>

this displays the correct values from the database. for some reason when i try to reference these values i am unable to. the data isnt getting transferred from the first query to the next. any ideas?

[iamjesus1342027]

also i just realized something that is very strange. i have this if statment.

<cfif #IsPickList# eq "0">
						<ss:Cell ss:Index="1" ss:StyleID="BottomLeft"><ss:Data ss:Type="String">Lookup Values:</ss:Data></ss:Cell>
						<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="BottomRight"><ss:Data ss:Type="String">This field is not a picklist</ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row></ss:Row>
					<cfelseif #LookupTable# eq "" OR #LookupType# eq "">
						<ss:Cell ss:Index="1" ss:StyleID="BottomLeft"><ss:Data ss:Type="String">Lookup Values:</ss:Data></ss:Cell>
						<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="BottomRight"><ss:Data ss:Type="String">The lookup table and type are not defined</ss:Data></ss:Cell>
					<cfelse>
						<ss:Cell ss:Index="1" ss:StyleID="BottomLeft"><ss:Data ss:Type="String">Lookup Values:</ss:Data></ss:Cell>
						<cfloop query="Lookup">
							<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="BottomRight"><ss:Data ss:Type="String">#LookupValue#, </ss:Data></ss:Cell>
						</cfloop>
					</ss:Row>
					<ss:Row></ss:Row>
					</cfif>

so if lookuptable or lookupfield dont have any value in them then the query shouldnt even be called. so i dont understand why there would be an error saying there isnt anything in these fields.

[sansclue]

iamjesus1342027, on 15 Apr, 2009 - 08:44 AM, said:

alright i did the diy debugging and found out that the values of getTable.LookupTable and getTable.LookupType are empty. so thats obviously why i was getting the syntax error but im not sure where to go from here. i have used these values elsewhere and they are not empty. for example.

There is no way the values from the first query can be empty in one place but not in another unless a) the query was modified in your code or b ) you are using actually using different values.

Start simply and get the queries working before first. Then worry about the output. Run the first query "getTable" and dump the data immediately after the query.

<cfdump var="#getTable#">

1. Does it return any records at all?
2. If it does, are the values for "LookupTable" and "LookupType" empty?

If it does not return any records then you may be passing in the wrong value for #url.reportkey#. If it does return 1 or more records, but the column values are blank .. the problem is the underlying data in the table.

WHERE DictionaryTable.ReportKey = '#url.reportkey#'
AND DictionaryField.ReportKey = '#url.reportkey#'

This post has been edited by sansclue: 15 April 2009 - 10:01 AM

[iamjesus1342027]

actually everything works except the lookup query. i had the whole thing working but needed to display some more data and so i added the lookup query. i just tried using cfdump though and received the same error message about the syntax near the WHERE clause.

[iamjesus1342027]

also when i ran the dump this is the only code i had.

<cfsetting showdebugoutput="false">

<!--- Gets the report key from the url that was passed from the previous page (ExportToExcel.cfm) --->
<cfparam name="url.reportkey" default="None">

<!--- Get the name of the report to be used in the file name --->
<cfquery name="getReportName" datasource="#session.sysdb#">
	SELECT ReportName, ReportKey 
	FROM DictionaryReport
	WHERE ReportKey = '#url.reportkey#'
</cfquery>
<cfoutput query="getReportName">
	<cfset ReportName=#ReportName#>
</cfoutput>


<!--- The queries to get all of the data for the spreadsheets --->
<cfquery name="getReport" datasource="#session.sysdb#">
	SELECT * 
	FROM DictionaryReport
	WHERE ReportKey = '#url.reportkey#'
</cfquery>
<cfquery name="getTableName" datasource="#session.sysdb#">
	SELECT *
	FROM DictionaryTable
	WHERE DictionaryTable.ReportKey = '#url.reportkey#'
	ORDER BY DisplayOrder
</cfquery>
<cfquery name="getTable" datasource="#session.sysdb#">
	SELECT DictionaryTable.TableShortName, DictionaryTable.TableKey, DictionaryTable.TableDescription, DictionaryTable.DisplayOrder, DictionaryTable.ReportKey, DictionaryField.ReportKey, DictionaryField.TableKey, DictionaryField.FieldName, DictionaryField.FieldDataType, DictionaryField.IsPickList, DictionaryField.FieldLength, DictionaryField.DisplayOrder, DictionaryField.LookupTable, DictionaryField.LookupType, DictionaryField.FieldRequired, DictionaryField.FieldDescription 
	FROM DictionaryTable
	INNER JOIN DictionaryField ON DictionaryTable.TableKey = DictionaryField.TableKey
	WHERE DictionaryTable.ReportKey = '#url.reportkey#'
	AND DictionaryField.ReportKey = '#url.reportkey#'
	ORDER BY DictionaryTable.DisplayOrder, DictionaryField.DisplayOrder
</cfquery>
<cfquery name="Lookup" datasource="#session.sysdb#">
	SELECT *
	FROM #getTable.LookupTable#
	WHERE LookupType = '#getTable.LookupType#'
	ORDER BY DisplayOrder, LookupValue
</cfquery>
<cfdump var="#getTable#">

this caused the same syntax error as before. removing the lookup query gets rid of the error and allows the dump to run.