Join 300,574 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 2,195 people online right now. Registration is fast and FREE... Join Now!
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.
CODE
<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.
CODE
<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.
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.
CODE
<cfquery name="Lookup" datasource="#session.sysdb#"> SELECT LookupType, LookupValue, DisplayOrder FROM #getTable.LookupTable# WHERE LookupType = #getTable.LookupType# ORDER BY DisplayOrder, LookupValue </cfquery>
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.
CODE
<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.
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>
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!!
CODE
<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>
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 Apr, 2009 - 12:27 PM
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??
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.
CODE
<cfquery name="Lookup" datasource="#session.sysdb#"> SELECT * FROM #theTable# WHERE LookupType = '#theType#' ORDER BY DisplayOrder, LookupValue </cfquery>
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??
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.adobe.com/coldfusion/8/htm...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
CODE
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>
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.adobe.com/coldfusion/8/htm...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
CODE
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 -->
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.
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?
also i just realized something that is very strange. i have this if statment.
CODE
<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.
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 Apr, 2009 - 09:01 AM
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.
also when i ran the dump this is the only code i had.
CODE
<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.
<!--- 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#">
error message
The system could not complete your request.
Please provide the following information to technical support:
Event:
Event Message: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'.
Event Detail: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'.
Where is the debug output from the bottom of the page? Also, you have to dump the "getTable" query _before_ "Lookup" or you won't be able to see the results (ie since an error occurs before CF can get to that line)
CODE
<cfdump var="#getTable#">
<cfquery name="Lookup" datasource="#session.sysdb#"> SELECT * FROM #getTable.LookupTable# WHERE LookupType = '#getTable.LookupType#' ORDER BY DisplayOrder, LookupValue </cfquery>
This post has been edited by sansclue: 15 Apr, 2009 - 10:05 AM
Forum Index:
Programming Help |
C and C++ |
VB6 |
Java |
VB.NET |
C# |
ASP.NET |
PHP |
ColdFusion |
Perl and Python |
Ruby |
Databases |
Other Languages |
Game Programming |
Mobile Development |
Software Development |
Computer Science |
Industry News |
Programming Tutorials |
C++ Tutorials |
Visual Basic Tutorials |
Java Tutorials |
VB.NET Tutorials |
C# Tutorials |
Linux Tutorials |
PHP Tutorials |
ColdFusion Tutorials |
Windows Tutorials |
HTML/JavaScript Tutorials |
CSS Tutorials |
Flash Tutorials |
Web Promotion Tutorials |
Graphic Design & Photoshop Tutorials |
Software Development Tutorials |
Database Tutorials |
Other Language Tutorials |
ASP.NET Tutorials |
Game Programming Tutorials |
WPF & Silverlight Tutorials |
14 Apr, 2009 - 09:10 AM
| 
