How to output with a control variable inside cfquery name?

[bonneylake]

Hey Acoder,

Alrighty just wanted to make sure it be ok to do it that way. But i do have another problem which is going to affect doing the < - + 3% part.

Well i am trying to just output the cost for my company. The problem i am having is the way its outputting. If i do it this way it matching up the cost with the lastbuyquote (or where the cost should be) but instead of displaying the cost its displaying the lasbuyquote.

<cfquery name="acompare" datasource="Configurator">
select Cost
from dbo.[Master Parts List]
where Cost = #matchmanu.lastbuyquote#
</cfquery>

an then if i do it this way, it outputs the cost but it displays the same cost down the whole column an doesn't match up property so all i see is 45 all the way down.

<cfquery name="acompare" datasource="Configurator">
select Cost
from dbo.[Master Parts List]
where lastbuyquote = Cost
</cfquery>-

Any suggestions on what i am doing wrong?

Thank you,
Rach

[acoder]

What's the last buy quote? What you need to use in the where clause is a foreign key that matches with the part number. Perhaps a look at the fields in that table may help.

[bonneylake]

Hey Acoder,

Lastbuyquote is one of the fields that appear in the master parts list table,but it is used in the stored procedure.So i figured if i used the lastbuyquote from the stored procedure and matched it up to the cost that it would match up cost to the correct lastbuyquotes. but there are a lot of fields in the table an the only field in there that holds the part number (which i am outputting using the stored procedure) is mpn. An i don't think i can just use mpn without doing matchmanu.mpn because it will output all the mpn instead of just the ones used in the report. so is the problem the fact that i am using a column from a different cfquery or stored procedure that is causing the trouble?

Thank you,
Rach

[acoder]

If you can get the price from the main stored procedure, then there's no need for a separate query to get the cost/price. If you can't, then look at the fields in the Master Parts Table and see which one is for parts number. Then, in the query, match that field against whichever field is the parts number in the stored procedure.

[bonneylake]

Hey Acoder,

Your second idea worked perfectly! I feel blond for not seeing that because it makes perfect sense! So how would i begin doing the > - + 3% part?

Thank you :),
Rach

[acoder]

If you want to work out the price difference as a percentage:

(lowest - company price)/company price

I've not tested, but that would be the idea.

[bonneylake]

Hey Acoder,

would something like this work for it?

(#acompare.Cost# - (#lowest# * 3 / 100)) and (#acompare.Cost# + (#lowest# * 3 / 100))

an how would i output just the results (final number). When i do it above i get the whole line appearing with the values.i tried <cfset test = the above but i ended up getting errors.

Thank you,
Rach

[acoder]

What exactly do you want to display? The price difference, the % price difference, 3% of the lowest price?

[bonneylake]

Hey Acoder,

Basically i am trying to see my company's price difference of > + - 3% from the lowest cost distributor (company with the lowest price)

Thank you,
Rach

[acoder]

So, if it's less than +- 3%, you don't want to display it?

[bonneylake]

Hey Acoder,

Yes basically they don't want to see it unless its 3% and up.

Thank you,
Rach

[acoder]

So you could try

<cfset cost = aCompare.cost>
<cfset pricediff = (lowest - cost)/cost)>

Then check that it's greater than 3 or less than -3. If it is, display, otherwise just display nothing or a space.

[bonneylake]

Hey Acoder,

Don't think i got this right. Am i suppose to be using lowest for the gt or lt or am i suppose to be using pricediff?An i am getting and error with the ) it says Invalid parser construct

<cfset cost = #acompare.cost#>
<cfset pricediff = (#lowest# - cost)/cost)>
<cfif lowest gt 0.03 or lowest lt -0.03>
#cost#
<cfelse>
0
</cfif>

Thank you,
Rach

[acoder]

pricediff, not lowest.

[bonneylake]

Hey Acoder,

So would something like this be correct? or does the pricediff gt and lt need to be on the same line?

<cfset cost = #acompare.cost#>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03>
#cost#
<cfelseif pricediff lt -0.03>
0
</cfif>

also i am getting an error saying

An error occurred while evaluating the expression:

pricediff = ((#lowest# - cost)/cost)

Cannot convert to number.

An i think i got one part confused. They want to see > + - 3%. I been thinking the whole time its <.



Thank you,
Rach