A query using the results from another query

(3 Pages)
←
1
2
3
→

31 Replies - 3644 Views - Last Post: 24 April 2009 - 10:28 AM Rate Topic:

#16 sansclue

D.I.C Regular

Reputation: 29

Posts: 316
Joined: 21-November 07

Re: A query using the results from another query

Posted 15 April 2009 - 10:30 AM

iamjesus1342027, on 15 Apr, 2009 - 09:25 AM, said:

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

Can you run that exact code, and post the full debug output (and the exact error message) here?

Was This Post Helpful? 0

#17 iamjesus1342027

New D.I.C Head

Reputation: 0

Posts: 41
Joined: 31-March 09

Re: A query using the results from another query

Posted 15 April 2009 - 10:56 AM

<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#">

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'.

Was This Post Helpful? 0

#18 sansclue

D.I.C Regular

Reputation: 29

Posts: 316
Joined: 21-November 07

Re: A query using the results from another query

Posted 15 April 2009 - 11:02 AM

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)


<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 April 2009 - 11:05 AM

Was This Post Helpful? 0

#19 iamjesus1342027

New D.I.C Head

Reputation: 0

Posts: 41
Joined: 31-March 09

Re: A query using the results from another query

Posted 15 April 2009 - 11:27 AM

follow this link to see.

http://172.16.10.41/...DB59E7110FD2C68

Was This Post Helpful? 0

#20 sansclue

D.I.C Regular

Reputation: 29

Posts: 316
Joined: 21-November 07

Re: A query using the results from another query

Posted 15 April 2009 - 11:44 AM

iamjesus1342027, on 15 Apr, 2009 - 10:27 AM, said:

follow this link to see.

http://172.16.10.41/...DB59E7110FD2C68

The link is just timing out.

The server at 172.16.10.41 is taking too long to respond.

Was This Post Helpful? 0

#21 iamjesus1342027

New D.I.C Head

Reputation: 0

Posts: 41
Joined: 31-March 09

Re: A query using the results from another query

Posted 15 April 2009 - 11:49 AM

sorry i changed the code without thinking. try it again.

Was This Post Helpful? 0

#22 sansclue

D.I.C Regular

Reputation: 29

Posts: 316
Joined: 21-November 07

Re: A query using the results from another query

Posted 15 April 2009 - 12:09 PM

iamjesus1342027, on 15 Apr, 2009 - 10:49 AM, said:

sorry i changed the code without thinking. try it again.

No, it is still timing out.

Was This Post Helpful? 0

#23 iamjesus1342027

New D.I.C Head

Reputation: 0

Posts: 41
Joined: 31-March 09

Re: A query using the results from another query

Posted 15 April 2009 - 12:14 PM

alright i attached the results because its too large to fit.

Attached File(s)

query.doc (1.44MB)
Number of downloads: 64

Was This Post Helpful? 0

#24 sansclue

D.I.C Regular

Reputation: 29

Posts: 316
Joined: 21-November 07

Re: A query using the results from another query

Posted 15 April 2009 - 12:27 PM

iamjesus1342027, on 15 Apr, 2009 - 11:14 AM, said:

alright i attached the results because its too large to fit.

Much better. It is a problem with your underlying data. The issue is some of the values from:

DictionaryField.LookupTable,
DictionaryField.LookupType

Are literally blank. Not all of them, just some. Unfortunately the ones in the first row of the getTables query are blank. When you use this syntax in the Lookup query:

#getTable.LookupType#

It is shorthand for "give me the value in the first row of the getTable query". Since that value is blank, that is why you are getting a syntax error.

Was This Post Helpful? 0

#25 iamjesus1342027

New D.I.C Head

Reputation: 0

Posts: 41
Joined: 31-March 09

Re: A query using the results from another query

Posted 16 April 2009 - 06:27 AM

well is there anything i can do to fix this? I thought using that if statement would keep it from trying to use a empty field but i guess not.

Was This Post Helpful? 0

#26 sansclue

D.I.C Regular

Reputation: 29

Posts: 316
Joined: 21-November 07

Re: A query using the results from another query

Posted 16 April 2009 - 09:52 AM

iamjesus1342027, on 16 Apr, 2009 - 05:27 AM, said:

well is there anything i can do to fix this? I thought using that if statement would keep it from trying to use a empty field but i guess not.

Yes, you could use a separate query to only pull non-empty LookUpTable values. But I think there is a logic problem in the query/code. Mind you I do not understand the relationships, but a few things strike me as odd

1) Why are the lookup values empty in the first place
2) Even if all of the values were non-empty, they are not all the same:
ApplicationLookUp
ExploitationIEDLookup

Your "Lookup" query can only use one table, so how do you know which one is the right one to use for your query?

Was This Post Helpful? 0

#27 iamjesus1342027

New D.I.C Head

Reputation: 0

Posts: 41
Joined: 31-March 09

Re: A query using the results from another query

Posted 16 April 2009 - 11:55 AM

alright i have narrowed things down a bit. i have this code where i am receiving an error.

<cfquery name="Lookup" datasource="#session.sysdb#">
							SELECT LookupType, DisplayOrder, LookupValue
							FROM #getTable.LookupTable#
							WHERE LookupType = '#getTable.LookupType#'
							ORDER BY DisplayOrder, LookupValue
						</cfquery>					
						<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"><cfset commaStopper = ""><cfloop query="Lookup">#commaStopper##LookupValue#<cfset commaStopper = ","></cfloop></ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row></ss:Row>

i get the error XML parse error: missing end tag.

i have looked over this code a hundred times but cant find anything. can anyone else see what my problem is???

Was This Post Helpful? 0

#28 sansclue

D.I.C Regular

Reputation: 29

Posts: 316
Joined: 21-November 07

Re: A query using the results from another query

Posted 17 April 2009 - 08:11 AM

I don't see anything obvious from that snippet other than the <ss:Row> tags are imbalanced.

Was This Post Helpful? 0

#29 iamjesus1342027

New D.I.C Head

Reputation: 0

Posts: 41
Joined: 31-March 09

Re: A query using the results from another query

Posted 17 April 2009 - 11:22 AM

well i have figured out exactly what the problem is. for some reason excel doesnt like the fact that i have a defined a query in it. i am wondering if there is a namespace or something that i can define at the beginning of my code that will allow excel to deal with the query. this is my code that should work.

<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>

<!--- Tells coldfusion what type of file will be created (excel spreadsheet) and names the file --->
<cfheader name="content-disposition" value="inline;filename=#ReportName#ReportData.xls">
<cfcontent type="application/msexcel">

<!--- 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>


