ColdFusion RSS ColdFusion RSS

query cfoutput help

Reply

•
•
Join Date: Jan 2009
Posts: 20
Reputation: jedimatt is an unknown quantity at this point 
Solved Threads: 1
jedimatt jedimatt is offline Offline
Newbie Poster

query cfoutput help

 
0
  #1
Jan 26th, 2009
Hello,

How can i produce the following output from a query:

<group1>
<value="100"> <-- This value is in a table
<value=""> <--The null ones are not in a table
<value="">
<value="">

<group2>
<value="">
<value="230">
<value="">
<value="">

<group3>
<value="">
<value="">
<value="154">
<value="">


<group4>
<value="">
<value="">
<value="">
<value="300">

We always no how many rows to return in each group
This formatted data is used for xml graphing output.

Does anyone have any ideas please?

JM
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 45
Reputation: arrgh is an unknown quantity at this point 
Solved Threads: 6
arrgh arrgh is offline Offline
Light Poster

Re: query cfoutput help

 
0
  #2
Jan 28th, 2009
•
•
•
•
Originally Posted by jedimatt View Post
<group1>
<value="100"> <-- This value is in a table
<value=""> <--The null ones are not in a table
<value="">
<value="">

We always no how many rows to return in each group
This formatted data is used for xml graphing output.
What do you mean not in the table? In other words, if they are not in your table what determines that one value should be in first "value" position, versus the second, third or fourth?
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 20
Reputation: jedimatt is an unknown quantity at this point 
Solved Threads: 1
jedimatt jedimatt is offline Offline
Newbie Poster

Re: query cfoutput help

 
0
  #3
Jan 28th, 2009
Yes good question,

Let me explain further:
I didnt add that there is a sort order for each value and a grouping order.
The output from the query without doing the xml output is this:

FailureMode Value Group SortOrder
Operation1 100 1 1
Operation2 350 2 2
Operation3 50 3 3
Operation4 120 3 4
Operation5 60 3 5

What i need to acheive is outputting the results to produce the following xml.
(This creates a stacked graph)


ColdFusion Syntax (Toggle Plain Text) 
  1. Group1
  2. <set value=100>
  3. <set value="">
  4. <set value="">
  5.  
  6.  
  7. Group2
  8. <set value="">
  9. <set value="350">
  10. <set value="">
  11.  
  12.  
  13.  
  14. Group3
  15. <set value="">
  16. <set value="">
  17. <set value="50">
  18.  
  19.  
  20. Group3
  21. <set value="">
  22. <set value="">
  23. <set value="120">
  24.  
  25.  
  26. Group3
  27. <set value="">
  28. <set value="">
  29. <set value="60">

To create a stack on the group 3 it is important to place the values in the correct location on the output. i.e. Each result on group 3 is on the third row.

Now this needs to be dynamic so any group of results will get stacked.

Is this possible to achieve or do i need to store something else in the results table to produce the desired output?

I have managed to get so far using the rownumber to determine postioning, what i can't figure out is how to place the grouped values in on location so that it produces the stack.

Its a bit messy using loops but i cant figure out any other way right now.

Here's what i have so far with queries:

ColdFusion Syntax (Toggle Plain Text) 
  1. <cfquery datasource="UKCHAPP145" name="Q1">
  2. SELECT	
  3. 	SUM(TBL_RESULTS.FTBR_UNIT_QTY) AS TotalFailedQty,
  4.     TBL_RESULTS.FTBR_OPERATION_GROUPING_UUID,
  5.     TBL_RESULTS.FTBR_OPERATION_UUID,
  6.     TBL_OPERATION_GROUPING.OPERATION_GROUP_NAME,
  7.     TBL_OPERATION_GROUPING.OPERATION_GROUP_SORT_ORDER
  8. FROM
  9. 	UKCHAPP145.TBL_RESULTS,
  10.     UKCHAPP145.TBL_OPERATION_GROUPING
  11. WHERE
  12. 	TBL_RESULTS.FTBR_PASS_OR_FAIL = <cfqueryparam value="0" cfsqltype="cf_sql_integer" >
  13. AND
  14.     TBL_RESULTS.FTBR_OPERATION_GROUPING_UUID = TBL_OPERATION_GROUPING.OPERATION_GROUP_UUID(+)
  15. AND
  16. 	TBL_RESULTS.FTBR_DATE BETWEEN <cfqueryparam value="#FORM.START_DATE#" cfsqltype="cf_sql_date">
  17. AND
  18. 	<cfqueryparam value="#FORM.END_DATE#" cfsqltype="cf_sql_date">
  19. GROUP BY
  20.     TBL_RESULTS.FTBR_OPERATION_GROUPING_UUID,
  21.     TBL_RESULTS.FTBR_OPERATION_UUID,
  22.     TBL_OPERATION_GROUPING.OPERATION_GROUP_NAME,
  23.     TBL_OPERATION_GROUPING.OPERATION_GROUP_SORT_ORDER
  24. </cfquery>	
  25.  
  26.  
  27.  
  28. <cfquery datasource="UKCHAPP145" name="Q2">
  29. SELECT	
  30. 	SUM(FTBR_UNIT_QTY) AS TotalPassedQty,
  31.     TBL_RESULTS.FTBR_OPERATION_GROUPING_UUID,
  32.     TBL_RESULTS.FTBR_OPERATION_UUID
  33. FROM
  34. 	TBL_RESULTS
  35. WHERE
  36. 	TBL_RESULTS.FTBR_HEADER_UUID = <cfqueryparam value="#FORM.HEADER_UUID#" cfsqltype="cf_sql_varchar">
  37. AND
  38. 	TBL_RESULTS.FTBR_PASS_OR_FAIL = <cfqueryparam value="1" cfsqltype="cf_sql_integer" >
  39. AND
  40. 	TBL_RESULTS.FTBR_DATE BETWEEN <cfqueryparam value="#FORM.START_DATE#" cfsqltype="cf_sql_date">
  41. AND
  42. 	<cfqueryparam value="#FORM.END_DATE#" cfsqltype="cf_sql_date">
  43. GROUP BY
  44.     TBL_RESULTS.FTBR_OPERATION_GROUPING_UUID,
  45.     TBL_RESULTS.FTBR_OPERATION_UUID
  46. </cfquery>	
  47.  
  48.  
  49.  
  50. <cfquery dbtype="query" name="Q3">
  51. SELECT
  52. 	SUM(100*(1-(Q1.TOTALFAILEDQTY /(Q2.TOTALPASSEDQTY + Q1.TOTALFAILEDQTY)))) AS QualityRate,  
  53.     Q1.FTBR_OPERATION_GROUPING_UUID,
  54.     Q1.FTBR_OPERATION_UUID,
  55.     Q1.OPERATION_GROUP_NAME
  56. FROM
  57. 	Q1,
  58.     Q2
  59. WHERE
  60. 	Q1.FTBR_OPERATION_UUID = Q2.FTBR_OPERATION_UUID
  61. GROUP BY
  62.     Q1.FTBR_OPERATION_UUID,
  63.     Q1.FTBR_OPERATION_GROUPING_UUID,
  64.     Q1.OPERATION_GROUP_NAME
  65. ORDER BY
  66. 	Q1.OPERATION_GROUP_SORT_ORDER 
  67. ASC
  68. </cfquery>
  69.  
  70.  
  71.  
  72. <cfquery dbtype="query" name="Q4">
  73. SELECT
  74.     Q1.FTBR_OPERATION_GROUPING_UUID,
  75.     Q1.OPERATION_GROUP_NAME
  76. FROM
  77. 	Q1,
  78.     Q2
  79. WHERE
  80. 	Q1.FTBR_OPERATION_GROUPING_UUID = Q2.FTBR_OPERATION_GROUPING_UUID
  81. GROUP BY
  82.     Q1.FTBR_OPERATION_GROUPING_UUID,
  83.     Q1.OPERATION_GROUP_NAME
  84. ORDER BY
  85. 	Q1.OPERATION_GROUP_SORT_ORDER 
  86. ASC
  87. </cfquery>
  88.  
  89.  
  90.  
  91.  
  92. <cfset strXML = strXML & "<categories> " >
  93.    <cfloop query="Q4"> 
  94.       <cfset strXML = strXML & "<category label='#Q4.OPERATION_GROUP_NAME#'/>" >
  95.    </cfloop>
  96. <cfset strXML = strXML & "</categories> " > 
  97.  
  98. <cfoutput group="FTBR_OPERATION_GROUPING_UUID" query="Q3"> 
  99.       <cfset strXML = strXML & "<dataset seriesName='#Q3.OPERATION_GROUP_NAME#' showValues='0'>" >
  100.  
  101. <cfset CurrentRowStart =  #currentrow# -1> 
  102. <cfset CurrentRowContinue =  #currentrow# +1>
  103.  
  104. 	<cfloop from="1" to="#CurrentRowStart#" index="i">
  105.       	<cfset strXML = strXML & "<set value=''/> " >
  106.     </cfloop>   
  107.  
  108.       	<cfset strXML = strXML & "<set value='#Round(Q3.QUALITYRATE)#'/>" >
  109.  
  110. 	<cfloop from="#CurrentRowContinue#" to="#Q3.recordcount#" index="i">
  111.       	<cfset strXML = strXML & "<set value=''/> " >
  112.     </cfloop> 
  113.  
  114. 	  <cfset strXML = strXML & "</dataset>" >  		
  115. </cfoutput>
  116.  
  117.  
  118.    <!--- Finally, close <chart>
  119.  element --->
  120.    <cfset strXML = strXML & "</chart> " >


JM
Last edited by jedimatt; Jan 28th, 2009 at 4:32 am. Reason: Spelling typos & added extra code
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 45
Reputation: arrgh is an unknown quantity at this point 
Solved Threads: 6
arrgh arrgh is offline Offline
Light Poster

Re: query cfoutput help

 
0
  #4
