469,326 Members | 1,423 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

How to output with a control variable inside cfquery name?

769 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. This combines all the parts
  2. <cfquery name="matchmanu" datasource="Configurator">
  3. select a.[hc part number]'hcpartnumber',a.mpn,a.Mfg,a.lastbuyquote,a.[description],b.prodid,c.distisku,c.distiid
  4. from [svr-htssqldb].configurator.dbo.[Master Parts List] a
  5. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_prod b
  6. on a.mpn=b.mfpn
  7. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_metamap c
  8. on b.prodid=c.prodid
  9. where a.lastbuyquote is not null and (c.distiid='c00030' or c.distiid='c00055' or c.distiid='c00791')
  10. </cfquery>
  11.  
  12.  
  13. <table width="95%" align="center">
  14. <thead>
  15. <tr>
  16. <th>Manu Part number </th>
  17. <th>Techdata_Price</th>
  18. <th>Synnex Price</th>
  19. <th>D&H Price</th>
  20. </tr>
  21. </thead>
  22. <cfset alt=0>
  23. <cfset classStr="">
  24.  
  25. <cfoutput>
  26. <cfloop query="matchmanu">
  27. <cfset t = 1>
  28. this gets the prices for techdata
  29. <cfif distiid EQ 'C00030'>
  30. <cfquery name="techdata#t#" datasource="Distributor">
  31. Select  COST
  32. From dbo.PRODUCTS
  33. where PART_NUM = '#matchmanu.distisku#'
  34. </cfquery>                          
  35. <cfelse>
  36.  
  37. </cfif>
  38. <cfset t = t + 1>
  39.  
  40. <cfset s = 1>
  41. this gets the prices fo synnex
  42. <cfif distiid EQ 'C00055'>
  43. <cfquery name="synnex#s#" datasource="Distributor">
  44. Select Unit_Cost__wo_PromoRebate
  45. From dbo.tbl_synnex_price
  46. where SYNNEX_SKU = '#matchmanu.distisku#'
  47. </cfquery>
  48. <cfelse>
  49. </cfif>
  50. <cfset s = s + 1>
  51.  
  52. <cfset d = 1>
  53. this gets the prices for dandh
  54. <cfif distiid EQ 'C00791'>
  55. <cfquery name="dandh#d#" datasource="Distributor">
  56. Select unit_cost
  57. From dbo.tbl_dandh_price
  58. where dandh_item_num = '#matchmanu.distisku#'
  59. </cfquery>
  60. <cfelse>
  61. </cfif>
  62. <cfset d = d + 1>
  63.  
  64.  
  65.  
  66. <cfif alt eq 0>
  67.     <cfset classStr="">
  68.     <cfset alt=1>
  69. <cfelse>
  70. <cfset alt=0>
  71. </cfif>
  72. <tr>
  73. <td class="#classStr#">
  74. #matchmanu.distisku#
  75. </td>
  76. this outputs techdata
  77. <td class="#classStr#">
  78. #techdata#t#.COST#
  79. </td>
  80. thisoutputs synnex
  81. <td class="#classStr#">
  82. #synnex.Unit_Cost__wo_PromoRebate#
  83. </td>
  84. this outputs dandh
  85. <td class="#classStr#">
  86. #dandh.unit_cost#
  87. </td>
  88. </tr>
  89. </cfloop>
  90. </cfoutput>
  91. </table>
Thank you,
Rachel
Feb 3 '09 #1

✓ answered by acoder

If you can get the price from the main stored procedure, then there's no need for a separate query to get the cost/price. If you can't, then look at the fields in the Master Parts Table and see which one is for parts number. Then, in the query, match that field against whichever field is the parts number in the stored procedure.

109 11558
acoder
16,027 Expert Mod 8TB
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.
Feb 3 '09 #2
bonneylake
769 512MB
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.


Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. part number   techdata   synnex       d and h
  4.  
  5. 5555               6.50        7.50            6.78
  6.  
