We help IT Professionals succeed at work.

SQL union statment error

ubsmail
ubsmail asked
on
237 Views
Last Modified: 2012-05-11
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

Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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?
SharathData Engineer
CERTIFIED EXPERT

Commented:
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.
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.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
It comes due to stock.price1. Please male sire the field datatype is numeric
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.