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,s ynnex 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