error. but I'm stuck on trying to figure out how to approach this.
I have the needed info in the database. So I just need to compare or count and that's where I'm stuck atm.
<CFQUERY NAME="count" DATASOURCE="isiswood"> SELECT COUNT(*) AS WoodType FROM ProductionDetail WHERE rudID='041811BD' AND rudCategory='Produced' AND rudProductID LIKE '%RO%'; </cfquery>
<CFQUERY NAME="count" DATASOURCE="isiswood"> SELECT rudID AS WoodType, COUNT(*) AS WoodCount FROM ProductionDetail WHERE rudCategory='Produced' AND rudProductID LIKE '%RO%' GROUP BY rudID </cfquery>
SELECT SUBSTRING(rudProductID,6,2) AS WoodType, COUNT(*) AS WoodCount FROM ProductionDetail WHERE rudCategory='Produced' GROUP BY SUBSTRING(rudProductID,6,2)
OK Peter I'm stuck again.
I can get it to output the types and count for a query, but how can I tell it the one with the biggest number is the one I want to use?
I'll show you after I eat supper. I can't remember really.
But I rebuilt the whole code and cleaned it all up. It required much less math and variables. So I thank you for that! Plus I formatted it as you suggested and that helps a lot.
<CFQUERY NAME="sluth" DATASOURCE="isiswood"> SELECT ID=rudID, Category=rudCategory, Seq=rudSeq, Type=rudType, Product_ID=rudProductID, Description=rudDescrip, PcsPerPkg=rudPcsPerPkg, Tally=rudTally, UOM=rudBaseUnitID, Pieces=rudPieces, Volume=rudVolume, NetVolume=rudNetVolume, Cost=rudCost, AddedCost= rudAddedCost, MktValue=rudMarketValue, Total=rudTotal, NetThick=rudNetThick, NetWidth=rudNetWidth, NetLength=rudNetLength, NetType=rudVolType, LocationID=rudLocationID,rudFIFOVolume,rudForceCost,rudBoomID,prdLengthID=(SELECT prdLengthID FROM ProductMaster WHERE prdID=rudProductID) FROM ProductionDetail WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip NOT LIKE '%boards%' AND rudDescrip NOT LIKE '%blocking%' ORDER BY Seq; </cfquery>
AND rudID = <cfqueryparam value="#form.batch#" />
Run ID = <cfoutput>#HtmlEditFormat(form.batch)#</cfoutput>
Yes just an internal tool so I'm not really concerned about injections, but you're right. Get in the habit!
I have no doubt there are things that need changed. I haven't used CF in about 2 years and even then I wasn't what I would consider much good at it.
WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip NOT LIKE '%boards%' AND rudDescrip NOT LIKE '%blocking%'
WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip LIKE '%boards%' AND rudDescrip LIKE '%blocking%'
<CFQUERY NAME="woodtype" DATASOURCE="isiswood"> SELECT ID=rudID, Category=rudCategory, Seq=rudSeq, Type=rudType, Product_ID=rudProductID, Description=rudDescrip, PcsPerPkg=rudPcsPerPkg, Tally=rudTally, UOM=rudBaseUnitID, Pieces=rudPieces, Volume=rudVolume, NetVolume=rudNetVolume, Cost=rudCost, AddedCost= rudAddedCost, MktValue=rudMarketValue, Total=rudTotal, NetThick=rudNetThick, NetWidth=rudNetWidth, NetLength=rudNetLength, NetType=rudVolType, LocationID=rudLocationID,rudFIFOVolume,rudForceCost,rudBoomID,prdLengthID=(SELECT prdLengthID FROM ProductMaster WHERE prdID=rudProductID) FROM ProductionDetail WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip NOT LIKE '%boards%' AND rudDescrip NOT LIKE '%blocking%' ORDER BY Seq; </cfquery>
<CFQUERY NAME="bandb" DATASOURCE="isiswood"> SELECT ID=rudID, Category=rudCategory, Seq=rudSeq, Type=rudType, Product_ID=rudProductID, Description=rudDescrip, PcsPerPkg=rudPcsPerPkg, Tally=rudTally, UOM=rudBaseUnitID, Pieces=rudPieces, Volume=rudVolume, NetVolume=rudNetVolume, Cost=rudCost, AddedCost= rudAddedCost, MktValue=rudMarketValue, Total=rudTotal, NetThick=rudNetThick, NetWidth=rudNetWidth, NetLength=rudNetLength, NetType=rudVolType, LocationID=rudLocationID,rudFIFOVolume,rudForceCost,rudBoomID,prdLengthID=(SELECT prdLengthID FROM ProductMaster WHERE prdID=rudProductID) FROM ProductionDetail WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip LIKE '%boards%' OR rudDescrip LIKE '%blocking%' ORDER BY Seq; </cfquery>
And the name of the query. :P
Do you happen to have both queries in the code, but the second query is after the first one?
Cus if so, once the first query has executed, the WoodType variable will be replaced with that query, and so the second one will fall over when it tries to use the original WoodType string.
(Which is exactly what the error is suggesting.)
A simple way to avoid it is to prefix or suffix all query variables, (so you could call it qryWoodType or WoodTypeQry and it wouldn't conflict with the original WoodType string), or you can give it a completely different name.