Jan 28th, 2009
My first thought was to modify the query to return the needed results. Then it would be easy to construct the xml using cfoutput's "group". But I do not know how simple it would be to modify your queries. It looks you are using oracle(?), which I am not as familiar with.

I was thinking about an outer or full outer join with a small lookup table (physical or derived)? The lookup table would contain all of the necessary "value" positions: (ie 1, 2, 3 ) The outer join would then return NULL's for the empty positions.

ie Instead of returning:

Operation1 100 1 1
Operation2 350 2 2
...

The query would return something like
--------------------------------------
Operation1, 100, 1, 1, 1 (value position 1)
Operation1, NULL, 1, 1, 2 (value position 2)
Operation1, NULL, 1, 1, 3 (value position 2)

Operation2, NULL, 2, 2, 1 (value position 1)
Operation2, 350, 2, 2, 2 (value position 2)
Operation2, NULL, 2, 2, 3 (value position 2)

That may or may not work here. But just a thought....
Last edited by arrgh; Jan 28th, 2009 at 3:36 pm. Reason: Changed Operation1 to Operation2
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 20
Reputation: jedimatt is an unknown quantity at this point 
Solved Threads: 1
jedimatt jedimatt is offline Offline
Newbie Poster

Re: query cfoutput help

 
0
  #5
Jan 28th, 2009
Hi arrgh,

Ok i am liking the idea, just struggling on how to impliment.

You say a lookup table which holds the positions. can you expand a little more on that approach please?

I understand the full outer join so it will output rows that have nulls in. Just need to understand how the lookup table will get populated.
I am guessing when each result line gets added into the result table it also needs to add a line into the lookup table as well, right?

JM
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 45
Reputation: arrgh is an unknown quantity at this point 
Solved Threads: 6
arrgh arrgh is offline Offline
Light Poster

Re: query cfoutput help

 
0
  #6
Jan 29th, 2009
•
•
•
•
Originally Posted by jedimatt View Post
Hi arrgh,
I am guessing when each result line gets added into the result table it also needs to add a line into the lookup table as well, right?
That depends on whether the number of "values" is fixed or not. Maybe this is too simple, but the way I see it, the "value" position is linked to the group order.

Group1
<set value=100> <!--- value position 1 --->
<set value="">
<set value="">


Group2
<set value="">
<set value="350"> <!--- value position 2 --->
<set value="">


So if you had a look-up table with all of the needed value positions (ie In this case 3), you could do an outer or cross join to it to generate all of the necessary rows. For example, say your group table contained 3 groups:

GroupName, GroupOrder
Group1,1
Group2,2
Group3,3

And you had a "valuePosition" table with all of the positions (ie 3)

ValuePosition
1
2
3

You could do an outer or cross join to produce return all positions for each group.

GroupName, GroupOrder, ValuePosition
Group1, 1, 1
Group1, 1, 2
Group1, 1, 3

Group1, 2, 1
Group1, 2, 2
Group1, 2, 3

Group1, 3, 1
Group1, 3, 2
Group1, 3, 3

....

Then from there use a CASE to return a value of "" or 0 if GroupOrder does not match the ValuePosition

CASE WHEN GroupOrder <> ValuePosition THEN 0 ELSE (Return TotalFailedQty) END ...

That may not be exactly how you would do the join, but hopefully that illustrates the idea a bit better.
Last edited by arrgh; Jan 29th, 2009 at 10:48 am.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 20
Reputation: jedimatt is an unknown quantity at this point 
Solved Threads: 1
jedimatt jedimatt is offline Offline
Newbie Poster

Re: query cfoutput help

 
0
  #7
Jan 30th, 2009
Hi arrgh

Yes that looks like it will work.

The number of values is dynamic but i think i can take care of that.

I will try this out and let you know how it goes.

Many thanks for your help on this.

cheers

JM
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 20
Reputation: jedimatt is an unknown quantity at this point 
Solved Threads: 1
jedimatt jedimatt is offline Offline
Newbie Poster

Re: query cfoutput help

 
0
  #8
Feb 3rd, 2009
Hi arrgh,

I have come across a little problem.

I use query of quries to get the desired output, what i am struglling right now is qofq does not support outer joins.

Any ideas on how i can work around this?

JM
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 45
Reputation: arrgh is an unknown quantity at this point 
Solved Threads: 6
arrgh arrgh is offline Offline
Light Poster

Re: query cfoutput help

 
0
  #9
Feb 3rd, 2009
•
•
•
•
Originally Posted by jedimatt View Post
Hi arrgh,
I use query of quries to get the desired output, what i am struglling right now is qofq does not support outer joins.
There are hacks, but it is a bit of a pain.
http://instantbadger.blogspot.com/20...-query-of.html

I was thinking more along the lines of doing the outer/cross join in your original queries. Then you could still do inner joins in your QoQ. Though you would probably have to join on the new "Value Position" column as well.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the ColdFusion Forum
Thread Tools Search this Thread



adobe adobelivecyclees alfresco amazon cfquery checkbox coldfusion contentmanagement dreamweaver ebooks insert kindle multiple opensource openstandards pdf select sony sonyreader
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC