Skip to main content
T
trojnfn
Inspiring
February 10, 2009
Question

cfloop help

  • February 10, 2009
  • 2 replies
  • 1524 views
I posted this previously and still do not know how to do it, so I will post again with another example.

We have a table on sql server. The table contains an order number and one or more part numbers for that order. We added a new column called line item, currently set at NULL. What I need to do is update the line item for each part number, per order. For example, if order number 1 has 3 part numbers, then the line item should be 1, 2, and 3. If the next order 2 has 5 part numbers, then the line items should be 1,2,3,4,5. If the next order has 2 part number, then the line items should be 1,2...etc.

What I tried to do was count(order) as totalcount then group by order. This would give me the total line items for each order. I then tried to use cfloop from=1 to=totalcount index=i, and loop thru the update, with set lineItem = lineitem + 1 (line items are updated from NULL to 0), and where order = #qry.order#

I was hoping that the first time around, it would update the first order number up to the max, then switch and go to the next order number, etc. But what is happening is that if the order has 3 part numbers, it will update the line item of the first part number to 3, and the other 2 remain 0.

What is the proper cfloop setup to do what I need to do, or is there another better method to update the line items ?

Thanks for any help.
    This topic has been closed for replies.

    2 replies

    D
    dongzky
    Inspiring
    February 16, 2009
    I think you need to remove the GROUP BY in your select query since you are using the group parameter in the <cfoutput> tag. So your SELECT query will only be

    <cfquery name="qry" datasource="recDisc">
    select partNumber
    from test_gfmSerialNumbers_test
    order by partNumber
    </cfquery>

    <cfoutput query="qry" GROUP="partNumber">
    <cfset idx = 0>
    <cfoutput>
    <cfset idx = idx + 1>
    <cfquery name="qryUpdate" datasource="recDisc">
    UPDATE test_gfmSerialNumbers_test
    SET lineItem = <cfqueryparam value="#idx#" cfsqltype="cf_sql_numeric">
    WHERE partNumber = <cfqueryparam value="#qry.partNumber#" cfsqltype="cf_sql_numeric">
    AND lineItem = 0
    </cfquery>
    </cfoutput>
    </cfoutput>
    T
    trojnfnAuthor
    Inspiring
    February 16, 2009
    I tried your suggested queries and it still makes all the line items 1 for all parts, instead of incremental. Is there suppose to be a cfloop involved ?
    D
    dongzky
    Inspiring
    February 17, 2009
    quote:
    Originally posted by: trojnfn
    I tried your suggested queries and it still makes all the line items 1 for all parts, instead of incremental. Is there suppose to be a cfloop involved ?


    What is the Primary Key for your table test_gfmSerialNumbers_test? Yo should use it. Assuming your primary key is numeric then,

    <cfquery name="qry" datasource="recDisc">
    select partNumber, your_primary_key_field
    from test_gfmSerialNumbers_test
    order by partNumber, your_primary_key_field
    </cfquery>

    <cfoutput query="qry" GROUP="partNumber">
    <cfset idx = 0>
    <cfoutput>
    <cfset idx = idx + 1>
    <cfquery name="qryUpdate" datasource="recDisc">
    UPDATE test_gfmSerialNumbers_test
    SET lineItem = <cfqueryparam value="#idx#" cfsqltype="cf_sql_numeric">
    WHERE your_primary_key_field = <cfqueryparam value="#qry.your_primary_key_field#" cfsqltype="cf_sql_numeric">
    </cfquery>
    </cfoutput>
    </cfoutput>

    N
    Newsgroup_User
    Inspiring
    February 10, 2009
    proper cfloop setup will depend entirely on your query and other code:
    post it!!!

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    T
    trojnfnAuthor
    Inspiring
    February 10, 2009
    here is the code I am trying to use, does not work :

    <cfquery name="qry" datasource="recDisc">
    select PartNumber, lineItem,count(PartNumber) as total
    from table
    group by PartNumber, lineItem
    order by PartNumber
    </cfquery>

    <cfloop from="1" to="#qryTotal#" index="i">

    <cfquery name="qryUpdate" datasource="recDisc">
    update table
    set lineItem = ineItem + 1
    where PartNumber = '#qry.partNumber#'
    </cfquery>
    </cfloop>
    Firefly Community Gallery
    Adobe Firefly

    Remix with Firefly Community Gallery

    Thousands of free creations to fall in love with and remix in Firefly.

    Explore now
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices