Solved

Error converting data type varchar to numeric with Outer Join and CFLOOP

Posted on 2004-10-20
15
273 Views
Last Modified: 2013-12-24
I'm trying to insert data into the database, but I get Error converting data type varchar to numeric.

The queries work fine. In the table squareinch2, if i define it as varchar the data will insert. But this gives me problems later on.

<cfquery name="qCats" datasource="#request.datasource#" username="#request.username#" password="#request.password#">
SELECT monthlyspecials.ad_code, monthlyspecials.page_number, monthlyspecials.ggroup, monthlyspecials.location, monthlyspecials.square_inches, monthlyspecials.product_name, IMINVLOC_SQL.prod_cat, monthlyspecials.new_product, monthlyspecials.new_photo, monthlyspecials.advertisement, monthlyspecials.cover_feature, monthlyspecials.back_cover_feature,  monthlyspecials.small_pict_name,  monthlyspecials.large_pict_name
FROM monthlyspecials
LEFT OUTER JOIN
IMINVLOC_SQL ON monthlyspecials.item_number = IMINVLOC_SQL.Item_No
WHERE IMINVLOC_SQL.loc = 'TP'
ORDER BY monthlyspecials.page_number
</cfquery>

<!--- Publication Admin Area --->
<cfset pp108 = 539.59>

<!--- inches per page --->
<cfset ipp = 93.5>

<cfoutput query="qCats">
<cfquery name="qRes" datasource="#request.datasource#" username="#request.username#" password="#request.password#">
SELECT SUM (((OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Price) * ((100 - OELINHST_SQL.Discount_Pct) / 100))) As Total, SUM(OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Cost) As Cost
FROM OEHDRHST_SQL, OELINHST_SQL
WHERE OEHDRHST_SQL.Ord_No = OELINHST_SQL.Ord_No
AND OEHDRHST_SQL.Ord_dt >=20040801
AND OEHDRHST_SQL.Ord_dt <=20040831
AND OELINHST_SQL.prod_cat = '#qCats.prod_cat#'
</cfquery>

<cfset sicost = pp108 / ipp * square_inches>
<cfquery name="qInsert" datasource="#request.datasource#" username="#request.username#" password="#request.password#">
INSERT INTO squareinch2
(product, ggroup, prod_cat, page, location, squareinch, squareinchcost, sales, adcode, new_product, new_photo, advertisement, cover_feature, back_cover_feature, small_pict_name, large_pict_name, cost) VALUES
('#product_name#', '#ggroup#', '#prod_cat#', '#page_number#', '#location#', '#square_inches#', '#sicost#', '#qRes.Total#', '#ad_code#', '#new_product#', '#new_photo#', '#advertisement#', '#cover_feature#', '#back_cover_feature#', '#small_pict_name#', '#large_pict_name#', '#qRes.cost#')
</cfquery>

</cfoutput>
0
Comment
Question by:JRockFL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
15 Comments
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12362500
try taking out the single quote around the data in the Values if the field you're trying to insert is numeric.

i.e.
if field (page) is numeric

Insert into squareinch2 (page)
Values(#page_number#)  

--- instead of
Values('#page_number#')  
0
 
LVL 8

Author Comment

by:JRockFL
ID: 12362539
I have already tried that and I get

[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near ','.
0
 
LVL 8

Author Comment

by:JRockFL
ID: 12362592
I have all the fields set to varchar except for the column sales.
the value for sales is '#qRes.Total#'

qRes.Total comes from this...and it works fine...all these fields are numeric
SUM (((OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Price) * ((100 - OELINHST_SQL.Discount_Pct) / 100))) As Total


If I change '#qRes.Total#' to '5' the query executes fine.



0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 5

Expert Comment

by:kkhipple
ID: 12362605
try this.. what i did was removed the single quotes around #sicost#

<cfquery name="qInsert" datasource="#request.datasource#" username="#request.username#" password="#request.password#">
INSERT INTO squareinch2
(product, ggroup, prod_cat, page, location, squareinch, squareinchcost, sales, adcode, new_product, new_photo, advertisement, cover_feature, back_cover_feature, small_pict_name, large_pict_name, cost) VALUES
('#product_name#', '#ggroup#', '#prod_cat#', '#page_number#', '#location#', '#square_inches#',#sicost#, '#qRes.Total#', '#ad_code#', '#new_product#', '#new_photo#', '#advertisement#', '#cover_feature#', '#back_cover_feature#', '#small_pict_name#', '#large_pict_name#', '#qRes.cost#')
</cfquery>
0
 
LVL 5

Expert Comment

by:kkhipple
ID: 12362615
that goes the same with any other values that are numeric and not varchar
0
 
LVL 8

Author Comment

by:JRockFL
ID: 12362647
I tried removing the single quotes and didnt work. Right now all the fields in the db are set to varchar except for sales. Sales is set to integer. The value is '#qRes.Total#' that gets inserted.
I removed the single quotes and still get an error.
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12362654
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near ','

-- seems to be a totally different error then converting data type varchar to numeric
-- check to see if you have an extra ',' some where

Like I said before, remove single quote around the data if field data type is numeric
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12362661
How about....

#numberformat(qRes.Total)#
0
 
LVL 8

Author Comment

by:JRockFL
ID: 12362674
I tried again, this is the code now...

INSERT INTO squareinch2
(product, ggroup, prod_cat, page, location, squareinch, squareinchcost, sales, adcode, new_product, new_photo, advertisement, cover_feature, back_cover_feature, small_pict_name, large_pict_name, cost) VALUES
('#product_name#', '#ggroup#', '#prod_cat#', '#page_number#', '#location#', '#square_inches#', '#sicost#', #qRes.Total#, '#ad_code#', '#new_product#', '#new_photo#', '#advertisement#', '#cover_feature#', '#back_cover_feature#', '#small_pict_name#', '#large_pict_name#', '#qRes.cost#')

Gives me this error....
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near ','
0
 
LVL 8

Author Comment

by:JRockFL
ID: 12362698
NumberFormat doesnt work either
0
 
LVL 5

Expert Comment

by:kkhipple
ID: 12362704
if its saying line 3.. then most probably it has to do with your first query

<cfquery name="qCats" datasource="#request.datasource#" username="#request.username#" password="#request.password#">
SELECT monthlyspecials.ad_code, monthlyspecials.page_number, monthlyspecials.ggroup, monthlyspecials.location, monthlyspecials.square_inches, monthlyspecials.product_name, IMINVLOC_SQL.prod_cat, monthlyspecials.new_product, monthlyspecials.new_photo, monthlyspecials.advertisement, monthlyspecials.cover_feature, monthlyspecials.back_cover_feature,  monthlyspecials.small_pict_name,  monthlyspecials.large_pict_name
FROM monthlyspecials
LEFT OUTER JOIN
IMINVLOC_SQL ON monthlyspecials.item_number = IMINVLOC_SQL.Item_No
WHERE IMINVLOC_SQL.loc = 'TP'
ORDER BY monthlyspecials.page_number
</cfquery>



somewhere you are select the wrong columns, try

<cfquery name="qCats" datasource="#request.datasource#" username="#request.username#" password="#request.password#">
SELECT *
FROM monthlyspecials
LEFT OUTER JOIN
IMINVLOC_SQL ON monthlyspecials.item_number = IMINVLOC_SQL.Item_No
WHERE IMINVLOC_SQL.loc = 'TP'
ORDER BY monthlyspecials.page_number
</cfquery>
0
 
LVL 8

Author Comment

by:JRockFL
ID: 12362744
Tried that....doesnt work either. I'm not sure why it says line 3...this is the entire message...

[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near ','.  
 
The error occurred in C:\Inetpub\wwwroot\development\squareinch\qry_data.cfm: line 31
 
29 : INSERT INTO squareinch2
30 : (product, ggroup, prod_cat, page, location, squareinch, squareinchcost, sales, adcode, new_product, new_photo, advertisement, cover_feature, back_cover_feature, small_pict_name, large_pict_name, cost) VALUES
31 : ('#product_name#', '#ggroup#', '#prod_cat#', '#page_number#', '#location#', '#square_inches#', '#sicost#', #qRes.Total#, '#ad_code#', '#new_product#', '#new_photo#', '#advertisement#', '#cover_feature#', '#back_cover_feature#', '#small_pict_name#', '#large_pict_name#', '#qRes.cost#')
32 : </cfquery>
33 :
 
0
 
LVL 8

Author Comment

by:JRockFL
ID: 12362818
It must have something to do with the CFLOOP...if i change to orginal code to...
it works fine..

<cfquery name="qRes" datasource="#request.datasource#" username="#request.username#" password="#request.password#">
SELECT SUM (((OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Price) * ((100 - OELINHST_SQL.Discount_Pct) / 100))) As Total, SUM(OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Cost) As Cost
FROM OEHDRHST_SQL, OELINHST_SQL
WHERE OEHDRHST_SQL.Ord_No = OELINHST_SQL.Ord_No
AND OEHDRHST_SQL.Ord_dt >=20040801
AND OEHDRHST_SQL.Ord_dt <=20040831
AND OELINHST_SQL.prod_cat = 'R82'
</cfquery>


<cfquery name="qInsert" datasource="#request.datasource#" username="#request.username#" password="#request.password#">
INSERT INTO squareinch2
(sales) VALUES
('#qRes.Total#')
</cfquery>
0
 
LVL 8

Author Comment

by:JRockFL
ID: 12362913
I found the problem...

The prod_cat K90 returns 2 null values...

SELECT     SUM((OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Price) * ((100 - OELINHST_SQL.Discount_Pct) / 100)) AS Total,
                      SUM(OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Cost) AS Cost
FROM         OEHDRHST_SQL INNER JOIN
                      OELINHST_SQL ON OEHDRHST_SQL.Ord_No = OELINHST_SQL.Ord_No
WHERE     (OEHDRHST_SQL.Ord_Dt >= 20040801) AND (OEHDRHST_SQL.Ord_Dt <= 20040831) AND (OELINHST_SQL.Prod_Cat = 'K90')
0
 
LVL 8

Accepted Solution

by:
MartinCMS earned 125 total points
ID: 12362930
I don't think it is from your loop!  Perhalf you don't want to use the left outer join in the first query.


LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

you're using AND OELINHST_SQL.prod_cat = '#qCats.prod_cat#' from the first query, but
When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table. This will clause a hick-up from your second query.

do you really need to use left outer join?  Can you use inner join instead?
0

Featured Post

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question