Solved

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

Posted on 2004-10-20
15
268 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
  • 8
  • 4
  • 3
15 Comments
 
LVL 8

Expert Comment

by:MartinCMS
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 5

Expert Comment

by:kkhipple
Comment Utility
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
Comment Utility
that goes the same with any other values that are numeric and not varchar
0
 
LVL 8

Author Comment

by:JRockFL
Comment Utility
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
Comment Utility
[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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:MartinCMS
Comment Utility
How about....

#numberformat(qRes.Total)#
0
 
LVL 8

Author Comment

by:JRockFL
Comment Utility
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
Comment Utility
NumberFormat doesnt work either
0
 
LVL 5

Expert Comment

by:kkhipple
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now