How to output with a control variable inside cfquery name?

[acoder]

I think that you need to work with some sample data, so that you know what's working and what's not. You can use the "maxrows" attribute if you don't want to change the query.

[bonneylake]

Hey Acoder,

Well the thing is is the only data i was given to work with, which i can see the results and see whats working an not working thats how i know that none of the numbers are right. An i am a bit confused on what you mean about maxrows are you saying to use that for each cfquery? Also the cfif example i gave earlier will not work for making it 0 if there is no price?

I tried doing the cfloop part as such but now it gives me errors for #synnex.Unit_Co st__wo_PromoReb ate# saying it can not resolve the parameter

<table width="95%" align="center">
<thead>
<tr>

<th>Manu Part number </th>
<th>Techdata_Price</th>
<th>Synnex Price</th>
<th>D&H Price</th>
</tr>
</thead>
<cfset alt=0>
<cfset classStr="">


<cfloop query="matchmanu">

<cfset t = 1>
<cfif distiid EQ 'C00030'>
<cfoutput>
<cfquery name="techdata" datasource="Distributor#t#">
Select  COST,DESCR
From dbo.PRODUCTS
where PART_NUM = '#distisku#'
</cfquery> 
</cfoutput>
</cfif>




<cfif distiid EQ 'C00055'>
<cfoutput>
<cfquery name="synnex" datasource="Distributor#t#">
Select Unit_Cost__wo_PromoRebate
From dbo.tbl_synnex_price
where SYNNEX_SKU = '#distisku#'
</cfquery>
</cfoutput>
<cfelse>
</cfif>




<cfif distiid EQ 'C00791'>
<cfoutput>
<cfquery name="dandh" datasource="Distributor#t#">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#distisku#'
</cfquery>
</cfoutput>
<cfelse>

</cfif>
<cfset t = t + 1>

<cfoutput>
<cfif alt eq 0>
	<cfset classStr="">
	<cfset alt=1>
<cfelse>
<cfset alt=0>
</cfif>
<tr>
<td class="#classStr#">
#matchmanu.distisku#
</td>
<td class="#classStr#">
<!---<cfif distiid EQ 'C00030'>--->
#techdata.COST#
<!---<cfelse>0</cfif>--->
</td>
<td class="#classStr#">
<!---<cfif distiid EQ 'C00055'>--->
#synnex.Unit_Cost__wo_PromoRebate#
<!---<cfelse>0</cfif>--->
</td>
<td class="#classStr#">
<!---<cfif distiid EQ 'C00791'>--->
#dandh.unit_cost#
<!---<cfelse>0</cfif>--->
</td>
</tr>
</cfoutput>
</cfloop>

Thank you,
Rach

[acoder]

That was just to see some sample data. If it's sensitive, perhaps you could PM me some sample data that I could look at.

[bonneylake]

Hey Acoder,

Yeah i have tried putting cfloop around the whole thing before an it works great for techdata. But it don't get the values for the other 2 unless i don't have the cfloop around the cfoutput. An right now its hard to really show any sample data because i cant even get a full row to appear right now. All i get is the part number an really techdata to show. By doing it the way you suggested with the cfloop it gets the right value for techdata, but not for the other 2. But if i don't have a cfloop around the whole thing then i get values for all 3 companys, but none of them are the right value that match the part. If i had something outputting right i would show you but i cant even get it to output the data right based on i got the cfloop or cfoutput somehow mixed up for it. but i can show you what i am seeing right now when i mess with the cfloop. here is what i see.

when i get the right information for tech data by having a cfloop around the cfoutput this is what i get

part number   the manufacturer         techdata price
636070             ABL Electronics       2.87

nothing displays for synnex or d and h cause get errors

if i don't put a cfloop around the cfoutputs and just leave it as a cfoutput and leave the cfloop just around the cfif's i get

part number    manufacturer       techdat price     synnex price    d and h price
636070             ABL Electronics     131.25         134.34                98.93

i don't know if that helps much, but thats all i currently get.

Thank you,
Rach

[bonneylake]

Hey Acoder,

Well i wanted to write an say i have not gotten any further. But i did double check with the other programmer and ask if i had all the columns right and he said he was pretty sure. So i am completely lost on what to do. The last 3 things the other programmer told me to do was the following

1)move the outputs into the <cfif> will not have that variable defined if it didn't query during the previous cfif

