• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

SQL union statment error

When I run the attached query I get the following error

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

I believe in the past I have used:

CONVERT(CHAR(40),mycolumn)

If this is the to correct the error, is there a way to distinguish which fields need to be converted?


select

ROUND((stock.price1/.9),0) + .99 as AmazonPrice,
CASE when stock.size_color=1 then 'C'
   
  when stock.size_color=0 then 'N'
END as IsChild,
prodtype.search_Desc as ProdTypeText,
mfg.MFG,
LEFT(stock.number,10) as StyleNo,
stock.inetcprice as ComparePrice,
Stock.Number,
stock.desc1 as Title,
stock.units as InStock,
Price1 as Price,
prodtype.ShareaSaleCat1,
prodtype.ShareaSaleCat2,
stock.UnitWeight,
prodtype.sz_id as ShopzillaCatID,
stock.desc2 as OptionText,
stock.uncost as WholesalePrice,
stock.upccode as UPC



from stock
join mfg on mfg.num_code=LEFT(stock.number,3)
join prodtype on prodtype.prodtype=stock.assoc
join stockinf on stockinf.number=stock.number

where NOT (stock.units = 0 AND stock.discont = 1) and stock.number not like 'P-%' and stock.number not like '9-%'


union


select distinct 


ROUND((stock.price1/.9),0) + .99 as AmazonPrice,
'P' IsChild,
prodtype.search_Desc as ProdTypeText,
mfg.MFG,
LEFT(stock.number,10) as StyleNo,
stock.inetcprice as ComparePrice,
LEFT(stock.number,10) as Number,
stock.desc1 as Title,
'' as InStock,
Price1 as Price,
prodtype.ShareaSaleCat1,
prodtype.ShareaSaleCat2,
'' as UnitWeight,
prodtype.sz_id as ShopzillaCatID,
'' as OptionText,
'' as WholesalePrice,
'' as UPC


from stock
join mfg on mfg.num_code=LEFT(stock.number,3)
join prodtype on prodtype.prodtype=stock.assoc


where NOT (stock.units = 0 AND stock.discont = 1) and stock.size_color=1 and stock.number not like 'P-%' and stock.number not like '9-%'

order by stock.number

Open in new window

0
ubsmail
Asked:
ubsmail
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>If this is the to correct the error,
yes

>is there a way to distinguish which fields need to be converted?
either you just compare all the fields that are either "UNIONed" or JOINed together, or work "by elimination".
comparing all fields is though the recommended (aka professional) version, obviously, as it will eliminate "sleeping" problems.

0
 
dirknibleckCommented:
It seems to me that this is probably the offender:

LEFT(stock.number,10) as Number

In the first query you simply refer to stock.number as number, why are you running LEFT against it in the second?
0
 
SharathData EngineerCommented:
1) Try running each query individually. If you get error in one of the queries, you can concentrate on that to fix the issue.
2) Try commenting all the columns listed in SELECT clause. If you get error, the error may be in the JOIN conditions.
3) Try uncommenting columns in the SELECT clause one by one, so that you can get the erroneous partof the query.
0
 
kotukunuiCommented:
Are you able to give us the column datatype definition of the "stock" table? The column datatypes will make it a lot easier to determine where the issue is. e.g. Having a column in the "stock" table called "number" that looks like it is actually a varchar is quite confusing when we don't know the actual underlying datatypes.
0
 
Alpesh PatelAssistant ConsultantCommented:
It comes due to stock.price1. Please male sire the field datatype is numeric
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now