Thank you,
Rachel
Feb 3 '09 #3
acoder
16,027 Expert Mod 8TB
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.
Feb 3 '09 #4
bonneylake
769 512MB
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
Feb 3 '09 #5
acoder
16,027 Expert Mod 8TB
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).
Feb 4 '09 #6
bonneylake
769 512MB
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
Feb 4 '09 #7
acoder
16,027 Expert Mod 8TB
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.
Feb 4 '09 #8
bonneylake
769 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="matchmanu" datasource="Configurator">
  2. select a.[hc part number]'hcpartnumber',a.mpn,a.Mfg,a.lastbuyquote,a.[description],b.prodid,c.distisku,c.distiid
  3. from [svr-htssqldb].configurator.dbo.[Master Parts List] a
  4. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_prod b
  5. on a.mpn=b.mfpn
  6. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_metamap c
  7. on b.prodid=c.prodid
  8. where a.lastbuyquote is not null and (c.distiid='c00030' or c.distiid='c00055' or c.distiid='c00791')
  9. </cfquery>
  10.  
  11. <table width="95%" align="center">
  12. <thead>
  13. <tr>
  14. <th>Manu Part number </th>
  15. <th>Techdata_Price</th>
  16. <th>Synnex Price</th>
  17. <th>D&H Price</th>
  18. </tr>
  19. </thead>
  20. <cfset alt=0>
  21. <cfset classStr="">
  22.  
  23. <cfoutput>
  24. <cfloop query="matchmanu">
  25. <cfset t = 1>
  26. <cfif distiid EQ 'C00030'>
  27. <cfquery name="techdata" datasource="Distributor">
  28. Select  COST,DESCR
  29. From dbo.PRODUCTS
  30. where PART_NUM = '#matchmanu.distisku#'
  31. </cfquery>                          
  32. <cfelse>
  33. </cfif>
  34. <cfset t = t + 1>
  35.  
  36.  
  37.  
  38. <cfset s = 1>
  39. <cfif distiid EQ 'C00055'>
  40. <cfquery name="synnex" datasource="Distributor">
  41. Select Unit_Cost__wo_PromoRebate
  42. From dbo.tbl_synnex_price
  43. where SYNNEX_SKU = '#matchmanu.distisku#'
  44. </cfquery>
  45. <cfelse>
  46. </cfif>
  47. <cfset s = s + 1>
  48.  
  49. <cfset d = 1>
  50. <cfif distiid EQ 'C00791'>
  51. <cfquery name="dandh" datasource="Distributor">
  52. Select unit_cost
  53. From dbo.tbl_dandh_price
  54. where dandh_item_num = '#matchmanu.distisku#'
  55. </cfquery>
  56. <cfelse>
  57. </cfif>
  58. <cfset d = d + 1>
  59. </cfloop>
  60.  
  61.  
  62. <cfif alt eq 0>
  63.     <cfset classStr="">
  64.     <cfset alt=1>
  65. <cfelse>
  66. <cfset alt=0>
  67. </cfif>
  68. <tr>
  69. <!---<cfoutput>--->
  70. <td class="#classStr#">
  71. #matchmanu.distisku#
  72. </td>
  73. <td class="#classStr#">
  74.  
  75. #techdata.COST#
  76. <td class="#classStr#">
  77. #synnex.Unit_Cost__wo_PromoRebate#
  78. </td>
  79. <td class="#classStr#">
  80. #dandh.unit_cost#
  81. </td>
  82. </tr>
  83.  
  84. <!---</cfloop>--->
  85. </cfoutput>
  86.  
  87. </table>
Thank you,
Rachel
Feb 4 '09 #9
bonneylake
769 512MB
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
Feb 4 '09 #10
bonneylake
769 512MB
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

Expand|Select|Wrap|Line Numbers
  1. <table width="95%" align="center">
  2. <thead>
  3. <tr>
  4. <th>Manu Part number </th>
  5. <th>Techdata_Price</th>
  6. <th>Synnex Price</th>
  7. <th>D&H Price</th>
  8. </tr>
  9. </thead>
  10. <cfset alt=0>
  11. <cfset classStr="">
  12.  
  13. <!---<cfoutput>
  14. --->
  15.  
  16. <cfloop query="matchmanu">
  17. <cfset t = 1>
  18.  
  19. <cfif distiid EQ 'C00030'>
  20. <cfoutput>
  21. <cfquery name="techdata" datasource="Distributor">
  22. Select  COST,DESCR
  23. From dbo.PRODUCTS
  24. where PART_NUM = '#matchmanu.distisku#'
  25. </cfquery> 
  26. </cfoutput>                         
  27. <cfelse>
  28.  
  29. </cfif>
  30. <cfset t = t + 1>
  31.  
  32.  
  33. <cfoutput>
  34. <cfset s = 1>
  35. <cfif distiid EQ 'C00055'>
  36. <cfquery name="synnex" datasource="Distributor">
  37. Select Unit_Cost__wo_PromoRebate
  38. From dbo.tbl_synnex_price
  39. where SYNNEX_SKU = '#matchmanu.distisku#'
  40. </cfquery>
  41. <cfelse>
  42. </cfif>
  43. <cfset s = s + 1>
  44. </cfoutput>
  45.  
  46. <cfoutput>
  47. <cfset d = 1>
  48. <cfif distiid EQ 'C00791'>
  49. <cfquery name="dandh" datasource="Distributor">
  50. Select unit_cost
  51. From dbo.tbl_dandh_price
  52. where dandh_item_num = '#matchmanu.distisku#'
  53. </cfquery>
  54. <cfelse>
  55. </cfif>
  56. <cfset d = d + 1>
  57. </cfoutput>
  58. </cfloop>
  59.  
  60.  
  61. <cfoutput query="matchmanu">
  62. <cfif alt eq 0>
  63.     <cfset classStr="">
  64.     <cfset alt=1>
  65. <cfelse>
  66. <cfset alt=0>
  67. </cfif>
  68. <tr>
  69. <!---<cfoutput>--->
  70. <td class="#classStr#">
  71. #matchmanu.distisku#
  72. </td>
  73. <td class="#classStr#">
  74. #techdata.COST#
  75. </td>
  76. <td class="#classStr#">
  77. #synnex.Unit_Cost__wo_PromoRebate#
  78. </td>
  79. <td class="#classStr#">
  80. #dandh.unit_cost#
  81. </td>
  82. </tr>
  83.  
  84. <!---</cfloop>--->
  85. </cfoutput>
  86.  
  87. </table>
Thank you,
Rach
Feb 4 '09 #11
acoder
16,027 Expert Mod 8TB
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#.
Feb 4 '09 #12
bonneylake
769 512MB
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
Expand|Select|Wrap|Line Numbers
  1. <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
Expand|Select|Wrap|Line Numbers
  1. <cfloop query="matchmanu">
  2. <cfif distiid EQ 'C00030'>
  3.  
  4. <cfquery name="techdata" datasource="Distributor">
  5. Select  COST,DESCR
  6. From dbo.PRODUCTS
  7. where PART_NUM = '#distisku#'
  8. </cfquery> 
  9.      <cfelse>  
  10. </cfif>
  11.  
  12.  
  13.  
  14. <cfoutput>
  15. <cfif distiid EQ 'C00055'>
  16. <cfquery name="synnex" datasource="Distributor">
  17. Select Unit_Cost__wo_PromoRebate
  18. From dbo.tbl_synnex_price
  19. where SYNNEX_SKU = '#distisku#'
  20. </cfquery>
  21. <cfelse>
  22. </cfif>
  23. </cfoutput>
  24.  
  25. <cfoutput>
  26.  
  27. <cfif distiid EQ 'C00791'>
  28. <cfquery name="dandh" datasource="Distributor">
  29. Select unit_cost
  30. From dbo.tbl_dandh_price
  31. where dandh_item_num = '#distisku#'
  32. </cfquery>
  33. <cfelse>
  34.  
  35. </cfif>
  36. </cfoutput>
  37. </cfloop>
  38.  
  39. <cfoutput>
  40. <tr>
  41. <td class="#classStr#">
  42. #matchmanu.distisku#
  43. </td>
  44. <td class="#classStr#">
  45. <cfif distiid EQ 'C00030'>
  46. #techdata.COST#
  47. <cfelse>0</cfif>
  48. </td>
  49. <td class="#classStr#">
  50. <cfif distiid EQ 'C00055'>
  51. #synnex.Unit_Cost__wo_PromoRebate#
  52. <cfelse>0</cfif>
  53. </td>
  54. <td class="#classStr#">
  55. <cfif distiid EQ 'C00791'>
  56. #dandh.unit_cost#
  57. <cfelse>0</cfif>
  58. </td>
  59. </tr>
  60. </cfoutput>
  61.  
  62.  
  63.  
Thank you,
Rach
Feb 4 '09 #13
acoder
16,027 Expert Mod 8TB
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.
Feb 5 '09 #14
bonneylake
769 512MB
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?

Expand|Select|Wrap|Line Numbers
  1.  <cfoutput>
  2.  <tr>
  3. <td class="#classStr#">
  4.  #matchmanu.distisku#
  5.  </td>
  6. <td class="#classStr#">
  7.  <cfif distiid EQ 'C00030'>
  8.  #techdata.COST#
  9.  
  10. </td>
  11.  <td class="#classStr#">
  12.  <cfelseif distiid EQ 'C00055'>
  13.  #synnex.Unit_Cost__wo_PromoRebate#
  14.  
  15.  </td>
  16.  <td class="#classStr#">
  17.  <cfelseif distiid EQ 'C00791'>
  18.  #dandh.unit_cost#
  19.  <cfelse>0</cfif>
  20.  </td>
  21.  </tr>
  22.  </cfoutput>
Thank you :),
Rachel
Feb 5 '09 #15
acoder
16,027 Expert Mod 8TB
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.
Feb 5 '09 #16
bonneylake
769 512MB
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

Expand|Select|Wrap|Line Numbers
  1. <table width="95%" align="center">
  2. <thead>
  3. <tr>
  4.  
  5. <th>Manu Part number </th>
  6. <th>Techdata_Price</th>
  7. <th>Synnex Price</th>
  8. <th>D&H Price</th>
  9. </tr>
  10. </thead>
  11. <cfset alt=0>
  12. <cfset classStr="">
  13.  
  14.  
  15. <cfloop query="matchmanu">
  16.  
  17. <cfset t = 1>
  18. <cfif distiid EQ 'C00030'>
  19. <cfoutput>
  20. <cfquery name="techdata" datasource="Distributor#t#">
  21. Select  COST,DESCR
  22. From dbo.PRODUCTS
  23. where PART_NUM = '#distisku#'
  24. </cfquery> 
  25. </cfoutput>
  26. </cfif>
  27.  
  28.  
  29.  
  30.  
  31. <cfif distiid EQ 'C00055'>
  32. <cfoutput>
  33. <cfquery name="synnex" datasource="Distributor#t#">
  34. Select Unit_Cost__wo_PromoRebate
  35. From dbo.tbl_synnex_price
  36. where SYNNEX_SKU = '#distisku#'
  37. </cfquery>
  38. </cfoutput>
  39. <cfelse>
  40. </cfif>
  41.  
  42.  
  43.  
  44.  
  45. <cfif distiid EQ 'C00791'>
  46. <cfoutput>
  47. <cfquery name="dandh" datasource="Distributor#t#">
  48. Select unit_cost
  49. From dbo.tbl_dandh_price
  50. where dandh_item_num = '#distisku#'
  51. </cfquery>
  52. </cfoutput>
  53. <cfelse>
  54.  
  55. </cfif>
  56. <cfset t = t + 1>
  57.  
  58. <cfoutput>
  59. <cfif alt eq 0>
  60.     <cfset classStr="">
  61.     <cfset alt=1>
  62. <cfelse>
  63. <cfset alt=0>
  64. </cfif>
  65. <tr>
  66. <td class="#classStr#">
  67. #matchmanu.distisku#
  68. </td>
  69. <td class="#classStr#">
  70. <!---<cfif distiid EQ 'C00030'>--->
  71. #techdata.COST#
  72. <!---<cfelse>0</cfif>--->
  73. </td>
  74. <td class="#classStr#">
  75. <!---<cfif distiid EQ 'C00055'>--->
  76. #synnex.Unit_Cost__wo_PromoRebate#
  77. <!---<cfelse>0</cfif>--->
  78. </td>
  79. <td class="#classStr#">
  80. <!---<cfif distiid EQ 'C00791'>--->
  81. #dandh.unit_cost#
  82. <!---<cfelse>0</cfif>--->
  83. </td>
  84. </tr>
  85. </cfoutput>
  86. </cfloop>
  87.  

Thank you,
Rach
Feb 5 '09 #17
acoder
16,027 Expert Mod 8TB
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.
Feb 5 '09 #18
bonneylake
769 512MB
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
Expand|Select|Wrap|Line Numbers
  1. part number   the manufacturer         techdata price
  2. 636070             ABL Electronics       2.87
  3.  
  4. nothing displays for synnex or d and h cause get errors
  5.  
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
Expand|Select|Wrap|Line Numbers
  1. part number    manufacturer       techdat price     synnex price    d and h price
  2. 636070             ABL Electronics     131.25         134.34                98.93
  3.  
i don't know if that helps much, but thats all i currently get.

Thank you,
Rach
Feb 5 '09 #19
bonneylake
769 512MB
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:

Expand|Select|Wrap|Line Numbers
  1. <cfset i = 1>
  2.  
  3. <cfloop query="matchmanu">
  4.  
  5. {code}
  6.  
  7. <cfquery name=”techdata#i#”>
  8.  
  9. {code}
  10.  
  11. ….
  12.  
  13. <cfset i = i +1>
  14.  
  15. </cfloop>
  16.  

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

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="matchmanu" datasource="Configurator">
  2. select a.[hc part number]'hcpartnumber',a.mpn,a.Mfg,a.lastbuyquote,a.[description],b.prodid,c.distisku,c.distiid
  3. from [svr-htssqldb].configurator.dbo.[Master Parts List] a
  4. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_prod b
  5. on a.mpn=b.mfpn
  6. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_metamap c
  7. on b.prodid=c.prodid
  8. where a.lastbuyquote is not null and (c.distiid='c00030' or c.distiid='c00055' or c.distiid='c00791')
  9. </cfquery>
  10.  
  11. <cfoutput>
  12. <cfif alt eq 0>
  13.     <cfset classStr="">
  14.     <cfset alt=1>
  15. <cfelse>
  16. <cfset alt=0>
  17. </cfif>
  18. <tr>
  19. <td class="#classStr#">
  20. #matchmanu.hcpartnumber#
  21. </td>
  22. <td class="#classStr#">
  23. #matchmanu.distisku#
  24. </td>
  25. <td class="#classStr#">
  26. #matchmanu.Mfg#</td>
  27. <td class="#classStr#">
  28. #matchmanu.lastbuyquote#
  29. </td>
  30. <td class="#classStr#">
  31. <!---test--->
  32. </td>
  33. <td class="#classStr#">
  34. #evaluate("techdata#i#.COST")#
  35. </td>
  36. <td class="#classStr#">
  37. #evaluate("synnex#i#.Unit_Cost__wo_PromoRebate")#</td>
  38. <td class="#classStr#">
  39. #evaluate("dh#i#.unit_cost")#</td>
  40. <!---<td class="#classStr#">
  41. #matchmanu.DESCRIPTION#
  42. </td>
  43. <td class="#classStr#">
  44. #DESCR#
  45. </td>--->
  46. </tr>
  47.  
  48.  
  49. </cfoutput>
  50. <cfset i = i + 1>
  51. </cfloop>
  52. </table>
  53.  