2)Also told me to be careful of my big outer loop saying if you can’t match on a part number, and the recordset goes to empty, then when you print out the price, it might print out the one from the previous query. SO, you’ll have to use a loop control variable to keep track of your multiple queries.

Example:

<cfset i = 1>

<cfloop query="matchmanu">

{code}

<cfquery name=”techdata#i#”>

{code}

….

<cfset i = i +1>

</cfloop>

And do that for all of your internal queries inside the loop, and be sure to also use your Loop control variable when outputting

3) That i needed to account for when a part and price don't match up.

Until i do all 3 i don't think i am going to be able to get any values to output but i am completely lost on how to do this all correctly.

here is what i have tried so far based on his information. The only 2 thing i can not get to work is 1) i have no clue what he means by move the outputs into the <cfif> will not have that variable defined if it didn't query during the previous cfif.An when he said that i had the exact same set up i have now with the cfoutputs and cfloops.2) no clue how to make it where if a price does not exist for that part that it will ignore the price an leave the field blank. Here is what i currently got based on his information

<cfquery name="matchmanu" datasource="Configurator">
select a.[hc part number]'hcpartnumber',a.mpn,a.Mfg,a.lastbuyquote,a.[description],b.prodid,c.distisku,c.distiid
from [svr-htssqldb].configurator.dbo.[Master Parts List] a
inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_prod b
on a.mpn=b.mfpn
inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_metamap c
on b.prodid=c.prodid
where a.lastbuyquote is not null and (c.distiid='c00030' or c.distiid='c00055' or c.distiid='c00791')
</cfquery>

<cfoutput>
<cfif alt eq 0>
	<cfset classStr="">
	<cfset alt=1>
<cfelse>
<cfset alt=0>
</cfif>
<tr>
<td class="#classStr#">
#matchmanu.hcpartnumber#
</td>
<td class="#classStr#">
#matchmanu.distisku#
</td>
<td class="#classStr#">
#matchmanu.Mfg#</td>
<td class="#classStr#">
#matchmanu.lastbuyquote#
</td>
<td class="#classStr#">
<!---test--->
</td>
<td class="#classStr#">
#evaluate("techdata#i#.COST")#
</td>
<td class="#classStr#">
#evaluate("synnex#i#.Unit_Cost__wo_PromoRebate")#</td>
<td class="#classStr#">
#evaluate("dh#i#.unit_cost")#</td>
<!---<td class="#classStr#">
#matchmanu.DESCRIPTION#
</td>
<td class="#classStr#">
#DESCR#
</td>--->
</tr>


</cfoutput>
<cfset i = i + 1>
</cfloop>
</table>

Thank you,
Rach

[bonneylake]

Hey Acoder,

I wanted to write an let you know i did figure out most of it. The other programmer ended up having to look it over because i couldn't get any further. Turns out some of the problem was the way i was programming it an some was the database. I personally have never done it this way but it did work.

<table width="95%" align="center">
<thead>
<tr>
<th>How many</th>
<th>HC part number</th>
<th>Manu Part number </th>
<th>MPL_manufacturer</th>
<th>MPL.LastBuyQuote</th>
<th>%price diff</th>
<th>Techdata_Price</th>
<th>Synnex Price</th>
<th>D&H Price</th>
<th>MPL_Description</th>
<th>Techdata Description</th>
</tr>
</thead>
<cfset alt=0>
<cfset classStr="">



<cfset counter=0>
<cfoutput>
<cfset i = 1>
<cfloop query="matchmanu">
<cfif alt eq 0>
	<cfset classStr="">
	<cfset alt=1>
<cfelse>
<cfset alt=0>
</cfif>
<tr>

<td class="#classStr#">
<cfset counter=counter +1>
#counter#
</td>
<td class="#classStr#">
#matchmanu.hcpartnumber#
</td>
<td class="#classStr#">
#matchmanu.distisku#
</td>
<td class="#classStr#">
#matchmanu.Mfg#</td>
<td class="#classStr#">
#matchmanu.lastbuyquote#
</td>
<td class="#classStr#">
<!---test--->
</td>
<cfif distiid EQ 'C00030'>
<cfquery name="techdata" datasource="Distributor">
Select  COST,DESCR
From dbo.PRODUCTS
where PART_NUM = '#distisku#' 
</cfquery> 
<td class="#classStr#">
#dollarformat(techdata.COST)#
</td>                       
<cfelse>
<td>
</td>
</cfif>

<cfif distiid EQ 'C00055'>
<cfquery name="synnex" datasource="Distributor">
Select Unit_Cost__wo_PromoRebate
From dbo.tbl_synnex_price
where SYNNEX_SKU = '#distisku#'
</cfquery>
<td class="#classStr#">
#synnex.Unit_Cost__wo_PromoRebate#</td>
<cfelse>
<td>
</td>
</cfif>

<cfif distiid EQ 'C00791'>
<cfquery name="dh" datasource="Distributor">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#distisku#'
</cfquery>
<td class="#classStr#">
#dh.unit_cost#</td>
<cfelse>
<td>
</td>
</cfif>

<td class="#classStr#">
#matchmanu.DESCRIPTION#
</td>
<td class="#classStr#">
#techdata.DESCR#
</td>
</tr>

<cfset i = i + 1>
</cfloop>
</cfoutput>
</table>

but i do have one more question i was hoping you could help lead me in the right direction on.If this needs to be another question or in another form let me know.Well they also want on this report a price difference. Basically they want to see anything that has are company's part cost different of > + - 3% from the lowest cost distributor (this case the 3 company's i got the price for).But i am not sure how i would figure out of the the 3 company's who has the lowest price.

But Thank you so much for all the help you have given me.

Thank you,
Rach

[acoder]

Sorry I couldn't look at this sooner.

I can see how it's been solved and I did suspect (based on your information) that this was one way of solving it, but it does seem odd to have only one price on one row (out of three companies).

On the price difference, you can use an array or struct to store the lowest value for each part, or you could keep one variable set to the manufacturer price and compare with the company prices to work out the difference and maybe highlight ones > +- 3%.

[bonneylake]

Hey Acoder,

Actually the price on one row problem was being cause by the database an had to get the other programmer to help me fix it since it was beyond my control. So that problem is fixed.

An the second way you suggested is the way i need to go because i am suppose to compare the lowest cost distributor (one of the 3 company's) to our company.But only thing unsure if i need to show > - + next to each price and it has to be by 3%.The other tricky part is i am suppose to do this for each row compare our company to the lowest cost distributor so i am not sure where to begin based on the information.

I came up with this but i know i am way off on my thinking.But let me know how to begin because i am a bit lost on where to go.

<cfquery name="pricedifference" datasource="Distributor">
Select MIN(COST,Unit_Cost__wo_PromoRebate,unit_cost) as minCost,Cost
From dbo.PRODUCTS,dbo.tbl_synnex_price,dbo.tbl_dandh_price,
[svr-htssqldb].configurator.dbo.[Master Parts List]
where (Cost > minCost) or (Cost + minCost) or (Cost -minCost)
</cfquery>

Oh an for some reason i can not get my hc part number to output. The column name in the table is hc part number an it wont out put i have tried the following below. Do you have any suggestions on what i could try?

#matchmanu.[hc part number]#
#evaluate(matchmanu.hc part number)#
#evaluate(matchmanu.[hc part number])#
#matchmanu["hc part number"]#
#matchmanu.['hc part number']#
#matchmanu.hc part number#

Thank you,
Rach

[acoder]

There's no need for a query to compare prices. You can just do it within the loop when you get the company price. Perhaps it might be an idea to move the Price Difference column to after the company prices.

Re. your second problem. Change the query to use an alias for the part number, e.g.

select [hc part number] as hcpartnumber, ...

[bonneylake]

Hey Acoder,

well actually i forgot to add a few thing i did change. Now to get the values for all the fields i only use a stored procedure (with i am not allowed to see how the stored procedure was done and can not change it) instead of the select. Before when i was doing the select i did the hc part number as you described and it worked but now that its in a stored procedure i don't know how to do that. Is there another way i could try to make hc part number work without having to do the part about select or is doing the part about select the only thing that will make it work correctly?If thats the case i have to get the other programmer to change the stored procedure cause i have no permission.

An my other question is how would i go about getting the lowest price for each row by doing it in the cfloop? would i just add a where to each cfquery for a price looking for the lowest?

here is what i currently have (i moved the price difference behind d and h price like you said)

gets all the information (this is what my select got replaced with)

<cfquery name="matchmanu" datasource="Distributor">
[usp_matching_vendor_distisku_from_MPL]
</cfquery>

<table width="95%" align="center">
<thead>
<tr>
<th>How many</th>
<th>HC part number</th>
<th>Manu Part number </th>
<th>MPL_manufacturer</th>
<th>MPL.LastBuyQuote</th>
<th>Techdata_Price</th>
<th>Synnex Price</th>
<th>D&H Price</th>
<th>%price diff</th>
<th>MPL_Description</th>
<th>Techdata Description</th>
</tr>
</thead>
<cfset alt=0>
<cfset classStr="">



<cfset counter=0>
<cfoutput>
<cfloop query="matchmanu">
<cfif alt eq 0>
	<cfset classStr="">
	<cfset alt=1>
<cfelse>
<cfset alt=0>
</cfif>
<tr>

<td class="#classStr#">
<cfset counter=counter +1>
#counter#
</td>
<td class="#classStr#">
<!---#matchmanu."["hc part number"]"#--->
</td>
<td class="#classStr#">
#matchmanu.mpn#
</td>
<td class="#classStr#">
#matchmanu.Mfg#</td>
<td class="#classStr#">
#matchmanu.lastbuyquote#
</td>
<cfquery name="techdata" datasource="Distributor">
Select  COST
From dbo.PRODUCTS
where PART_NUM = '#techdatasku#' 
</cfquery> 
<td class="#classStr#">
#dollarformat(techdata.COST)#
</td>                       
<cfquery name="synnx" datasource="Distributor">
Select Unit_Cost__wo_PromoRebate
From dbo.tbl_synnex_price
where SYNNEX_SKU = '#synnxsku#'
</cfquery>
<td class="#classStr#">
#dollarformat(synnx.Unit_Cost__wo_PromoRebate)#-</td>
<cfquery name="dh" datasource="Distributor">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#dandhsku#'
</cfquery>
<td class="#classStr#">
#dollarformat(dh.unit_cost)#</td>
<td class="#classStr#">

</td>
<td class="#classStr#">
#matchmanu.DESCRIPTION#
</td>
<td class="#classStr#">
<cfquery name="techdatad" datasource="Distributor">
Select DESCR
from dbo.PRODUCTS
where PART_NUM = '#techdatasku#' 
</cfquery>
#techdatad.DESCR#
</td>
</tr>
</cfloop>
</cfoutput>
</table>

Thank you,
Rachel

[acoder]

The space problem should be fixed in the query really, but if you've no choice you can use something like #matchmanu['hc part number'][matchmanu.curre ntrow]#

As for the price difference, there's no need for a query. You already have the values, so you just need to compare to the manufacturer price:

((company price - manufacturer price)/manufacturer price) * 100

[bonneylake]

Hey Acoder,

Your idea for the hc part number worked perfectly! But i am still a bit confused on how to output by 3%. I see that we are subtracting, but how would i also get > + and do that by 3%? An then how i check all 3 company's at the same time?
This is what i currently have but the whole line is being displayed and the only thing that needs to be displayed is what the price difference is from my company to the company who has the lowest cost (or basically the result).

<cfquery name="dh" datasource="Distributor">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#dandhsku#'
</cfquery>
<td class="#classStr#">
#dollarformat(dh.unit_cost)#</td>
<td class="#classStr#">
<cfquery name="mplpricecompare" datasource="Configurator">
select Cost
from dbo.[Master Parts List]
</cfquery>
(((#techdata.cost# - #mplpricecompare.Cost#)/#mplpricecompare.Cost#) * 100) and
<!---(((#synnex.Unit_Cost__wo_PromoRebate# - #mplpricecompare.Cost#)/#mplpricecompare.Cost#) * 100)---> and
(((#dh.unit_cost# - #mplpricecompare.Cost#)/#mplpricecompare.Cost#) * 100)
</td>

Thank you,
Rach

[acoder]

In any one row, you probably have only one company price, so I assume the part numbers are grouped/ordered, so the company prices would appear together.

You can work out the price difference on each row. To work out the cheapest price, keep a variable and set it when a new part number comes up. When you get the next company price, compare it with this value to get the cheapest.

All of this could have been simpler if you had more control.

[bonneylake]

Hey Acoder,

I am completely confused on how to set it. All i am thinking is something like

<cfset test="PART_NUM,SYNNEX_SKU,dandh_item_num">

but i don't even think i have that right an no clue how i would compare it.

An yes it would be easier if i had more control i agree.

Thank you,
Rach

[acoder]

I've just looked again at your updated code in post #25.

So, it seems the code is improved and the prices are on one row. This does make it easier. Compare the three prices with the lastbuyquote or whatever the manufacturer price that you're comparing against. After the techdata query, you can set the variable to the techdata cost. Then compare to the synnex and d&h prices.