Databases
StackOverflow2013
Postsdbo
PORailo Query of Query returning incorrect results
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORailo Query of Query returning incorrect results
    primarykey
    data
    text
    <p>I'm running the following two statements:</p> <p>First is A) which does what it needs to do and works:</p> <pre><code>SELECT itemColumn ,valueColumn ,label FROM rstCombinedChartData UNION ALL SELECT itemColumn ,CAST(valueColumn AS INTEGER) AS valueColumn ,label FROM rstChartData </code></pre> <p>This gives me the following results:</p> <p><img src="https://i.stack.imgur.com/2lqGn.png" alt="enter image description here"></p> <p>Next I need to take these results and get back total of itemcolumn for each value in this case yes and no i.e. </p> <pre><code>i.e. Yes 200 No 400 </code></pre> <p>B) This is the query I have for to achieve this:</p> <pre><code>SELECT itemColumn ,SUM(valueColumn) AS valueColumn ,label FROM rstCombinedChartData (this is above result set) GROUP BY label ,itemColumn ORDER BY label DESC ,itemColumn DESC </code></pre> <p>However, I get the following result which is not correct:</p> <p><img src="https://i.stack.imgur.com/NI0p8.png" alt="enter image description here"></p> <p>Whats going on here with query B it should be yes = x and no = x, instead I'm getting false and all the totals?</p> <p>CF function:</p> <pre><code> &lt;cffunction name="getAverageChartData" hint="I return the data required to render an average chart." returntype="array" output="false"&gt; &lt;cfargument name="surveyList" hint="I am a record set of Surveys." required="true" type="query" /&gt; &lt;cfargument name="filter" hint="I am the optional filter which is to be applied to all results." required="false" default="" type="string" /&gt; &lt;cfset var local=structNew() /&gt; &lt;cfset var rstChartData="" /&gt; &lt;cfset var rstChartDataTotal="" /&gt; &lt;cfset var rstCombinedChartData=queryNew("itemColumn,valueColumn,label","varchar,integer,varchar") /&gt; &lt;cfset local.objQuestion=objQuestionService.get(arguments.surveyList.question_ID[1]) /&gt; &lt;cfset local.intQuestionTypeID = local.objQuestion.getTypeID() /&gt; &lt;cfset local.strSubQuestionList=local.objQuestion.getAnswer() /&gt; &lt;cfset local.strPossibleAnswerList=local.objQuestion.getPossibleAnswer() /&gt; &lt;cfset local.arrChartDataResult=arrayNew(1) /&gt; &lt;!--- loop over each school's survey ---&gt; &lt;cfloop query="arguments.surveyList"&gt; &lt;cfset local.arrChartData = getChartData(arguments.surveyList.survey_id, arguments.surveyList.question_id, arguments.filter) /&gt; &lt;!--- loop over each sub question and append (union) it to a running total ---&gt; &lt;cfloop array="#local.arrChartData#" index="rstChartData"&gt; &lt;cfquery name="rstCombinedChartData" dbtype="query"&gt; SELECT itemColumn ,valueColumn ,label FROM rstCombinedChartData UNION ALL SELECT itemColumn ,CAST(valueColumn AS INTEGER) AS valueColumn ,label FROM rstChartData &lt;/cfquery&gt; &lt;/cfloop&gt; &lt;/cfloop&gt; &lt;!--- get the totals for each itemColumn ---&gt; &lt;cfquery name="rstChartDataTotal" dbtype="query"&gt; SELECT itemColumn ,SUM(valueColumn) AS valueColumn ,label FROM rstCombinedChartData GROUP BY label ,itemColumn ORDER BY label DESC ,itemColumn DESC &lt;/cfquery&gt; </code></pre> <p><strong>OK - Major UPDATE</strong></p> <p>I have no idea why but I added this line in by mistake:</p> <pre><code>&lt;cfset querySetCell(rstCombinedChartData, "itemColumn", "1") /&gt; </code></pre> <p>All of a sudden the query started to work in Railo! Ok now I have one extra result that makes no sense "1" but WTF! It seems that Railo does not like all yes, no answers I put something else into the mix and it starts to tread it correctly again as varchars like it should.</p> <p>anyone know whats going on here? I have tired casting in sql as varchar but this does not work only at the CF level does something happen.</p> <p><img src="https://i.stack.imgur.com/pMg6A.png" alt="enter image description here"></p> <p>If i take out the line: </p> <pre><code>&lt;cfset querySetCell(rstCombinedChartData, "itemColumn", "1") /&gt; </code></pre> <p>it goes back to:</p> <p><img src="https://i.stack.imgur.com/OJ7K3.png" alt="enter image description here"></p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJames A Mohler
    1. USGrantU
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORailo Query of Query returning incorrect results
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PORailo Query of Query returning incorrect results
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PORailo Query of Query returning incorrect results
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis does not appear to be a MySQL question, as the query you're working on is a query-on-query, not a DB query. Can you do me a favour? JSONify your recordset `rstCombinedChartData` and post it here? I'll have a play around. Might pay to de-tag your question of "SQL" and "MySQL" as CFML's dialect of SQL is rather different/limited compared to standard SQL dialects.
      singulars
      1. PORailo Query of Query returning incorrect results
      1. USAdam Cameron
    2. COJust a shot in the dark, but try casting your itemColumn as varchar in your q of q.
      singulars
      1. PORailo Query of Query returning incorrect results
      1. USDan Bracuk
    3. CO@Adam Cameron only thing I have noticed it that it works if the itemColumn is 'boy' 'girl'. but the values 'yes' 'no' do something strange to the query.
      singulars
      1. PORailo Query of Query returning incorrect results
      1. USGrantU
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload