943,644 Members | Top Members by Rank

Web Development > ColdFusion > query cfoutput help
Ad:
  • ColdFusion Discussion Thread
  • Unsolved
  • Views: 3126
  • ColdFusion RSS
Permalink
Jan 26th, 2009
0

query cfoutput help

Expand Post »
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
Similar Threads
Reputation Points: 10
Solved Threads: 1
Newbie Poster
jedimatt is offline Offline
20 posts
since Jan 2009
Permalink
Jan 28th, 2009
0

Re: query cfoutput help

Click to Expand / Collapse  Quote originally posted by jedimatt ...
<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?
Reputation Points: 32
Solved Threads: 44
Posting Whiz
arrgh is offline Offline
348 posts
since Dec 2008
Permalink
Jan 28th, 2009
0

Re: query cfoutput help

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
Reputation Points: 10
Solved Threads: 1
Newbie Poster
jedimatt is offline Offline
20 posts
since Jan 2009
Permalink
Jan 28th, 2009
0

Re: query cfoutput help

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
Reputation Points: 32
Solved Threads: 44
Posting Whiz
arrgh is offline Offline
348 posts
since Dec 2008
Permalink
Jan 28th, 2009
0

Re: query cfoutput help

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
Reputation Points: 10
Solved Threads: 1
Newbie Poster
jedimatt is offline Offline
20 posts
since Jan 2009
Permalink
Jan 29th, 2009
0

Re: query cfoutput help

Click to Expand / Collapse  Quote originally posted by jedimatt ...
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.
Reputation Points: 32
Solved Threads: 44
Posting Whiz
arrgh is offline Offline
348 posts
since Dec 2008
Permalink
Jan 30th, 2009
0

Re: query cfoutput help

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
Reputation Points: 10
Solved Threads: 1
Newbie Poster
jedimatt is offline Offline
20 posts
since Jan 2009
Permalink
Feb 3rd, 2009
0

Re: query cfoutput help

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
Reputation Points: 10
Solved Threads: 1
Newbie Poster
jedimatt is offline Offline
20 posts
since Jan 2009
Permalink
Feb 3rd, 2009
0

Re: query cfoutput help

Click to Expand / Collapse  Quote originally posted by jedimatt ...
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.
Reputation Points: 32
Solved Threads: 44
Posting Whiz
arrgh is offline Offline
348 posts
since Dec 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in ColdFusion Forum Timeline: a help in my url
Next Thread in ColdFusion Forum Timeline: Problem with DNS when switching my host





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC