473,376 Members | 1,085 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,376 software developers and data experts.

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

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

Similar topics

2
by: Bill Sneddon | last post by:
Can any one tell me how to output the following string? <%response.write "<tr><td><a href=""file://SERVER/mmlogs/TNAME" & yearmonth & """>"& "MYJUNK" & "</a><BR></td></tr>" %> ...
5
by: Marcel Akkerman | last post by:
Hi, Does anyone have a clue how to reduce the number of nodes using XSLT? When outputing all nodes in order I could just use <xsl:for-each select="name"> But what if I, besides sorting and...
1
by: Richard Hollenbeck | last post by:
I noticed I can't push a value into a text box by saying something like, "txtThisTextBox = intSomeVariable * 0.5" because I get an run-time error saying I can't assign a value to this object....
6
by: martin | last post by:
Hi, I am a web page and a web user control. My web user control is placed in my web page using the following directive <%@ Register TagPrefix="uc1" TagName="Header"...
2
by: John Lau | last post by:
Hi, Is there documentation that talks about the page lifecycle, the lifecycle of controls on the page, and the rendering of inline code, in a single document? Thanks, John
23
by: Russ Chinoy | last post by:
Hi, This may be a totally newbie question, but I'm stumped. If I have a function such as: function DoSomething(strVarName) { ..... }
8
by: fernandezr | last post by:
I would like to use a user control as a template inside a repeater. Some of the fields in the control should be hidden depending on whether or not there is data. I'm still a ASP .Net newbie so the...
4
by: eric.goforth | last post by:
Hello, I'm getting: msxml3.dll (0x80004005) Error while parsing "file:///c:/WWWROOT/includes/Inc.xsl". A name was started with an invalid character. When I put in the "{$sDisabledString}"
15
by: Giff | last post by:
Hi again, I need a suggestion. Right now I am developing an application and I want to be able to output on screen some variables. Anyway, I want to remove these output operations when passing...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.