Solved

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

Posted on 2004-10-20
15
269 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

12 Experts available now in Live!

Get 1:1 Help Now