Solved

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

Posted on 2004-10-20
15
271 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
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

839 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