SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    data from 2 different queries!

    I am trying to get data from 2 different queries.
    But in de code below is there something wrong. I can't see it.
    Is there anyone who can help me?

    ----------------------------------

    <!--- Get data --->
    <cfinvoke component="wvt_vmw_views" method="getMeasDefInfo" returnvariable="qryInfo1">
    <cfinvokeargument name="measdef_id" value="#measdef_id_1#">
    </cfinvoke>

    <!--- <cfdump var="#qryInfo1#"> --->

    <cfinvoke component="wvt_vmw_views" method="getHisDayData" returnvariable="qryData1">
    </cfinvoke>

    <!--- <cfdump var="#qryData1#"> --->

    <!--- Calculations --->

    <cfquery name="qrydatinfo" dbtype="query">

    select qrydata1.measdef_id, qryInfo1.measdef_id
    , count(qrydata1.meas_value) as dacount
    , sum(qrydata1.meas_value) as dasum
    , min(qrydata1.meas_value) as damin
    , max(qrydata1.meas_value) as damax
    , avg(qrydata1.meas_value) as daavg

    from qryInfo1,qryData1

    where qryInfo1.measdef_id = qryData1.measdef_id
    and qryinfo1.measdef_desc_short = verb
    and qrydata1.measdef_id > 300500000

    group
    by qrydata1.measdef_id

    order
    by 1

    </cfquery>


    -----------------------

    With kindly regards,

    Maarten.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    what exactly is the problem?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    more explination

    This is the cfc where the code is to get info from the database.
    <cfcomponent>

    <cffunction name="getHisDayData" returntype="query" access="remote">
    <cfargument name="measdef_id" type="numeric" required="no">
    <cfargument name="tsStartDate" type="date" required="no">
    <cfargument name="tsEndDate" type="date" required="no">

    <cfscript>
    var qryData="";
    var whereClause="";

    </cfscript>

    <!--- <cfinvoke component="ws.rights" method="isValidUser" returnvariable="isValid">
    <cfinvokeargument name="measdef_id" value="#measdef_id#">
    </cfinvoke> --->

    <cftransaction>
    <cfquery name="qryData" datasource="mevisco1" timeout="10">
    SELECT *
    FROM HIS_DAY
    </cfquery>
    </cftransaction>

    <cfdump var="#qryData#">

    <cfreturn qryData>
    </cffunction>



    <!--- ------------------------------------------ --->

    <cffunction name="getMeasDefInfo" returntype="struct" access="remote">
    <cfargument name="measdef_id" type="numeric" required="no">

    <cfset stMeasDefInfo=StructNew()>

    <cftransaction>
    <cfquery name="qryMeasDefInfoSiteInfo" datasource="mevisco1" timeout="10">
    SELECT s.site_long_desc AS siteName
    FROM SITE s
    WHERE s.site_id=(
    SELECT ca.site_id
    FROM COUPLERA ca
    WHERE ca.measdef_id=#arguments.measdef_id#)
    </cfquery>
    </cftransaction>

    <cfset stMeasDefInfo.siteName=qryMeasDefInfoSiteInfo.siteName>

    <cftransaction>
    <cfquery name="qryMeasDefInfo" datasource="mevisco1" timeout="10">
    SELECT measdef_desc_long AS measName, measdef_unit AS measUnit, m.measdef_id
    FROM MEASURINGDEF m
    WHERE m.measdef_id=#arguments.measdef_id#
    </cfquery>
    </cftransaction>

    <cfdump var="#qryMeasDefInfo#">

    <cfset stMeasDefInfo.measName=qryMeasDefInfo.measName>
    <cfset stMeasDefInfo.measUnit=qryMeasDefInfo.measUnit>

    <cfreturn stMeasDefInfo>
    </cffunction>



    </cfcomponent>


    Then this is de code from the cfm:

    <!--- <cfheader name="Content-Disposition" value="INLINE;FILENAME=wvt_vmw_md_1_01.doc"> --->
    <cfparam name="SomeValue" default="">
    <cfparam name="URL.tsStartDateM" default="2005-11-07">
    <cfparam name="URL.tsEndDateM" default="2005-11-11">
    <cfparam name="URL.locale" default="Dutch (Belgian)">

    <cfset meternr_1 = "123456789">
    <cfset measdef_id_1 = 300502001>
    <cfset measdef_id_2 = "measdef_id">

    <!--- Get data --->
    <cfinvoke component="wvt_vmw_views" method="getMeasDefInfo" returnvariable="qryInfo1">
    <cfinvokeargument name="measdef_id" value="#measdef_id_1#">
    </cfinvoke>

    <!--- <cfdump var="#qryInfo1#"> --->

    <cfinvoke component="wvt_vmw_views" method="getHisDayData" returnvariable="qryData1">
    </cfinvoke>

    <!--- <cfdump var="#qryData1#"> --->

    <!--- Calculations --->

    <cfquery name="qrydatinfo" dbtype="query">

    select qrydata1.measdef_id<!--- , qryInfo1.measdef_id --->
    , count(qrydata1.meas_value) as dacount
    , sum(qrydata1.meas_value) as dasum
    , min(qrydata1.meas_value) as damin
    , max(qrydata1.meas_value) as damax
    , avg(qrydata1.meas_value) as daavg

    from qryData1,qryInfo1

    where qryInfo1.measdef_id = qryData1.measdef_id
    and qryinfo1.measdef_desc_short = verb
    and qrydata1.measdef_id > 300500000

    group
    by qrydata1.measdef_id

    order
    by 1

    </cfquery>


    <table width="502" border="1" cellpadding="5" cellspacing="0">
    <tr>
    <th width="100">Omschrijving</th>
    <th width="83">SUM [m&sup3;/maand]</th>
    <th width="80">AVG [m&sup3;/dag]</th>
    <th width="84">MAX [m&sup3;/dag]</th>
    <th width="81">MIN [m&sup3;/dag]</th>
    </tr>
    </table>

    <CFOUTPUT QUERY="qrydatinfo">
    <table width="502" border="1" cellpadding="5" cellspacing="0">
    <tr>
    <td width="100"><div align="center">#measdef_id#</div></td>
    <td width="83"><div align="center">#daSum#</div></td>
    <td width="80"><div align="center">#daAvg#</div></td>
    <td width="84"><div align="center">#daMax#</div></td>
    <td width="81"><div align="center">#daMin#</div></td>
    </tr>
    </table>
    </CFOUTPUT>



    When you try this code without the part

    ,qryInfo1

    where qryInfo1.measdef_id = qryData1.measdef_id
    and qryinfo1.measdef_desc_short = verb
    and qrydata1.measdef_id > 300500000



    You get the followming result.

    Omschrijving SUM [m/maand] AVG [m/dag] MAX [m/dag] MIN [m/dag]
    300003001 337 337 337 337
    300003002 10 10 10 10
    300500001 659 329 359 300
    300500002 14 7 10 4
    300501001 642 321 341 301
    300501002 24 12 15 9
    300502001 655 327 346 309
    300502002 38 19 25 13
    300503001 400 400 400 400
    300503002 20 20 20 20
    300504001 675 337 373 302
    300504002 29 14 15 14
    300505001 358 358 358 358
    300505002 2 2 2 2


    Now we are only need the the values where a 1 is in the "omschrijving" (300500001, 300501001, etc..
    So the other values we don't need.

    And now comes the question, How can we do this?


    Hopely you can help me, because i am a totaly amateur.

    GRTZ,

    Maarten.

  4. #4
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Oplossing gevonden

    Dit is de cfc.

    <cfcomponent>

    <cffunction name="getHisDayData" returntype="query" access="remote">
    <cfargument name="measdef_id" type="numeric" required="no">
    <cfargument name="tsStartDate" type="date" required="no">
    <cfargument name="tsEndDate" type="date" required="no">

    <cfscript>
    var qryData="";
    var whereClause="";
    </cfscript>

    <cftransaction>
    <cfquery name="qryData" datasource="mevisco1" timeout="10">
    SELECT MEASDEF_DESC_SHORT, his_day.measdef_id, MEAS_VALUE
    FROM HIS_DAY, MEASURINGDEF
    Where his_day.measdef_id=MEASURINGDEF.measdef_id
    </cfquery>
    </cftransaction>

    <!--- <cfdump var="#qryData#"> --->

    <cfreturn qryData>
    </cffunction>
    </cfcomponent>



    Dit is de cfm.

    <!--- <cfheader name="Content-Disposition" value="INLINE;FILENAME=wvt_vmw_md_1_01.doc"> --->
    <cfparam name="SomeValue" default="">
    <cfparam name="URL.tsStartDateM" default="2005-11-07">
    <cfparam name="URL.tsEndDateM" default="2005-11-11">
    <cfparam name="URL.locale" default="Dutch (Belgian)">

    <cfset meternr_1 = "123456789">



    <!--- Get data --->

    <cfinvoke component="wvt_vmw_views" method="getHisDayData" returnvariable="qryData1">
    </cfinvoke>

    <!--- <cfdump var="#qryData1#"> --->

    <!--- Calculations --->

    <cfquery name="qryData1" dbtype="query">

    select qrydata1.measdef_id
    , count(qrydata1.meas_value) as dacount
    , sum(qrydata1.meas_value) as dasum
    , min(qrydata1.meas_value) as damin
    , max(qrydata1.meas_value) as damax
    , avg(qrydata1.meas_value) as daavg

    from qryData1

    where qrydata1.measdef_id > 300500000
    and qrydata1.MEASDEF_DESC_SHORT = 'temp'
    group
    by qrydata1.measdef_id

    order
    by 1

    </cfquery>


    <table width="502" border="1" cellpadding="5" cellspacing="0">
    <tr>
    <th width="100">Omschrijving</th>
    <th width="83">SUM [m&sup3;/maand]</th>
    <th width="80">AVG [m&sup3;/dag]</th>
    <th width="84">MAX [m&sup3;/dag]</th>
    <th width="81">MIN [m&sup3;/dag]</th>
    </tr>
    </table>
    <!--- <cfcontent type="application/excel"> --->
    <CFOUTPUT QUERY="qryData1">
    <table width="502" border="1" cellpadding="5" cellspacing="0">
    <tr>
    <td width="100"><div align="center">#qrydata1.measdef_id#</div></td>
    <td width="83"><div align="center">#daSum#</div></td>
    <td width="80"><div align="center">#daAvg#</div></td>
    <td width="84"><div align="center">#daMax#</div></td>
    <td width="81"><div align="center">#daMin#</div></td>
    </tr>
    </table>
    </CFOUTPUT>



    Thanks to those who helpt us.


    Regards,

    Maarten.

  5. #5
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ... again you saved the day r, with but a simple phrase.
    your brain reacts in the same way whether you are
    looking at something or thinking about it...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •