cfloop help

[trojnfn]

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.

[Newsgroup_User]

proper cfloop setup will depend entirely on your query and other code:
post it!!!

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[trojnfn]

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>

[Newsgroup_User]

try this instead of your second query:

<cfoutput query="qry">
<cfloop from="1" to="#qry.total#" index="idx">
<cfquery datasource="...">
UPDATE table
SET lineItem = #idx#
WHERE PartNumber = '#qry.PartNumber#'
</cfquery>
</cfloop>
</cfoutput>

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[trojnfn]

I replaced by second query with your recommended query. Other than a time out issue (too many records but I can work around that), it updates the line items with the max amount. For example, if there are ten parts, all the line items are 10, instead of 1, 2, 3,4,5,6,7,8,910...that is what I need to happend, and when the part nubmer chnages,then the line items should start all over at 1,2, ....etc.

[Newsgroup_User]

silly me, of course it would update all to the max number! my bad, sorry.

try this one instead.
NOTE: Assumes all your lineItem in the db are currently NULL or no value:

<cfoutput query="qry">
<cfloop from="1" to="#qry.total#" index="idx">
<cfquery datasource="...">
UPDATE table
SET lineItem = #idx#
WHERE PartNumber = '#qry.PartNumber#' AND (lineItem IS NULL OR lineItem
= '')
</cfquery>
</cfloop>
</cfoutput>


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[trojnfn]

All the line items are initially set to 0, so I just changed the last part to lineItem = 0. Now it is updating all line items to 1 instead of incremental. So it still does not give me what I want.

[Newsgroup_User]

maybe your QRY query is the culprit...

change it to:

<cfquery name="qry" datasource="recDisc">
SELECT PartNumber, COUNT(lineItem) as total
FROM table
GROUP BY PartNumber
ORDER BY PartNumber
</cfquery>

and try again...

if still not updating the lineitems right - post you current code (both
main query and the looping)!




you can also try the cfoutput with GROUP attribute, too:

<cfquery name="qry" datasource="recDisc">
SELECT PartNumber, lineItem
FROM table
ORDER BY PartNumber, lineItem
</cfquery>

<cfoutput query="qry" GROUP="PartNumber">
<cfset idx = 0>
<cfoutput>
<cfset idx = idx + 1>
<cfquery datasource="...">
UPDATE table
SET lineItem = #idx#
WHERE PartNumber = '#qry.PartNumber#' AND lineItem = 0
</cfquery>
</cfoutput>
</cfoutput>

! notice the changed QRY query and the nested CFOUTPUT in the above code

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[trojnfn]

I changed the queries using your suggestions and it is still updating all the line items to 1. I queried the table and there are no line items greater than 1.

Here are the queries I am using :

<cfquery name="qry" datasource="recDisc">
select partNumber, count(lineItem) as total
from test_gfmSerialNumbers_test
group by partNumber
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 = #idx#
WHERE partNumber = '#qry.partNumber#' AND lineItem = 0
</cfquery>
</cfoutput>
</cfoutput>

[Newsgroup_User]

the "qry" query you have is wrong for that type of cfoutput - check the
code in my post carefully again!

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[dongzky]

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>

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

[dongzky]

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>