<!--- Creates the excel file --->
<?xml version="1.0"?>
<ss:Workbook 
xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel">
	
	<!--- Creates the various styles/formats to be used on data --->
	<ss:Styles>
		<ss:Style ss:ID="Header">
			<ss:Font ss:FontName="Calibri" ss:Bold="1" ss:Size="15"/>
		</ss:Style>
		<ss:Style ss:ID="Description">
			<ss:Alignment ss:WrapText="1" ss:Vertical="Top"/>
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Top"/>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="TableDescription">
			<ss:Alignment ss:WrapText="1" ss:Vertical="Top"/>
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Top"/>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Bottom"/>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="Sides">
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="Ordinality">
			<ss:Alignment ss:Horizontal="Right"/>
		</ss:Style>
		<ss:Style ss:ID="Subheader">
			<ss:Font ss:FontName="Calibri" ss:Bold="1" ss:Size="13"/>
			<ss:Alignment ss:Horizontal="Right"/>
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="2" ss:Position="Bottom"/>
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="TopLeft">
			<ss:Alignment ss:Horizontal="Right"/>
			<ss:Interior ss:Color="#CCCCCC" ss:Pattern="Solid"/>
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Top"/>
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="Top">
			<ss:Alignment ss:Horizontal="Right"/>
			<ss:Interior ss:Color="#CCCCCC" ss:Pattern="Solid"/>
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Top"/>
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="TopRight">
			<ss:Alignment ss:Horizontal="Right"/>
			<ss:Interior ss:Color="#CCCCCC" ss:Pattern="Solid"/>
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Top"/>
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="DescriptionRight">
			<ss:Alignment ss:WrapText="1" ss:Vertical="Top"/>
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="Right">
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="Left">
			<ss:Alignment ss:Horizontal="Right"/>
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="BottomLeft">
			<ss:Alignment ss:Horizontal="Right"/>
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Bottom"/>
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="BottomRight">
			<ss:Alignment ss:WrapText="1" ss:Vertical="Top"/>
			<ss:Borders>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Bottom"/>
				<ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
			</ss:Borders>
		</ss:Style>
	</ss:Styles>
	
	<!--- Creates the Worksheet and sets the page layout to landscape --->
	<ss:Worksheet ss:Name="Report Data">
		<x:WorksheetOptions>
			<x:PageSetup>
				<x:Layout x:Orientation="Landscape"/>
			</x:PageSetup>
		</x:WorksheetOptions>
		
		<!--- Sets up the table which will contain all of the data. Loops the queries to get 
			 all of the data and put it in the specific cells and rows. Also formats the
			 cells, rows, and columns. --->
		<ss:Table>
			<ss:Column ss:Index="1" ss:Width="200"/>
			<ss:Column ss:Index="2" ss:Width="150"/>
			<ss:Column ss:Index="3" ss:Width="87"/>
			<ss:Column ss:Index="4" ss:Width="47"/>
			<ss:Column ss:Index="5" ss:Width="98"/>
			<cfoutput query="getReport">
				<ss:Row>
					<ss:Cell ss:Index="1" ss:StyleID="Header"><ss:Data ss:Type="String">Report Name: #Module# #ReportType#</ss:Data></ss:Cell>
				</ss:Row>
			</cfoutput>
			<cfoutput query="getReport">
				<ss:Row>
					<ss:Cell ss:Index="1" ss:StyleID="Ordinality"><ss:Data ss:Type="String">Description:</ss:Data></ss:Cell>
					<cfif #ReportDescription# eq "">
						<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="TableDescription"><ss:Data ss:Type="String">No Description Entered</ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row></ss:Row>
					<cfelse>
						<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:MergeDown="5" ss:StyleID="TableDescription"><ss:Data ss:Type="String">#ReportDescription#</ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>
					</cfif>
			</cfoutput>
			<ss:Row>
				<ss:Cell ss:Index="1" ss:StyleID="Subheader"><ss:Data ss:Type="String">Tables</ss:Data></ss:Cell>
				<ss:Cell ss:Index="2" ss:StyleID="Subheader"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
				<ss:Cell ss:Index="3" ss:StyleID="Subheader"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
				<ss:Cell ss:Index="4" ss:StyleID="Subheader"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
				<ss:Cell ss:Index="5" ss:StyleID="Subheader"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
			</ss:Row>
			<cfoutput query="getTableName">
				<ss:Row>
					<ss:Cell ss:Index="1" ss:StyleID="Ordinality"><ss:Data ss:Type="String">#TableShortName#</ss:Data></ss:Cell>
				</ss:Row>
				<ss:Row>
					<ss:Cell ss:Index="1" ss:StyleID="Ordinality"><ss:Data ss:Type="String">Description:</ss:Data></ss:Cell>
					<cfif #TableDescription# eq "">
						<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="TableDescription"><ss:Data ss:Type="String">No Description Entered</ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row></ss:Row>
					<cfelse>
						<ss:Cell ss:Index="2" ss:MergeAcross="3" ss:MergeDown="7" ss:StyleID="TableDescription"><ss:Data ss:Type="String">#TableDescription#</ss:Data></ss:Cell>
						</ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>
						<ss:Row></ss:Row>	
					</cfif>
			</cfoutput>
			<ss:Row></ss:Row>
			<ss:Row>
				<ss:Cell ss:Index="1" ss:StyleID="Subheader"><ss:Data ss:Type="String">Fields</ss:Data></ss:Cell>
				<ss:Cell ss:Index="2" ss:StyleID="Subheader"><ss:Data ss:Type="String">Table Name</ss:Data></ss:Cell>
				<ss:Cell ss:Index="3" ss:StyleID="Subheader"><ss:Data ss:Type="String">Data Type</ss:Data></ss:Cell>
				<ss:Cell ss:Index="4" ss:StyleID="Subheader"><ss:Data ss:Type="String">Length</ss:Data></ss:Cell>
				<ss:Cell ss:Index="5" ss:StyleID="Subheader"><ss:Data ss:Type="String">Field Required?</ss:Data></ss:Cell>
			</ss:Row>
			<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"></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"></ss:Data></ss:Cell>
						</ss:Row>
					</cfif>
				<ss:Row>
					<cfif #IsPickList# eq "0" OR #IsPickList# 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">This field is not a picklist</ss:Data></ss:Cell>
						
					<cfif #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>
						<cfquery name="Lookup" datasource="#session.sysdb#">
							SELECT LookupType, DisplayOrder, LookupValue
							FROM #getTable.LookupTable#
							WHERE LookupType = '#getTable.LookupType#'
							ORDER BY DisplayOrder, LookupValue
						</cfquery>					
						<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:MergeDown="5" ss:StyleID="BottomRight"><ss:Data ss:Type="String"><cfset commaStopper = ""><cfloop query="Lookup">#commaStopper# #LookupValue#<cfset commaStopper = ","></cfloop></ss:Data></ss:Cell>
					</cfif>
				</ss:Row>
				<ss:Row></ss:Row>
				<ss:Row></ss:Row>
				<ss:Row></ss:Row>
				<ss:Row></ss:Row>
				<ss:Row></ss:Row>
				<ss:Row></ss:Row>
			</cfoutput>
		</ss:Table>
	</ss:Worksheet>
</ss:Workbook>

the lookup query is within the cfoutput tags for the get table query which allows it to properly reference the values. also i have the if statement which prevents the query from being read with an empty field. the reason i know this code should work is because i tested it like this.

<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>

<cfoutput query="getTable">
<cfif #IsPickList# eq "0" OR #IsPickList# eq "">
	
	its not a picklist
<cfelseif #LookupType# eq "" OR #LookupTable# eq "">
none

<cfelse>
<cfquery name="Lookup" datasource="#session.sysdb#">
		SELECT *
		FROM #getTable.LookupTable#
		WHERE LookupType = '#getTable.LookupType#'
		ORDER BY DisplayOrder, LookupValue
	</cfquery>
	<cfset commaStopper = "">
	<cfloop query="Lookup">
	#commaStopper# #LookupValue#
	<cfset commaStopper = ",">
	</cfloop>
</cfif>
<br>
</cfoutput>

this code properly loops both queries and doesnt have a problem. this second code just simply displays the output on a webpage. so i have determined that excel is having the problem running the code. if there are any experts on exporting to excel please help! thanks!

Was This Post Helpful? 0

#30 sansclue

D.I.C Regular

Reputation: 29

Posts: 316
Joined: 21-November 07

Re: A query using the results from another query

Posted 17 April 2009 - 11:50 AM

iamjesus1342027, on 17 Apr, 2009 - 10:22 AM, said:

i am wondering if there is a namespace or something that i can define at the beginning of my code that will allow excel to deal with the query.

No. There isn't.

If your code results in malformed output (from Excel's perspective) it will not be able to parse it. It is not a good idea to mix queries and output code as your doing for exactly this reason: it generate extra output. In some cases using cfsetting, cfsilent, etc.. helps suppress extraneous output, but it all depends on your code.

If bad output is the problem, you have to reformat the code to generate valid output. One option is use cfsavecontent to save the output to a string. Then output the string after your header and cfcontent. But if you don't find out what part of your code is producing the bad/extra output, that still won't help.

<cfheader name="content-disposition" value="inline;filename=#ReportName#ReportData.xls">
<cfcontent type="application/msexcel">


Good Luck

Was This Post Helpful? 0

(3 Pages)
←
1
2
3
→

Re: A query using the results from another query

Re: A query using the results from another query

Re: A query using the results from another query

Re: A query using the results from another query

Re: A query using the results from another query

Re: A query using the results from another query

Re: A query using the results from another query

Re: A query using the results from another query

Attached File(s)

Re: A query using the results from another query

Re: A query using the results from another query

Re: A query using the results from another query

Re: A query using the results from another query

Re: A query using the results from another query

Re: A query using the results from another query

Re: A query using the results from another query

Follow & Share

Give It A Try!

Programming

Web Development

Reference Sheets

Bye Bye Ads

Monthly Drawing