query cfoutput help

Question

jedimatt 0 Newbie Poster

15 Years Ago

Hello,

How can i produce the following output from a query:

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

coldfusion

2 Contributors
8 Replies
193 Views
1 Week Discussion Span
Latest Post 15 Years Ago Latest Post by arrgh

arrgh 22 Posting Whiz

15 Years Ago

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

arrgh 22 Posting Whiz

15 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jedimatt 0 Newbie Poster · Answer 1 · 2009-01-28T14:18:31+00:00

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)

Group1
<set value=100>
<set value="">
<set value="">


Group2
<set value="">
<set value="350">
<set value="">



Group3
<set value="">
<set value="">
<set value="50">


Group3
<set value="">
<set value="">
<set value="120">


Group3
<set value="">
<set value="">
<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:

<cfquery datasource="UKCHAPP145" name="Q1">
SELECT	
	SUM(TBL_RESULTS.FTBR_UNIT_QTY) AS TotalFailedQty,
    TBL_RESULTS.FTBR_OPERATION_GROUPING_UUID,
    TBL_RESULTS.FTBR_OPERATION_UUID,
    TBL_OPERATION_GROUPING.OPERATION_GROUP_NAME,
    TBL_OPERATION_GROUPING.OPERATION_GROUP_SORT_ORDER
FROM
	UKCHAPP145.TBL_RESULTS,
    UKCHAPP145.TBL_OPERATION_GROUPING
WHERE
	TBL_RESULTS.FTBR_PASS_OR_FAIL = <cfqueryparam value="0" cfsqltype="cf_sql_integer" >
AND
    TBL_RESULTS.FTBR_OPERATION_GROUPING_UUID = TBL_OPERATION_GROUPING.OPERATION_GROUP_UUID(+)
AND
	TBL_RESULTS.FTBR_DATE BETWEEN <cfqueryparam value="#FORM.START_DATE#" cfsqltype="cf_sql_date">
AND
	<cfqueryparam value="#FORM.END_DATE#" cfsqltype="cf_sql_date">
GROUP BY
    TBL_RESULTS.FTBR_OPERATION_GROUPING_UUID,
    TBL_RESULTS.FTBR_OPERATION_UUID,
    TBL_OPERATION_GROUPING.OPERATION_GROUP_NAME,
    TBL_OPERATION_GROUPING.OPERATION_GROUP_SORT_ORDER
</cfquery>	



<cfquery datasource="UKCHAPP145" name="Q2">
SELECT	
	SUM(FTBR_UNIT_QTY) AS TotalPassedQty,
    TBL_RESULTS.FTBR_OPERATION_GROUPING_UUID,
    TBL_RESULTS.FTBR_OPERATION_UUID
FROM
	TBL_RESULTS
WHERE
	TBL_RESULTS.FTBR_HEADER_UUID = <cfqueryparam value="#FORM.HEADER_UUID#" cfsqltype="cf_sql_varchar">
AND
	TBL_RESULTS.FTBR_PASS_OR_FAIL = <cfqueryparam value="1" cfsqltype="cf_sql_integer" >
AND
	TBL_RESULTS.FTBR_DATE BETWEEN <cfqueryparam value="#FORM.START_DATE#" cfsqltype="cf_sql_date">
AND
	<cfqueryparam value="#FORM.END_DATE#" cfsqltype="cf_sql_date">
GROUP BY
    TBL_RESULTS.FTBR_OPERATION_GROUPING_UUID,
    TBL_RESULTS.FTBR_OPERATION_UUID
</cfquery>	



<cfquery dbtype="query" name="Q3">
SELECT
	SUM(100*(1-(Q1.TOTALFAILEDQTY /(Q2.TOTALPASSEDQTY + Q1.TOTALFAILEDQTY)))) AS QualityRate,  
    Q1.FTBR_OPERATION_GROUPING_UUID,
    Q1.FTBR_OPERATION_UUID,
    Q1.OPERATION_GROUP_NAME
FROM
	Q1,
    Q2
WHERE
	Q1.FTBR_OPERATION_UUID = Q2.FTBR_OPERATION_UUID
GROUP BY
    Q1.FTBR_OPERATION_UUID,
    Q1.FTBR_OPERATION_GROUPING_UUID,
    Q1.OPERATION_GROUP_NAME
ORDER BY
	Q1.OPERATION_GROUP_SORT_ORDER 
ASC
</cfquery>



<cfquery dbtype="query" name="Q4">
SELECT
    Q1.FTBR_OPERATION_GROUPING_UUID,
    Q1.OPERATION_GROUP_NAME
FROM
	Q1,
    Q2
WHERE
	Q1.FTBR_OPERATION_GROUPING_UUID = Q2.FTBR_OPERATION_GROUPING_UUID
GROUP BY
    Q1.FTBR_OPERATION_GROUPING_UUID,
    Q1.OPERATION_GROUP_NAME
ORDER BY
	Q1.OPERATION_GROUP_SORT_ORDER 
ASC
</cfquery>




<cfset strXML = strXML & "<categories>" >
   <cfloop query="Q4">
      <cfset strXML = strXML & "<category label='#Q4.OPERATION_GROUP_NAME#'/>" >
   </cfloop>
<cfset strXML = strXML & "</categories>" > 
   
<cfoutput group="FTBR_OPERATION_GROUPING_UUID" query="Q3">
      <cfset strXML = strXML & "<dataset seriesName='#Q3.OPERATION_GROUP_NAME#' showValues='0'>" >
      
<cfset CurrentRowStart =  #currentrow# -1> 
<cfset CurrentRowContinue =  #currentrow# +1>
    
	<cfloop from="1" to="#CurrentRowStart#" index="i">
      	<cfset strXML = strXML & "<set value=''/>" >
    </cfloop>  

      	<cfset strXML = strXML & "<set value='#Round(Q3.QUALITYRATE)#'/>" >

	<cfloop from="#CurrentRowContinue#" to="#Q3.recordcount#" index="i">
      	<cfset strXML = strXML & "<set value=''/>" >
    </cfloop>

	  <cfset strXML = strXML & "</dataset>" >  		
</cfoutput>


   <!--- Finally, close <chart> element --->
   <cfset strXML = strXML & "</chart>" >

JM

jedimatt 0 Newbie Poster · Answer 2 · 2009-01-29T04:29:52+00:00

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

arrgh 22 Posting Whiz · Answer 3 · 2009-01-29T20:47:40+00:00

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> 
<set value="">
<set value="">

Group2
<set value="">
<set value="350"> 
<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.

jedimatt 0 Newbie Poster · Answer 4 · 2009-01-30T21:28:43+00:00

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

jedimatt 0 Newbie Poster · Answer 5 · 2009-02-03T16:26:50+00:00

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

arrgh 22 Posting Whiz · Answer 6 · 2009-02-04T03:13:35+00:00

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/2006/07/faking-left-outer-join-in-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.