How to output with a control variable inside cfquery name?

[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> 
[B][B][B]<cfif #acompare.Cost# gt #techdata.COST#>[/B][/B][/B]
<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>
[B][B][B]<cfif #techdata.COST# gt #synnx.Unit_Cost__wo_PromoRebate#>[/B][/B][/B]
<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>
[B]<cfif #synnx.Unit_Cost__wo_PromoRebate# gt #dh.unit_cost#>[/B]
<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(com pany 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_cos t__wo_promoreba te 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