Thank you,
Rach
Feb 5 '09 #20
bonneylake
769 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. <table width="95%" align="center">
  2. <thead>
  3. <tr>
  4. <th>How many</th>
  5. <th>HC part number</th>
  6. <th>Manu Part number </th>
  7. <th>MPL_manufacturer</th>
  8. <th>MPL.LastBuyQuote</th>
  9. <th>%price diff</th>
  10. <th>Techdata_Price</th>
  11. <th>Synnex Price</th>
  12. <th>D&H Price</th>
  13. <th>MPL_Description</th>
  14. <th>Techdata Description</th>
  15. </tr>
  16. </thead>
  17. <cfset alt=0>
  18. <cfset classStr="">
  19.  
  20.  
  21.  
  22. <cfset counter=0>
  23. <cfoutput>
  24. <cfset i = 1>
  25. <cfloop query="matchmanu">
  26. <cfif alt eq 0>
  27.     <cfset classStr="">
  28.     <cfset alt=1>
  29. <cfelse>
  30. <cfset alt=0>
  31. </cfif>
  32. <tr>
  33.  
  34. <td class="#classStr#">
  35. <cfset counter=counter +1>
  36. #counter#
  37. </td>
  38. <td class="#classStr#">
  39. #matchmanu.hcpartnumber#
  40. </td>
  41. <td class="#classStr#">
  42. #matchmanu.distisku#
  43. </td>
  44. <td class="#classStr#">
  45. #matchmanu.Mfg#</td>
  46. <td class="#classStr#">
  47. #matchmanu.lastbuyquote#
  48. </td>
  49. <td class="#classStr#">
  50. <!---test--->
  51. </td>
  52. <cfif distiid EQ 'C00030'>
  53. <cfquery name="techdata" datasource="Distributor">
  54. Select  COST,DESCR
  55. From dbo.PRODUCTS
  56. where PART_NUM = '#distisku#' 
  57. </cfquery> 
  58. <td class="#classStr#">
  59. #dollarformat(techdata.COST)#
  60. </td>                       
  61. <cfelse>
  62. <td>
  63. </td>
  64. </cfif>
  65.  
  66. <cfif distiid EQ 'C00055'>
  67. <cfquery name="synnex" datasource="Distributor">
  68. Select Unit_Cost__wo_PromoRebate
  69. From dbo.tbl_synnex_price
  70. where SYNNEX_SKU = '#distisku#'
  71. </cfquery>
  72. <td class="#classStr#">
  73. #synnex.Unit_Cost__wo_PromoRebate#</td>
  74. <cfelse>
  75. <td>
  76. </td>
  77. </cfif>
  78.  
  79. <cfif distiid EQ 'C00791'>
  80. <cfquery name="dh" datasource="Distributor">
  81. Select unit_cost
  82. From dbo.tbl_dandh_price
  83. where dandh_item_num = '#distisku#'
  84. </cfquery>
  85. <td class="#classStr#">
  86. #dh.unit_cost#</td>
  87. <cfelse>
  88. <td>
  89. </td>
  90. </cfif>
  91.  
  92. <td class="#classStr#">
  93. #matchmanu.DESCRIPTION#
  94. </td>
  95. <td class="#classStr#">
  96. #techdata.DESCR#
  97. </td>
  98. </tr>
  99.  
  100. <cfset i = i + 1>
  101. </cfloop>
  102. </cfoutput>
  103. </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
Feb 6 '09 #21
acoder
16,027 Expert Mod 8TB
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%.
Feb 7 '09 #22
bonneylake
769 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="pricedifference" datasource="Distributor">
  2. Select MIN(COST,Unit_Cost__wo_PromoRebate,unit_cost) as minCost,Cost
  3. From dbo.PRODUCTS,dbo.tbl_synnex_price,dbo.tbl_dandh_price,
  4. [svr-htssqldb].configurator.dbo.[Master Parts List]
  5. where (Cost > minCost) or (Cost + minCost) or (Cost -minCost)
  6. </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?

Expand|Select|Wrap|Line Numbers
  1. #matchmanu.[hc part number]#
  2. #evaluate(matchmanu.hc part number)#
  3. #evaluate(matchmanu.[hc part number])#
  4. #matchmanu["hc part number"]#
  5. #matchmanu.['hc part number']#
  6. #matchmanu.hc part number#
  7.  

Thank you,
Rach
Feb 7 '09 #23
acoder
16,027 Expert Mod 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. select [hc part number] as hcpartnumber, ...
Feb 7 '09 #24
bonneylake
769 512MB
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)
Expand|Select|Wrap|Line Numbers
  1. <cfquery name="matchmanu" datasource="Distributor">
  2. [usp_matching_vendor_distisku_from_MPL]
  3. </cfquery>
  4.  
  5. <table width="95%" align="center">
  6. <thead>
  7. <tr>
  8. <th>How many</th>
  9. <th>HC part number</th>
  10. <th>Manu Part number </th>
  11. <th>MPL_manufacturer</th>
  12. <th>MPL.LastBuyQuote</th>
  13. <th>Techdata_Price</th>
  14. <th>Synnex Price</th>
  15. <th>D&H Price</th>
  16. <th>%price diff</th>
  17. <th>MPL_Description</th>
  18. <th>Techdata Description</th>
  19. </tr>
  20. </thead>
  21. <cfset alt=0>
  22. <cfset classStr="">
  23.  
  24.  
  25.  
  26. <cfset counter=0>
  27. <cfoutput>
  28. <cfloop query="matchmanu">
  29. <cfif alt eq 0>
  30.     <cfset classStr="">
  31.     <cfset alt=1>
  32. <cfelse>
  33. <cfset alt=0>
  34. </cfif>
  35. <tr>
  36.  
  37. <td class="#classStr#">
  38. <cfset counter=counter +1>
  39. #counter#
  40. </td>
  41. <td class="#classStr#">
  42. <!---#matchmanu."["hc part number"]"#--->
  43. </td>
  44. <td class="#classStr#">
  45. #matchmanu.mpn#
  46. </td>
  47. <td class="#classStr#">
  48. #matchmanu.Mfg#</td>
  49. <td class="#classStr#">
  50. #matchmanu.lastbuyquote#
  51. </td>
  52. <cfquery name="techdata" datasource="Distributor">
  53. Select  COST
  54. From dbo.PRODUCTS
  55. where PART_NUM = '#techdatasku#' 
  56. </cfquery> 
  57. <td class="#classStr#">
  58. #dollarformat(techdata.COST)#
  59. </td>                       
  60. <cfquery name="synnx" datasource="Distributor">
  61. Select Unit_Cost__wo_PromoRebate
  62. From dbo.tbl_synnex_price
  63. where SYNNEX_SKU = '#synnxsku#'
  64. </cfquery>
  65. <td class="#classStr#">
  66. #dollarformat(synnx.Unit_Cost__wo_PromoRebate)#-</td>
  67. <cfquery name="dh" datasource="Distributor">
  68. Select unit_cost
  69. From dbo.tbl_dandh_price
  70. where dandh_item_num = '#dandhsku#'
  71. </cfquery>
  72. <td class="#classStr#">
  73. #dollarformat(dh.unit_cost)#</td>
  74. <td class="#classStr#">
  75.  
  76. </td>
  77. <td class="#classStr#">
  78. #matchmanu.DESCRIPTION#
  79. </td>
  80. <td class="#classStr#">
  81. <cfquery name="techdatad" datasource="Distributor">
  82. Select DESCR
  83. from dbo.PRODUCTS
  84. where PART_NUM = '#techdatasku#' 
  85. </cfquery>
  86. #techdatad.DESCR#
  87. </td>
  88. </tr>
  89. </cfloop>
  90. </cfoutput>
  91. </table>
Thank you,
Rachel
Feb 7 '09 #25
acoder
16,027 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. ((company price - manufacturer price)/manufacturer price) * 100
Feb 9 '09 #26
bonneylake
769 512MB
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).

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="dh" datasource="Distributor">
  2. Select unit_cost
  3. From dbo.tbl_dandh_price
  4. where dandh_item_num = '#dandhsku#'
  5. </cfquery>
  6. <td class="#classStr#">
  7. #dollarformat(dh.unit_cost)#</td>
  8. <td class="#classStr#">
  9. <cfquery name="mplpricecompare" datasource="Configurator">
  10. select Cost
  11. from dbo.[Master Parts List]
  12. </cfquery>
  13. (((#techdata.cost# - #mplpricecompare.Cost#)/#mplpricecompare.Cost#) * 100) and
  14. <!---(((#synnex.Unit_Cost__wo_PromoRebate# - #mplpricecompare.Cost#)/#mplpricecompare.Cost#) * 100)---> and
  15. (((#dh.unit_cost# - #mplpricecompare.Cost#)/#mplpricecompare.Cost#) * 100)
  16. </td>
Thank you,
Rach
Feb 9 '09 #27
acoder
16,027 Expert Mod 8TB
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.
Feb 9 '09 #28
bonneylake
769 512MB
Hey Acoder,

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

Expand|Select|Wrap|Line Numbers
  1. <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
Feb 9 '09 #29
acoder
16,027 Expert Mod 8TB
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.
Feb 9 '09 #30
bonneylake
769 512MB
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
Expand|Select|Wrap|Line Numbers
  1. <cfset test="#techdata.cost#">
Thank you,
Rach
Feb 9 '09 #31
acoder
16,027 Expert Mod 8TB
To compare, simply use gt (greater than) or lt (less than).
Expand|Select|Wrap|Line Numbers
  1. <cfif something gt somethingelse>
Feb 9 '09 #32
bonneylake
769 512MB
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.


Expand|Select|Wrap|Line Numbers
  1. <cfquery name="matchmanu" datasource="Distributor">
  2. [usp_matching_vendor_distisku_from_MPL]
  3. </cfquery>
  4.  
  5. <cfquery name="acompare" datasource="Configurator">
  6. select Cost
  7. from dbo.[Master Parts List]
  8. </cfquery>
  9.  
  10. <cfquery name="techdata" datasource="Distributor">
  11. Select  COST
  12. From dbo.PRODUCTS
  13. where PART_NUM = '#techdatasku#' 
  14. </cfquery> 
  15. <cfif #acompare.Cost# gt #techdata.COST#>
  16. <td class="#classStr#">
  17. <cfset #acompare.Cost# = #techdata.COST#>
  18. #dollarformat(techdata.COST)#
  19. </td>   
  20. </cfif>                    
  21.  
  22. <td>
  23. <!---<cfquery name="synnx" datasource="Distributor">
  24. Select Unit_Cost__wo_PromoRebate
  25. From dbo.tbl_synnex_price
  26. where SYNNEX_SKU = '#synnxsku#'
  27. </cfquery>---></td>
  28. <cfif #techdata.COST# gt #synnx.Unit_Cost__wo_PromoRebate#>
  29. <td class="#classStr#">
  30. <cfset #acompare.Cost# = #synnx.Unit_Cost__wo_PromoRebate#>
  31.  
  32. <!---#dollarformat(synnx.Unit_Cost__wo_PromoRebate)#---></td>
  33. </cfif>
  34.  
  35.  
  36. <cfquery name="dh" datasource="Distributor">
  37. Select unit_cost
  38. From dbo.tbl_dandh_price
  39. where dandh_item_num = '#dandhsku#'
  40. </cfquery>
  41. <cfif #synnx.Unit_Cost__wo_PromoRebate# gt #dh.unit_cost#>
  42. <td class="#classStr#">
  43. <cfset #acompare.Cost# = #dh.unit_cost#>
  44.  
  45. #dollarformat(dh.unit_cost)#</td>
  46. </cfif>
  47. <td class="#classStr#">
  48.  
  49. <!---(((#techdata.cost# - #mplpricecompare.Cost#)/#mplpricecompare.Cost#) * 100) and
  50. (((#dh.unit_cost# - #mplpricecompare.Cost#)/#mplpricecompare.Cost#) * 100---><!---)--->
  51.  
  52.  
  53.  
  54. </td>
  55. <td class="#classStr#">
  56. #matchmanu.DESCRIPTION#
  57. </td>
  58. <td class="#classStr#">
  59. <cfquery name="techdatad" datasource="Distributor">
  60. Select DESCR
  61. from dbo.PRODUCTS
  62. where PART_NUM = '#techdatasku#' 
  63. </cfquery>
  64. #techdatad.DESCR#
  65. </td>
  66. </tr>
  67.  
  68.  
  69.  
  70. <cfset i = i + 1>
  71. </cfloop>
  72. </cfoutput>
  73. </table>
  74.  
Thank you,
Rach
Feb 9 '09 #33
acoder
16,027 Expert Mod 8TB
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?
Feb 9 '09 #34
bonneylake
769 512MB
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?

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="techdata" datasource="Distributor">
  2. Select  COST
  3. From dbo.PRODUCTS
  4. where PART_NUM = '#techdatasku#' 
  5. </cfquery>
  6. <cfif acompare.cost gt techdata.COST>
  7. <td class="#classStr#">#dollarformat(techdata.COST)#
  8. </td>   
  9. </cfif>
  10.  
  11. <cfquery name="dh" datasource="Distributor">
  12. Select unit_cost
  13. From dbo.tbl_dandh_price
  14. where dandh_item_num = '#dandhsku#'
  15. </cfquery>
  16. <cfif dh.unit_cost gt dh.unit_cost>
  17. <td class="#classStr#">
  18. #dollarformat(dh.unit_cost)#</td>
  19. </cfif>
Thank you,
Rach
Feb 9 '09 #35
acoder
16,027 Expert Mod 8TB
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.
Feb 10 '09 #36
bonneylake
769 512MB
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

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="techdata" datasource="Distributor">
  2. Select  COST
  3. From dbo.PRODUCTS
  4. where PART_NUM = '#techdatasku#' 
  5. </cfquery>
  6. <cfif acompare.cost gt techdata.COST></cfif>
  7. <td class="#classStr#">#dollarformat(techdata.COST)#
  8. </td>  
  9. <cfquery name="dh" datasource="Distributor">
  10. Select unit_cost
  11. From dbo.tbl_dandh_price
  12. where dandh_item_num = '#dandhsku#'
  13. </cfquery>
  14. <cfif acompare.cost gt dh.unit_cost></cfif>
  15. <td class="#classStr#">
  16.  
  17. #dollarformat(dh.unit_cost)#</td>
  18.  
  19.  
Thank you,
Rach
Feb 10 '09 #37
acoder
16,027 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. <cfset lowest = techdata.cost>
  2. <cfif lowest gt synnex.cost>
  3.   <cfset lowest = synnex.cost>
  4. </cfif>
Feb 10 '09 #38
bonneylake
769 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="acompare" datasource="Configurator">
  2. select Cost
  3. from dbo.[Master Parts List]
  4. where [HC Part Number] = '#matchmanu.LastBuyQuote#'
  5. </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.

Expand|Select|Wrap|Line Numbers
  1. <cfset lowest = techdata.COST>
  2. <cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
  3. </cfif>
  4. <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
  5. <cfif lowest gt dh.unit_cost>
  6. </cfif>
  7. <td class="#classStr#">
  8. #lowest#
  9. </td>
  10.  
Thank you,
Rach
Feb 10 '09 #39
acoder
16,027 Expert Mod 8TB
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.
Feb 10 '09 #40
bonneylake
769 512MB
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?

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="synnx" datasource="Distributor">
  2. Select Unit_Cost__wo_PromoRebate
  3. From dbo.tbl_synnex_price
  4. where SYNNEX_SKU = '#synnxsku#'
  5. </cfquery>
  6. <td class="#classStr#">
  7. #dollarformat(synnx.Unit_Cost__wo_PromoRebate)#
  8. </td>
So for the lowest i should be doing something like the below right?And how would i check for 0.00?

Expand|Select|Wrap|Line Numbers
  1. <cfset lowest = techdata.COST>
  2. <cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
  3. <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
  4. <cfif lowest gt dh.unit_cost>
  5. </cfif>
  6. </cfif>
  7.  
Thank you,
Rach
Feb 10 '09 #41
acoder
16,027 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. <cfif dandh.cost neq 0.00>
  2. <!--- now make the comparison --->
  3. </cfif>
Feb 10 '09 #42
bonneylake
769 512MB
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. <cfset lowest = techdata.COST>
  3.  
  4. <cfif techdata.COST neq 0.00>
  5.  
  6. <cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
  7. <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
  8. </cfif>
  9. </cfif>
  10. <cfif dh.unit_cost neq 0.00>
  11.  
  12. <cfif lowest gt dh.unit_cost>
  13. </cfif>
  14. </cfif>
  15.  
Thank you,
Rach
Feb 10 '09 #43
acoder
16,027 Expert Mod 8TB
The idea is that if it's 0.00, you don't make a comparison, e.g.
Expand|Select|Wrap|Line Numbers
  1. <cfset lowest = techdata.COST>
  2. <cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0>
  3.     <cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
  4.         <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
  5.     </cfif>
  6. </cfif>
  7.  
Then you'd repeat lines 2-6 for d&h.
Feb 10 '09 #44
bonneylake
769 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. <cfset lowest = techdata.COST>
  2.  <cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0>
  3.      <cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
  4.          <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
  5.      </cfif>
  6.  </cfif>
  7.  <cfif dh.unit_cost neq 0.0>
  8.      <cfif lowest gt dh.unit_cost>
  9.          <cfset lowest = dh.unit_cost>
  10.      </cfif>
  11.  </cfif>
  12.  
Thank you,
Rach
Feb 10 '09 #45
acoder
16,027 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. <cfif techdata.cost neq 0.0>
  2.   <cfset lowest = techdata.cost>
  3. <cfelseif synex.cost neq 0.0>
  4.   <cfset lowest = synex.cost>
  5. <cfelse>
  6.   <cfset lowest = dh.cost>
  7. </cfif>
Feb 10 '09 #46
bonneylake
769 512MB
Hey Acoder,

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

Expand|Select|Wrap|Line Numbers
  1. <cfif techdata.cost neq 0.00>
  2.  <cfset lowest = techdata.COST>
  3.   <!---<cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0>--->
  4.       <cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
  5. <cfelseif synex.cost neq 0.00>
  6.           <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
  7.       </cfif>
  8.   <!---</cfif>--->
  9.  <!--- <cfif dh.unit_cost neq 0.0>--->
  10.       <cfif lowest gt dh.unit_cost>
  11. <cfelse>
  12.           <cfset lowest = dh.unit_cost>
  13.       <!---</cfif>--->
  14.   </cfif>
  15.   </cfif>
  16.  
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
Feb 10 '09 #47
acoder
16,027 Expert Mod 8TB
No, that code wasn't to overwrite the previous code. It was to replace the first line only.
Feb 10 '09 #48
bonneylake
769 512MB
Hey Acoder,

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

Expand|Select|Wrap|Line Numbers
  1.  <cfif techdata.cost neq 0.00>
  2.  <cfset lowest = techdata.COST>
  3.    <cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0>
  4.        <cfif lowest gt synnx.Unit_Cost__wo_PromoRebate>
  5.  <cfelseif synex.cost neq 0.00>
  6.            <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
  7.        </cfif>
  8.    </cfif>
  9.   <cfif dh.unit_cost neq 0.0>--->
  10.        <cfif lowest gt dh.unit_cost>
  11.  <cfelse>
  12.            <cfset lowest = dh.unit_cost>
  13.        </cfif>
  14.    </cfif>
  15.    </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
Feb 10 '09 #49
acoder
16,027 Expert Mod 8TB
Try something like this:
Expand|Select|Wrap|Line Numbers
  1. <cfif techdata.cost neq 0.00>
  2.   <cfset lowest = techdata.COST>
  3. <cfelseif synnx.Unit_Cost__wo_PromoRebate neq 0.00>
  4.   <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
  5. <cfelse>
  6.    <cfset lowest = dh.unit_cost>
  7. </cfif>
  8. <cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0 and lowest gt synnx.Unit_Cost__wo_PromoRebate>
  9.   <cfset lowest = synnx.Unit_Cost__wo_PromoRebate>
  10. </cfif>
  11. <cfif dh.unit_cost neq 0.0 and lowest gt dh.unit_cost>
  12.    <cfset lowest = dh.unit_cost>
  13. </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.
Feb 11 '09 #50

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Bill Sneddon | last post: by
6 posts views Thread by martin | last post: by
2 posts views Thread by John Lau | last post: by
23 posts views Thread by Russ Chinoy | last post: by
15 posts views Thread by Giff | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.