How to output with a control variable inside cfquery name?

[bonneylake]

Hey Everyone,

Well i am having a problem outputting for my report and hoping someone can explain what i am doing wrong.

What the report is about is comparing my company's part price's to d and h, synnex and techdata. What i was told to do was take are part numbers and combine them with d and h, synnex and techdata to display only one column of parts (did this). Then i was told to create a cfloop using the query i created to combine the parts (did this). then inside the cfloop i created a cfif for each company, doing the cfif is suppose to get the price for each company(did this).Then i was told to add a loop control variable so that if one price is empty it will skip it instead of add the next price.

The problem is that i was able to get one price to output, but now i can not get any of the prices to output at all an not sure what i am doing wrong. Before i added a loop control variable i was able to output techdata's prices by doing #techdata.cost# but now with the loop control variable i am not able to. I was also not able to output synnex and techdata before adding the loop control variable, but i was told that how i was outputting it could be the problem. if anyone could tell me what i am doing wrong, atleast to how i am displaying techdata i would really appreciate it. Here is what i currently have.

This combines all the parts
<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>
 
 
<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="">
 
<cfoutput>
<cfloop query="matchmanu">
<cfset t = 1>
 this gets the prices for techdata
<cfif distiid EQ 'C00030'>
<cfquery name="techdata#t#" datasource="Distributor">
Select  COST
From dbo.PRODUCTS
where PART_NUM = '#matchmanu.distisku#'
</cfquery>                          
<cfelse>
 
</cfif>
<cfset t = t + 1>
 
<cfset s = 1>
 this gets the prices fo synnex
<cfif distiid EQ 'C00055'>
<cfquery name="synnex#s#" datasource="Distributor">
Select Unit_Cost__wo_PromoRebate
From dbo.tbl_synnex_price
where SYNNEX_SKU = '#matchmanu.distisku#'
</cfquery>
<cfelse>
</cfif>
<cfset s = s + 1>
 
<cfset d = 1>
 this gets the prices for dandh
<cfif distiid EQ 'C00791'>
<cfquery name="dandh#d#" datasource="Distributor">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#matchmanu.distisku#'
</cfquery>
<cfelse>
</cfif>
<cfset d = d + 1>
 
 
 
<cfif alt eq 0>
    <cfset classStr="">
    <cfset alt=1>
<cfelse>
<cfset alt=0>
</cfif>
<tr>
<td class="#classStr#">
#matchmanu.distisku#
</td>
 this outputs techdata
<td class="#classStr#">
#techdata#t#.COST#
</td>
thisoutputs synnex
<td class="#classStr#">
#synnex.Unit_Cost__wo_PromoRebate#
</td>
 this outputs dandh
<td class="#classStr#">
#dandh.unit_cost#
</td>
</tr>
</cfloop>
</cfoutput>
</table>

Thank you,
Rachel

[bonneylake]

Hey Acoder,

Asked boss and he said do it as .039 instead of the other idea we had. So would
#DecimalFormat(Pricediff)# work? when i tried it that way (decimal format) i got -.39 and i was excepting a 0 in front of the 3 so just wondering if this would be correct way to do it or not?

Thank you,
Rach

[acoder]

Well, if it's 0.39xxxxx (a whole lot of numbers), then you can't change it to 0.039. I assume you meant 0.39.

Decimalformat would work except that it won't round, so

<cfset pricediff = round(pricediff * 100)/100>

might be a better bet.

PS. it is rare to have such a huge price difference, so maybe you should be testing with something more "average".

[bonneylake]

Hey Acoder,


Well when i asked boss if he wanted it to be like 0.39 he wrote me back saying .039 should be fine so i guess either a) he put the decimal in wrong place or b) i got confused and he wants it as 0.39.An yeah it is high numbers i agree. Not sure why they didn't just type them in there rounded.but your suggestion worked perfectly. Thank you so much for all the help you have no idea how much i appreciate it :)

Thank you,Thank you :),
Rach

[acoder]

I'm glad it did. So is that the thread finished?

[bonneylake]

Hey Acoder,

Almost i got one more question i want to ask in case the people i made the report want this changed. How would i change the part where -1 appears when all 3 prices don't have a price for a particular part?i tried to change it but i am not even sure where the -1 comes from. i know its from the code below but not sure how it appears.Thought 0 would appear if none of them had a price.

 <cfset lowest = 0>
 <cfif techdata.recordcount neq 0 and techdata.COST neq 0.00> 
     <cfset lowest = techdata.COST>
      </cfif>
    <cfif synnx.recordcount neq 0 and synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
      <cfif lowest eq 0>
        <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
      <cfelseif lowest gt synnx.Unit_Cost__wo_PromoRebate>
        <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
     </cfif>
   </cfif>
 <cfif dh.recordcount neq 0 and dh.unit_cost neq 0.00> 
      <cfif lowest eq 0>
        <cfset lowest = dh.unit_cost> 
      <cfelseif lowest gt dh.unit_cost>
        <cfset lowest = dh.unit_cost> 
     </cfif>
   </cfif>
 

Thank you :),
Rach

[acoder]

Look for -1 somewhere in the code probably after the code you've posted.

Note that lines 6-10 could be combined to avoid repetition, e.g.

<cfif lowest eq 0 or lowest gt synnx.Unit_Cost__wo_PromoRebate>
    <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
</cfif>

likewise for the d&h part.

[bonneylake]

Hey Acoder,

I looked through the whole code, but no where does it have -1, unless i am missing it. Here is what i have in full.

<table width="85%" align="center">
<thead>
<tr>
<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>
 
 
 
<cfoutput>
 
<cfset alt=0>
<cfset classStr="">
 
<cfloop query="matchmanu">
<cfif alt eq 0>
    <cfset classStr="">
    <cfset alt=1>
<cfelse>
    <cfset classStr="alt">
    <cfset alt=0>
</cfif>
 
<tr>
 
<td class="#classStr#">
#matchmanu['hc part number'][matchmanu.currentrow]#
</td>
<td class="#classStr#">
#matchmanu.mpn#
</td>
<td class="#classStr#">
#matchmanu.Mfg#
</td>
<td class="#classStr#">
#decimalFormat(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 Unit_Cost__wo_PromoRebate is not null and (SYNNEX_SKU = '#val(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>
 
 <cfset lowest = 0>
 <cfif techdata.recordcount neq 0 and techdata.COST neq 0.00> 
     <cfset lowest = techdata.COST>
 </cfif>
   <cfif synnx.recordcount neq 0 and synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
      <cfif lowest eq 0 or lowest gt synnx.Unit_Cost__wo_PromoRebate>
      <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
      </cfif>
   </cfif>
 <cfif dh.recordcount neq 0 and dh.unit_cost neq 0.00> 
       <cfif lowest eq 0 or lowest gt dh.unit_cost>
       <cfset lowest = dh.unit_cost> 
       </cfif>
   </cfif>
 
 
<td class="#classStr#">
<cfset cost = #matchmanu.lastbuyquote#>
<cfif IsNumeric(cost) and #val(cost)# neq 0.0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 or pricediff lt -0.03>
<cfset pricediff = round(pricediff * 100)/100>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>
 
</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,
Rach

[acoder]

It'll be line 91:

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

which is causing the problem. If, for example, cost is 37.49 then pricediff would end up being 0 - 37.49/37.49 which equals -37.49/37.49 = -1!

To avoid that, add a condition on line 90:

<cfif lowest neq 0 and IsNumeric(cost) and val(cost) neq 0.0>

[bonneylake]

Hey Acoder,

That worked perfectly! I am glade i asked you because some of the values actually have -1 and have a lowest price to choose from so its a good thing we changed it .Other wise they would get -1 for the ones with no lowest cost and then some that have a lowest price with -1.But i cant think of any other questions to ask so i guess this topic is closed. But Thank You again for all your help!!!!

Thank you,Thank you!!! :)
Rach

[acoder]

Glad to help! Until next time... ;)