Is it giving an error or just producing the wrong result?

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.

OK Pete help me!

I have this:

<CFQUERY NAME="count" DATASOURCE="isiswood">
	SELECT COUNT(*) AS WoodType 
	FROM ProductionDetail WHERE rudID='041811BD' AND rudCategory='Produced' AND rudProductID LIKE '%RO%';
</cfquery>

And it's working, but how can I automate this so I don't have to put every time of shit we have. It's already in the database.

Max line length matters when I have 4 columns of code visible at once. Besides, I didn't make the rule.

You make a compelling argument in favour of tabs for indenting, though.

Which one is the actual wood type, the rudId ?

Try something along the lines of this:

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

Well here's the thing. The %RO% could be about 20 different things. Should I just create a table to hold what they could be and use it to loop through?

Oh, so "RO" is one type of wood, but the ProductID contains other stuff in addition to that?

Is it always two characters at the same location?

If so, you can use mid or substring or whatever function MSSQL uses to do this... looks like it is SUBSTRING, so...

SELECT SUBSTRING(rudProductID,6,2) AS WoodType, COUNT(*) AS WoodCount
FROM ProductionDetail WHERE rudCategory='Produced'
GROUP BY SUBSTRING(rudProductID,6,2)

If the RO (and similar values) changes position in the code then you can't do that, but probably they're in the same place?

Nice! Yes always in the same spot. That should be saweet!

That worked like a charm! Thanks!

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?

Nevermind I got it. Probably not the prettiest but it's working :)

Did you ORDER BY counted_column DESC or something more complicated?

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.

This is what I did. It let me eliminate a bunch of if's and other math.

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

Here is my code after your help.

Much better... though still a whole bunch of stuff I want to change there. :$

But anyway, yeah, if you make it to "ORDER BY WoodCount DESC" in your count query, then all you need is <cfset WoodType = Count.WoodType[1] /> instead of that cfoutput loop.

(Or, you can skip that entirely, and later on just use #Count.WoodType[1]# directly.)

You can probably save a small bit of memory by putting a "TOP 1" after the SELECT too, since by then you'll only care about the first row, but it'll still work without that, just not as efficiently.


All this is just an internal tool, yes - not something that will be publicly available?

In general, you should never directly do #form.whatever# or #url.whatever# or #cookie.whatever# inside a cfquery, because it potentially allows people to inject SQL, which is bad.

Solution is to use query parameters, like this:

AND rudID = <cfqueryparam value="#form.batch#" />

Which prevents SQL injection (and sometimes makes it easier for a database to optimise queries, so can make it faster too).

Same thing applies on the HTML side - to prevent people injecting HTML/JavaScript you need to use HtmlEditFormat for any unsanitized user input:

Run ID = <cfoutput>#HtmlEditFormat(form.batch)#</cfoutput>

Of course, that matters more for stuff on the actual internet - if this is a local tool then it's a question of if you can trust your work colleagues, but both of them are good habits to get into. :)

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.

Any idea why this would work:

WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip NOT LIKE '%boards%' AND rudDescrip NOT LIKE '%blocking%'

And this wouldn't?

WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip LIKE '%boards%' AND rudDescrip LIKE '%blocking%'

It tells me this:

It's not the NOTs that are making it not work (and if it was, they would give an SQL error instead).

The error message says you're trying to use a Query variable as if it were a String, and a query can't be implicitly converted (like numbers,booleans,etc can), so it falls over and complains.

Did you do the change in msg:38393.84 and/or anything else?

You've only got two variables shown, form.batch and WoodType - and neither of them should be queries based on the previous code, so either the code has changed, or the data has changed and is causing different behaviour in the code.

Is that definitely the line that the error is pointing to?
If so, are there any other variables inside the cfquery block?

No the only thing I changed was removing the NOT.

This works:

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

This doesn't:

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