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

[acoder]

Some of the code doesn't make sense. For example, on line 38, you increment the variable t, but for what? In the next loop round, it's going to be set back to 1 (line 27). This is the same for d and s.

There's some other problems too, but first, can you post some example data to make things easier.

[bonneylake]

Hey Acoder,

Well its hard to really post examples from the database because its a mess, and its hard to make it come up. But i can tell you an example. Basically each company mine,techdata,synnex and d and h all have are own part numbers. However, there are many that have the same part number so i combined all the ones that are the same. Then i need to get the price for techdata, synnex and d and h. when i get the price the price has to match up to the part. (basically got to get the price that is suppose to be with the part). an well some parts will not have a price for that part so it has to be able to account for that by simply leaving a space. Right now though if i brought it up the products will match the price, but if a price is missing it will not account for it and so it will take the next price an add it in the blank spot instead of leaving it blank. the #t# was suppose to do the blank part (atleast that is what i was told), but it didn't make any since to me which is why i decided to ask because i don't think its right.

so heres an example of how it should come up

the part number is the part and techdata, synnex and d and h are listing

what price they have for that particular part number. some companys might not have a price for that particular part because they don't have that particular part.

 
 
part number   techdata   synnex       d and h
 
5555               6.50        7.50            6.78
 

Thank you,
Rachel

[acoder]

OK, can you explain lines 29, 42 and 54 where the distiid value is compared?

I've also noticed something else. You set, for example, t to 1, but then it's incremented so the query name on line 78 is techdata2 instead of techdata1.

[bonneylake]

Hey Acoder,

well lines 29,42 and 54. The numbers themselves specify which vendors i am looking for, in this case i am looking for synnex, d and h and techdata.the cfif is saying distributor (the company) is equal to that number. It basically just makes sure that it gets the right company's (synnex, d and h, and techdata) based on the number. without the distiid eq number i would get all the parts for all the vendors we have instead of just the 3 i need.Its hard to really fully explain because the system is confusing (an not sure why they have made it so difficult).Also, it was not fully explained to me either i was just told the basic.

an yes i admit i got the incrementing pretty confused, i am use to using cfloop not the cfquery.



Thank you,
Rach

[acoder]

But the different prices for the companies are in separate tables anyway, so I don't see the purpose of those cfifs. Also, the distiid will be only one for each row, so that would mean you would only get one company price for each row which is not what you want, I don't think. You should be comparing on part number (which you're already doing in the queries).

[bonneylake]

Hey Acoder,

well i must admit its kind of confusing and hard to explain because i admit it didn't make much since to me either. The point of the cfif is to get the right vendor and for cfquery to make sure that the vendor match with the price and then if there is no price for that vendor to make that field blank for that row of prices (which havent told it to go blank yet). All i know for sure is without the cfif is number it will not do this right (atleast that is what i was told).An well i need the price for each row because its going to display as so.

part number synnex price techdata price d and h price
5555 1.04 1.09 20.00
3393 3.00 3.43 9.99

sorry that i can not explain it further. I must admit i was not explained what was done just shown. But without the cfif for each cfquery i will not get the right vendor information for that cfquery.

Thank you,
Rachel

[acoder]

Rather than all of these multiple queries, you may be better off with combining it into the main query. You can join the tables on the distisku and distiid values.

[bonneylake]

Hey Acoder,

Well i wanted to write an say doing it this way i was able to get one entire row of information to output (which i was not able to before). The thing is there is suppose to be 14 thousand rows appearing (atleast thats what we saw when we did the cfquery to put all the manufacturer part numbers together ).But i am not sure what i am doing wrong that i am only getting one row to appear. Also, i know right now that if a price does not exist for that part that it will not make that price column blank, it will just put the next price in line in that spot.

I been trying to think of a better way to explain the cfif and hope this might be a better way. Each distributor we have is associated with an ID number.Without having cfif it will not know which distributor i am getting the parts out of. Because we have lots of distributors an they all have similar part numbers. The cfif says the distributor is equal to the number or cfif eqal to techdata.Then inside the cfquery it basically selects the price out of the table an then to make sure it gets the right price to the right part it compares the part number out of the table i am selecting the cost from against distisku which is the product id for synnex,techdata and d and h. I don't know if thats a better explanation (i hope it is). But basically the cfif is just saying which company to check for the price for that particular part. if there is no price for that particular part then the price column should be blank for that company on that particular part (which i don't have it going blank).Right now if i output it each part will have a price even if the price don't go with that part and i need to do it where the price matches up to the part.

here is what i tried, i took the #t#,#s# and #d# out of each of them an then the value for all 3 outputed. But it not showing all the rows it should have only one row.

<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>
<cfif distiid EQ 'C00030'>
<cfquery name="techdata" datasource="Distributor">
Select  COST,DESCR
From dbo.PRODUCTS
where PART_NUM = '#matchmanu.distisku#'
</cfquery>                          
<cfelse>
</cfif>
<cfset t = t + 1>
 
 
 
<cfset s = 1>
<cfif distiid EQ 'C00055'>
<cfquery name="synnex" 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>
<cfif distiid EQ 'C00791'>
<cfquery name="dandh" datasource="Distributor">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#matchmanu.distisku#'
</cfquery>
<cfelse>
</cfif>
<cfset d = d + 1>
</cfloop>
 
 
<cfif alt eq 0>
    <cfset classStr="">
    <cfset alt=1>
<cfelse>
<cfset alt=0>
</cfif>
<tr>
<!---<cfoutput>--->
<td class="#classStr#">
#matchmanu.distisku#
</td>
<td class="#classStr#">
 
#techdata.COST#
<td class="#classStr#">
#synnex.Unit_Cost__wo_PromoRebate#
</td>
<td class="#classStr#">
#dandh.unit_cost#
</td>
</tr>
 
<!---</cfloop>--->
</cfoutput>
 
</table>

Thank you,
Rachel

[bonneylake]

Hey Acoder,

I am a bit confused on what you mean by joining the distisku and distiid values on the main cfquery i have. I must admit i am afraid to mess with that big cfquery based on i don't know how much data is really involved or what all happens in each table. i was just shown what to do an not givin an explanation.

Thank you,
Rach

[bonneylake]

Hey Acoder,

First off sorry for posting 3 times, but i am a little bit closer then i was before. Well i got all the rows that need to appear showing. However, instead of a different price for each part i am getting the same price down all the rows. Like for techdata the price for all parts is 131.25999999999999, synnex is 134.34999998999999, and d and h is 98.939999999999998. Everything else for the report seems right, like all the part numbers are different but the price is the same for all parts. Here is what i have

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

Thank you,
Rach

[acoder]

It's not a problem if you make a couple of posts, especially if there's progress.

Although I don't like how this is being done, I can suggest that #matchmanu.distisku# be replaced by #distisku#.

[bonneylake]

Hey Acoder,

Yeah i am not much of a fan of how they want it done either, but i know they got a reason for it.But i changed all the #matchman.distisku# to #distisku#. But i can not figure out why i am getting the same number for each part. Like techdata is 131.259 synnex is 134.349 and d and h price is 98.93. Could it be because i don't have the price defaulting to anything so its just putting anything or is it because of the way i am doing the cfoutput and cfloop for the cfifs?

An i also got another question. I think i figured out how to do the cost where if there is no value for cost it will be blank. However, my question is if the values are not right (meaning getting the same number through out each column and not the price per part) if i do

<cfif distiid EQ 'C00030'>#techdata.COST#<cfelse>0</cfif>

when i output the cost for techdata will it still know which prices are not in the database or do i have to get the prices right first before seeing if there are prices there or not? right know i did the above example an it got me a lot of null values but not sure if its working right or not based on i am getting the same price for all

here is what i have in full right now

<cfloop query="matchmanu">
<cfif distiid EQ 'C00030'>
 
<cfquery name="techdata" datasource="Distributor">
Select  COST,DESCR
From dbo.PRODUCTS
where PART_NUM = '#distisku#'
</cfquery> 
     <cfelse>  
</cfif>
 
 
 
<cfoutput>
<cfif distiid EQ 'C00055'>
<cfquery name="synnex" datasource="Distributor">
Select Unit_Cost__wo_PromoRebate
From dbo.tbl_synnex_price
where SYNNEX_SKU = '#distisku#'
</cfquery>
<cfelse>
</cfif>
</cfoutput>
 
<cfoutput>
 
<cfif distiid EQ 'C00791'>
<cfquery name="dandh" datasource="Distributor">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#distisku#'
</cfquery>
<cfelse>
 
</cfif>
</cfoutput>
</cfloop>
 
<cfoutput>
<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>
 
 
 

Thank you,
Rach

[acoder]

The reason you're getting the same value is that the output is not within a loop.

If you include the output within the matchmenu loop, you would get different values. One more thing: the cfif comparison to the distiid values means that within one loop round, you only get one company result because distiid can only have one value.

[bonneylake]

Hey Acoder,


are you saying the values that output like #techdata.COST# need to be wrapped in the
cfloop as well an not just the cfif values, like put one cfloop around the cfifs and what outputs? or do i need to create 2 loops one for the cfif's and one for the cfoutputs?

an that does make since about me only getting one value per loop,but how would i make it so that it gets all the values for that row, unless that company does not have a price for that product and in that case just put a 0 in its place ? would something like this work?

 <cfoutput>
 <tr>
<td class="#classStr#">
 #matchmanu.distisku#
 </td>
<td class="#classStr#">
 <cfif distiid EQ 'C00030'>
 #techdata.COST#
 
</td>
 <td class="#classStr#">
 <cfelseif distiid EQ 'C00055'>
 #synnex.Unit_Cost__wo_PromoRebate#
 
 </td>
 <td class="#classStr#">
 <cfelseif distiid EQ 'C00791'>
 #dandh.unit_cost#
 <cfelse>0</cfif>
 </td>
 </tr>
 </cfoutput>

Thank you :),
Rachel

[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_Cost__wo_PromoRebate# 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.currentrow]#

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.

[bonneylake]

Hey Acoder,

Part i am confused on is how to compare. I don't believe i have done a compare before so not sure how to go about it. An then to set it after techdata would i do something like

<cfset test="#techdata.cost#">

Thank you,
Rach

[acoder]

To compare, simply use gt (greater than) or lt (less than).

<cfif something gt somethingelse>

[bonneylake]

Hey Acoder,

Not sure if i got the right idea but i did something like the following.But iam not sure if i have done it right or not.An forthe cfif should i be using #techdata.cost# or just #techdata# ?.I bolded where i added the cfif's.

<cfquery name="matchmanu" datasource="Distributor">
[usp_matching_vendor_distisku_from_MPL]
</cfquery>
 
<cfquery name="acompare" datasource="Configurator">
select Cost
from dbo.[Master Parts List]
</cfquery>
 
<cfquery name="techdata" datasource="Distributor">
Select  COST
From dbo.PRODUCTS
where PART_NUM = '#techdatasku#' 
</cfquery> 
 <cfif #acompare.Cost# gt #techdata.COST#>
<td class="#classStr#">
<cfset #acompare.Cost# = #techdata.COST#>
#dollarformat(techdata.COST)#
</td>   
</cfif>                    
 
<td>
<!---<cfquery name="synnx" datasource="Distributor">
Select Unit_Cost__wo_PromoRebate
From dbo.tbl_synnex_price
where SYNNEX_SKU = '#synnxsku#'
</cfquery>---></td>
 <cfif #techdata.COST# gt #synnx.Unit_Cost__wo_PromoRebate#>
<td class="#classStr#">
<cfset #acompare.Cost# = #synnx.Unit_Cost__wo_PromoRebate#>
 
<!---#dollarformat(synnx.Unit_Cost__wo_PromoRebate)#---></td>
</cfif>
 
 
<cfquery name="dh" datasource="Distributor">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#dandhsku#'
</cfquery>
 <cfif #synnx.Unit_Cost__wo_PromoRebate# gt #dh.unit_cost#>
<td class="#classStr#">
<cfset #acompare.Cost# = #dh.unit_cost#>
 
#dollarformat(dh.unit_cost)#</td>
</cfif>
<td class="#classStr#">
 
<!---(((#techdata.cost# - #mplpricecompare.Cost#)/#mplpricecompare.Cost#) * 100) and
(((#dh.unit_cost# - #mplpricecompare.Cost#)/#mplpricecompare.Cost#) * 100---><!---)--->
 
 
 
</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>
 
 
 
<cfset i = i + 1>
</cfloop>
</cfoutput>
</table>
 

Thank you,
Rach

[acoder]

It'd be techdata.cost, dandh.unit_cost and so on. The cfif should be within the td or outside, but the td shouldn't be inside.

You could compare after all three queries. I don't think you need another query to get the manufacturer cost - that should be part of the main query, shouldn't it?

[bonneylake]

Hey Acoder,
Actually we didn't include cost in the main cfquery so that is why i am getting the cost separate. But ok so something like this is what i should be doing correct? an then how would i set the variable to the techdata cost?

<cfquery name="techdata" datasource="Distributor">
Select  COST
From dbo.PRODUCTS
where PART_NUM = '#techdatasku#' 
</cfquery>
<cfif acompare.cost gt techdata.COST>
<td class="#classStr#">#dollarformat(techdata.COST)#
</td>   
</cfif>
 
<cfquery name="dh" datasource="Distributor">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#dandhsku#'
</cfquery>
<cfif dh.unit_cost gt dh.unit_cost>
<td class="#classStr#">
#dollarformat(dh.unit_cost)#</td>
</cfif>

Thank you,
Rach

[acoder]

A number of things to note:

1. The acompare query has no "where" clause, so it will get all costs (prices) which is not what you require.

2. I thought you wanted the cheapest of the three company prices, so you should be comparing techdata with synnex and dandh. Could you explain the exact requirements again? Is it that the cheapest of the 3 prices should be compared to the company price?

3. The cfif should not include the td tags in them otherwise you might have some cells missing in the table. You only need the comparison to set a variable, not to change the display.

4. You may consider using queries of queries for performance rather than making 4 separate queries for each row.

[bonneylake]

Hey Acoder,

Yes your right i am getting all the prices for acompare.cost because i am unsure how to say where part = part.In the other's i knew to use part number from the table an then use a sku to compare to it. But with the master parts list i am unsure how to do this since there is no sku. Its just part an i don't know if i can say part=part.

An yes i want to compare the three companys to get the price. But i am confused on how to do the compare which is why it is the way it is (never had to compare 2 columns before).The parts i am confused on is i am not sure where the cfif should be. I don't know if i am suppose to have one under each cfquery. Then i don't know if for techdata i match it to master parts list an then for d and h match the master parts list or if i do techdata match master parts list an then techdata match d and h.An then i don't know what i need to output or put in the place of price difference to output the different price. Basically i am really lost on where everything needs to be and what i need for each one.

But the requirement for it is that i need to see if there is a material cost difference (my company) > + - 3% from the lowest cost distributor(company with the lowest price).So basically i need to figure out the company with the lowest price an then compare it to my company price to see if there is a > + - 3% difference.

An with the cfif are you saying to add it inside the td? because without the td's the value (like techdata.COST) will not go in the right column so should i be doing something like

<cfquery name="techdata" datasource="Distributor">
Select  COST
From dbo.PRODUCTS
where PART_NUM = '#techdatasku#' 
</cfquery>
<cfif acompare.cost gt techdata.COST></cfif>
<td class="#classStr#">#dollarformat(techdata.COST)#
</td>  
<cfquery name="dh" datasource="Distributor">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#dandhsku#'
</cfquery>
<cfif acompare.cost gt dh.unit_cost></cfif>
<td class="#classStr#">
 
#dollarformat(dh.unit_cost)#</td>
 
 

Thank you,
Rach

[acoder]

Try to find out what fields you have in that master costs table, so you know what to add for the where clause.

For the cfif comparison, add it after all the company prices before the price difference column. First, get the cheapest price. For that, set the variable to techdata.cost. Then compare it with the synnex and dandh prices:

<cfset lowest = techdata.cost>
<cfif lowest gt synnex.cost>
  <cfset lowest = synnex.cost>
</cfif>

[bonneylake]

Hey Acoder,

Well i think this should work for the cost for the compare of my company because in my report i have hc part number and lastbuyquote already matching each other so this should match the cost up correctly.

<cfquery name="acompare" datasource="Configurator">
select Cost
from dbo.[Master Parts List]
where [HC Part Number] = '#matchmanu.LastBuyQuote#'
</cfquery>

there is one question i want to ask. I been having problems with my synnex saying Error converting data type varchar to numeric. The thing is for the first 2 rows it displays a value for synnex an when it gets to the 3rd row is when it has a problem. The thing is the 3rd row is null (no value). I know if it was not going to work no value would of displayed at all for rows 1 and 2 so is there anyway to go around this? The rest of them don't have this problem and it just displays 0.00.

But the compare did work i got the following prices techdata 34.47, synnex 33.82 and d and h was 0.00 and it choose 33.82 (yay). But how would i now go and compare the lowest price to my company's cost by > - + 3%? here is what i correctly got for the lowest price.

<cfset lowest = techdata.COST>
<cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
</cfif>
<cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
<cfif lowest gt dh.unit_cost>
</cfif>
<td class="#classStr#">
#lowest#
</td>
 

Thank you,
Rach

[acoder]

Are you sure it's the real NULL value and not the string "null"? What's the data type of that field? You can use val() to get the value as a numeric value.

The comparison is actually incorrect. The cfset statements (with the exception of the first one) should go inside the cfif statements. You will also want to add a check for 0.00 because those should be ignored when comparing.

[bonneylake]

Hey Acoder,

Well maybe i am wrong because the first 2 values for synnex are 33.82 and 0.00 so that is weird.But it says synnex_sku is numeric, so could be based on what the field is.An i know i should change the synnex_sku field to anything else. So how would i try the val() would i wrap it around synnex_sku or around the dollar format part?

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

So for the lowest i should be doing something like the below right?And how would i check for 0.00?

<cfset lowest = techdata.COST>
<cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
<cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
<cfif lowest gt dh.unit_cost>
</cfif>
</cfif>
 

Thank you,
Rach

[acoder]

The closing </cfif> should be on line 4. You should also have something similar for d&h. To check for 0.00, just use eq or neq:

<cfif dandh.cost neq 0.00>
<!--- now make the comparison --->
</cfif>

[bonneylake]

Hey Acoder,

I think i got this pretty confused. I got the first part right, but the part about checking for 0.00 i think has me off. I am not sure where i should be checking for synnex, if i should be doing it with the techdata or with the d and h like <cfif techdata.cost neq 0.00 and synnex.unit_cost__wo_promorebate neq 0.00> or do that with d and h. here is what i got

 
<cfset lowest = techdata.COST>
 
<cfif techdata.COST neq 0.00>
 
<cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
<cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
</cfif>
</cfif>
<cfif dh.unit_cost neq 0.00>
 
<cfif lowest gt dh.unit_cost>
</cfif>
</cfif>
 

Thank you,
Rach

[acoder]

The idea is that if it's 0.00, you don't make a comparison, e.g.

<cfset lowest = techdata.COST>
<cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0>
    <cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
        <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
    </cfif>
</cfif>
 

Then you'd repeat lines 2-6 for d&h.

[bonneylake]

Hey Acoder,

But how would i check if techdata has a value of 0.00 cause techdata can have 0.00 as well. Here is the full thing with d and h added to it.

<cfset lowest = techdata.COST>
 <cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0>
     <cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
         <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
     </cfif>
 </cfif>
 <cfif dh.unit_cost neq 0.0>
     <cfif lowest gt dh.unit_cost>
         <cfset lowest = dh.unit_cost>
     </cfif>
 </cfif>
 

Thank you,
Rach

[acoder]

You can add some code for that too, so if techdata is 0.0, set it to synnex unless, of course, that's also 0.0:

<cfif techdata.cost neq 0.0>
  <cfset lowest = techdata.cost>
<cfelseif synex.cost neq 0.0>
  <cfset lowest = synex.cost>
<cfelse>
  <cfset lowest = dh.cost>
</cfif>

[bonneylake]

Hey Acoder,

Would this work? an yes techdata, synnex an d and h could all have 0.00 all in one row

<cfif techdata.cost neq 0.00>
 <cfset lowest = techdata.COST>
  <!---<cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0>--->
      <cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
<cfelseif synex.cost neq 0.00>
          <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
      </cfif>
  <!---</cfif>--->
 <!--- <cfif dh.unit_cost neq 0.0>--->
      <cfif lowest gt dh.unit_cost>
<cfelse>
          <cfset lowest = dh.unit_cost>
      <!---</cfif>--->
  </cfif>
  </cfif>
 

an i think your right on the problem with the synnex its not the real null value it has to be a string, atleast based off the information i am finding online. Also, i looked over the synnex_sku field and any other tables involved an none of them that is associated with synnex has a null value.

Thank you,
Rach

[acoder]

No, that code wasn't to overwrite the previous code. It was to replace the first line only.

[bonneylake]

Hey Acoder,

Well i got this but i cant seem to get the place of it all right

 <cfif techdata.cost neq 0.00>
 <cfset lowest = techdata.COST>
   <cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0>
       <cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
 <cfelseif synex.cost neq 0.00>
           <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
       </cfif>
   </cfif>
  <cfif dh.unit_cost neq 0.0>--->
       <cfif lowest gt dh.unit_cost>
 <cfelse>
           <cfset lowest = dh.unit_cost>
       </cfif>
   </cfif>
   </cfif>

an well i figured out one thing about synnex when i did the following to output synnex #LSIsNumeric(synnx.Unit_Cost__wo_PromoRebate)#. It said the first line is numeric, but the second line is not an then after the second line is when i got an error. Any ideas on how to go around that?

Thank you,
Rach

[acoder]

Try something like this:

<cfif techdata.cost neq 0.00>
  <cfset lowest = techdata.COST>
<cfelseif synnx.Unit_Cost__wo_PromoRebate neq 0.00>
  <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
<cfelse>
   <cfset lowest = dh.unit_cost>
</cfif>
<cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0 and lowest gt synnx.Unit_Cost__wo_PromoRebate>
  <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
</cfif>
<cfif dh.unit_cost neq 0.0 and lowest gt dh.unit_cost>
   <cfset lowest = dh.unit_cost>
</cfif>

Probably not the most efficient and could be combined, but enough to get working.
@bonneylake
Check the data type of the field. Use val() around the field value which should give 0 for non-numeric values.