469,326 Members | 1,530 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
109 11557
bonneylake
769 512MB
Hey Acoder,

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

Thank you,
Rach
Feb 16 '09 #101
acoder
16,027 Expert Mod 8TB
Well, if it's 0.39xxxxx (a whole lot of numbers), then you can't change it to 0.039. I assume you meant 0.39.

Decimalformat would work except that it won't round, so
Expand|Select|Wrap|Line Numbers
  1. <cfset pricediff = round(pricediff * 100)/100>
might be a better bet.

PS. it is rare to have such a huge price difference, so maybe you should be testing with something more "average".
Feb 16 '09 #102
bonneylake
769 512MB
Hey Acoder,


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

Thank you,Thank you :),
Rach
Feb 16 '09 #103
acoder
16,027 Expert Mod 8TB
I'm glad it did. So is that the thread finished?
Feb 16 '09 #104
bonneylake
769 512MB
Hey Acoder,

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

Expand|Select|Wrap|Line Numbers
  1.  <cfset lowest = 0>
  2.  <cfif techdata.recordcount neq 0 and techdata.COST neq 0.00> 
  3.      <cfset lowest = techdata.COST>
  4.       </cfif>
  5.     <cfif synnx.recordcount neq 0 and synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
  6.       <cfif lowest eq 0>
  7.         <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  8.       <cfelseif lowest gt synnx.Unit_Cost__wo_PromoRebate>
  9.         <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  10.      </cfif>
  11.    </cfif>
  12.  <cfif dh.recordcount neq 0 and dh.unit_cost neq 0.00> 
  13.       <cfif lowest eq 0>
  14.         <cfset lowest = dh.unit_cost> 
  15.       <cfelseif lowest gt dh.unit_cost>
  16.         <cfset lowest = dh.unit_cost> 
  17.      </cfif>
  18.    </cfif>
  19.  
Thank you :),
Rach
Feb 16 '09 #105
acoder
16,027 Expert Mod 8TB
Look for -1 somewhere in the code probably after the code you've posted.

Note that lines 6-10 could be combined to avoid repetition, e.g.
Expand|Select|Wrap|Line Numbers
  1. <cfif lowest eq 0 or lowest gt synnx.Unit_Cost__wo_PromoRebate>
  2.     <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  3. </cfif>
likewise for the d&h part.
Feb 17 '09 #106
bonneylake
769 512MB
Hey Acoder,

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

Expand|Select|Wrap|Line Numbers
  1. <table width="85%" align="center">
  2. <thead>
  3. <tr>
  4. <th>HC Part Number</th>
  5. <th>Manu Part Number </th>
  6. <th>MPL manufacturer</th>
  7. <th>MPL LastBuyQuote</th>
  8. <th>Techdata Price</th>
  9. <th>Synnex Price</th>
  10. <th>D&H Price</th>
  11. <th>%price diff</th>
  12. <th>MPL Description</th>
  13. <th>Techdata Description</th>
  14. </tr>
  15. </thead>
  16.  
  17.  
  18.  
  19. <cfoutput>
  20.  
  21. <cfset alt=0>
  22. <cfset classStr="">
  23.  
  24. <cfloop query="matchmanu">
  25. <cfif alt eq 0>
  26.     <cfset classStr="">
  27.     <cfset alt=1>
  28. <cfelse>
  29.     <cfset classStr="alt">
  30.     <cfset alt=0>
  31. </cfif>
  32.  
  33. <tr>
  34.  
  35. <td class="#classStr#">
  36. #matchmanu['hc part number'][matchmanu.currentrow]#
  37. </td>
  38. <td class="#classStr#">
  39. #matchmanu.mpn#
  40. </td>
  41. <td class="#classStr#">
  42. #matchmanu.Mfg#
  43. </td>
  44. <td class="#classStr#">
  45. #decimalFormat(matchmanu.lastbuyquote)#
  46. </td>
  47. <cfquery name="techdata" datasource="Distributor">
  48. Select  COST
  49. From dbo.PRODUCTS
  50. where PART_NUM = '#techdatasku#' 
  51. </cfquery>
  52. <td class="#classStr#">
  53. #dollarformat(techdata.COST)#
  54. </td>         
  55. <cfquery name="synnx" datasource="Distributor">
  56. Select Unit_Cost__wo_PromoRebate
  57. From dbo.tbl_synnex_price
  58. where Unit_Cost__wo_PromoRebate is not null and (SYNNEX_SKU = '#val(synnxsku)#')
  59. </cfquery>
  60. <td class="#classStr#">
  61. #dollarformat(synnx.Unit_Cost__wo_PromoRebate)#
  62. </td>
  63. <cfquery name="dh" datasource="Distributor">
  64. Select unit_cost
  65. From dbo.tbl_dandh_price
  66. where dandh_item_num = '#dandhsku#'
  67. </cfquery>
  68. <td class="#classStr#">
  69. #dollarformat(dh.unit_cost)#
  70. </td>
  71.  
  72.  <cfset lowest = 0>
  73.  <cfif techdata.recordcount neq 0 and techdata.COST neq 0.00> 
  74.      <cfset lowest = techdata.COST>
  75.  </cfif>
  76.    <cfif synnx.recordcount neq 0 and synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
  77.       <cfif lowest eq 0 or lowest gt synnx.Unit_Cost__wo_PromoRebate>
  78.       <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  79.       </cfif>
  80.    </cfif>
  81.  <cfif dh.recordcount neq 0 and dh.unit_cost neq 0.00> 
  82.        <cfif lowest eq 0 or lowest gt dh.unit_cost>
  83.        <cfset lowest = dh.unit_cost> 
  84.        </cfif>
  85.    </cfif>
  86.  
  87.  
  88. <td class="#classStr#">
  89. <cfset cost = #matchmanu.lastbuyquote#>
  90. <cfif IsNumeric(cost) and #val(cost)# neq 0.0>
  91. <cfset pricediff = ((#lowest# - cost)/cost)>
  92. <cfif pricediff gt 0.03 or pricediff lt -0.03>
  93. <cfset pricediff = round(pricediff * 100)/100>
  94. #pricediff#
  95. <cfelse>
  96. N/A
  97. </cfif>
  98. </cfif>
  99.  
  100. </td>
  101. <td class="#classStr#">
  102. #matchmanu.DESCRIPTION#
  103. </td>
  104. <td class="#classStr#">
  105. <cfquery name="techdatad" datasource="Distributor">
  106. Select DESCR
  107. from dbo.PRODUCTS
  108. where PART_NUM = '#techdatasku#' 
  109. </cfquery>
  110. #techdatad.DESCR#
  111. </td>
  112. </tr>
  113. </cfloop>
  114. </cfoutput>
  115. </table>
Thank you,
Rach
Feb 17 '09 #107
acoder
16,027 Expert Mod 8TB
It'll be line 91:
Expand|Select|Wrap|Line Numbers
  1. <cfset pricediff = ((lowest - cost)/cost)>
which is causing the problem. If, for example, cost is 37.49 then pricediff would end up being 0 - 37.49/37.49 which equals -37.49/37.49 = -1!

To avoid that, add a condition on line 90:
Expand|Select|Wrap|Line Numbers
  1. <cfif lowest neq 0 and IsNumeric(cost) and val(cost) neq 0.0>
Feb 17 '09 #108
bonneylake
769 512MB
Hey Acoder,

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

Thank you,Thank you!!! :)
Rach
Feb 17 '09 #109
acoder
16,027 Expert Mod 8TB
Glad to help! Until next time... ;)
Feb 17 '09 #110

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 suresh191 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | 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.