2 Replies Latest reply on Apr 27, 2006 2:50 AM by jypresley

    Query and Loop

    YogeshM Level 1
      Hi,

      Let's say I have a query e.g. getDetails which returns the following

      Instrument Avg. Buy Avg. Sell
      A 1 0
      B 1 1
      C 0 1

      Now I have a query object as follows:
      <cfset myQry = QueryNew("instrument, avg_buy, avg_sell")>

      Normally, to loop through getDetails to populate myQry, it's fairly simple. All that is needed is:

      <cfloop query="getDetails">
      <cfset numrow = QueryAddRow(myQry,1)>
      <cfset tmp = QuerySetCell(myQry,"instrument",#getDetails.instrument#)>
      <cfset tmp = QuerySetCell(myQry,"avg_buy",#getDetails.avg_buy#)>
      <cfset tmp = QuerySetCell(myQry,"avg_sell",#getDetails.avg_sell#)>
      </cfloop>

      i.e. for 3 records, I'll have 3 entries, which is quite logical.

      Now, if you look at the recordset above, you will notice that for instrument B, both the avg buy and the avg sell are neq 0.
      When I encounter such a condition, I need to split the row into 2 as follows:

      Instrument Avg. Buy Avg. Sell
      A 1 0
      B 1 0
      B 0 1
      C 0 1

      How can I modify the above code to achieve this please?

      Thanks and regards,
      Yogesh Mahadnac
        • 1. Re: Query and Loop
          boughtonp
          Like this:
          • 2. Re: Query and Loop
            jypresley
            hi yogesh,

            you can do it that way:

            <cfloop query="getDetails">
            <cfset numrow = QueryAddRow(myQry,1)>
            <cfset tmp = QuerySetCell(myQry,"instrument",#getDetails.instrument#)>
            <cfif getDetails.avg_buy eq "1" and getDetails.avg_sell eq "1">
            <cfset tmp = QuerySetCell(myQry,"avg_buy",1)>
            <cfset tmp = QuerySetCell(myQry,"avg_sell",0)>
            <cfset numrow = QueryAddRow(myQry,1)>
            <cfset tmp = QuerySetCell(myQry,"instrument",#getDetails.instrument#)>
            <cfset tmp = QuerySetCell(myQry,"avg_buy",0)>
            <cfset tmp = QuerySetCell(myQry,"avg_sell",1)>
            <cfelse>
            <cfset tmp = QuerySetCell(myQry,"avg_buy",#getDetails.avg_buy#)>
            <cfset tmp = QuerySetCell(myQry,"avg_sell",#getDetails.avg_sell#)>
            </cfif>
            </cfloop>

            rgds
            Presley