Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-10-20
15
Medium Priority
?
278 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
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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 500 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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

580 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