Msg 8114, Level 16, State 5, Line 1 2

[Qmoto]

Hello Everyone,

I'm trying to combine a view for current orders, and a view for historical orders. The view is created without error, but when I try to query the view I get the following error.

The interesting thing is that if I create the views (current / historical) seperatly the query works without error????


The error I get is this:

[query used]
select top 500 * from order_data

[error returned]
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

 

[jbenson001]

what is the code that creates the view?

 

[Qmoto]

Sorry, I wouldn't normally post this much code, but you asked for it... :0) It's fairly long, but here goes:

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
   WHERE TABLE_NAME = 'ORDER_DATA_C')
DROP VIEW ORDER_DATA_C;
GO

CREATE VIEW ORDER_DATA_C
AS
SELECT /* OPEN ORDERS */
'DUBUQUE'		AS BU_KEY
,CASE 
	WHEN (MBC6.C6ACDT = '0' OR MBC6.C6ACDT IS NULL) THEN '0'
	WHEN MBC6.C6ACDT <> '0' THEN CONVERT(VarChar(10), Convert(DateTime, Convert(VarChar(8), MBC6.C6ACDT + 19000000)), 101)
 END				AS ORDER_DATE
,MBC6.C6CVNB	AS ORDER_NUMBER
,MBC6.C6CANB	AS BU_ACCOUNT_KEY
,MBC6.C6CANB	AS SOLDTO_KEY
,MBC6.C6B9CD	AS SHIPTO_KEY
,MBC6.C6CANB	AS BILLTO_KEY
,MBC6.C6CHNB	AS SALESREP_KEY
,CASE
	WHEN MBBF.CUSCL = 'XI'
		THEN MBBF.TERRN
	ELSE ZIPF.ZTERNO
END				AS SALES_TERRITORY_KEY
,'USD'			AS CURRENCY_CD
,MBCD.CDFCNB	AS LINE_NUMBER
,MBCD.CDAITX	AS PRODUCT_KEY
,CAST( MBCD.CDDOVA AS NUMERIC(10,2) )	AS ORDER_UNIT_PRICE
,CAST( MBCD.CDDOVA AS NUMERIC(10,2) )	AS LIST_PRICE
--	,CAST( MBCD.? AS NUMERIC(10,2) ) AS COST
,'NULL'			AS COST
,'S'				AS COST_TYPE
,'USD'			AS COST_CURRENCY_CD
,CAST((MBCD.CDDOVA * MBCD.CDACQT - MBCD.CDDPVA)	AS NUMERIC(10,2))		AS DISCOUNT_AMOUNT
,CAST( MBCD.CDACQT AS NUMERIC(7,0) )	AS ORDER_LINE_ITEM_QTY
,MBCD.CDDHCD	AS UNIT_OF_MEASURE
,CAST( MBCD.CDDPVA AS NUMERIC(10,2) )	AS ORDER_LINE_ITEM_AMT
,MBCJ.CJCBTX	AS PURCHASE_ORDER
,CASE 
	WHEN (MBC6.C6ACDT = '0' OR MBC6.C6ACDT IS NULL) THEN '0'
	WHEN MBC6.C6ACDT <> '0' THEN CONVERT(VarChar(10), Convert(DateTime, Convert(VarChar(8), MBC6.C6ACDT + 19000000)), 101)
 END				AS TRANSACTION_DATE

FROM
/* BEGIN BASE QUERY JOINS */
MBC6REP MBC6
LEFT JOIN
	MBCDREP MBCD
	ON
	MBC6.C6AENB = MBCD.CDAENB	AND
	MBC6.C6DCCD = MBCD.CDDCCD	AND
	MBC6.C6CVNB = MBCD.CDCVNB
LEFT JOIN
	MBCJCPP MBCJ
	ON
	MBC6.C6AENB = MBCJ.CJAENB	AND
	MBC6.C6DCCD = MBCJ.CJDCCD	AND
	MBC6.C6CVNB = MBCJ.CJCVNB

/*
BEGIN TERRITORY LOGIC
IF COSTOMER CLASS = 'XI'
*/
LEFT JOIN
	MBBFREP MBBF
	ON
	MBC6.C6AENB = MBBF.COMNO	AND
	MBC6.C6CANB = MBBF.CUSNO

/* IF CUSTOMER_CLASS <> 'XI' */
LEFT JOIN
	MBDEREP MBDE
	ON
	MBC6.C6AENB = MBDE.DEAENB	AND
	MBC6.C6CANB = MBDE.DECANB	AND
	MBC6.C6B9CD = MBDE.DEB9CD
LEFT JOIN
	MBALREP MBAL
	ON
	MBDE.DEAENB = MBAL.ALAENB	AND
	MBDE.DECANB = MBAL.ALCANB	AND
	MBDE.DECUCD = MBAL.ALCUCD
LEFT JOIN
	ZIPFILE ZIPF
	ON
	MBAL.ALCPTX = ZIPF.CITY		AND
	LEFT(MBAL.ALCVCD, 5) = ZIPF.ZIP5

WHERE
MBC6.C6DCCD = '1'
--	MBC6.C6ACDT >= '1050101'

UNION


SELECT /* HISTORICAL ORDERS */
'DUBUQUE'		AS BU_KEY
,CASE 
	WHEN (MBBW.BWACDT = '0' OR MBBW.BWACDT IS NULL) THEN '0'
	WHEN MBBW.BWACDT <> '0' THEN CONVERT(VarChar(10), Convert(DateTime, Convert(VarChar(8), MBBW.BWACDT + 19000000)), 101)
 END				AS ORDER_DATE
,MBBW.BWCVNB	AS ORDER_NUMBER
,MBBW.BWCANB	AS BU_ACCOUNT_KEY
,MBBW.BWCANB	AS SOLDTO_KEY
,MBDH.DHB9CD	AS SHIPTO_KEY
,MBBW.BWCANB	AS BILLTO_KEY
,'NULL'			AS SALESREP_KEY
,CASE
	WHEN MBBF.CUSCL = 'XI' -- CUSTOMER CLASS CODE
		THEN MBBF.TERRN
	ELSE ZIPF.ZTERNO
END				AS SALES_TERRITORY_KEY
,'USD'			AS CURRENCY_CD
,MBDD.DDFCNB	AS LINE_NUMBER
,MBDD.DDAITX	AS PRODUCT_KEY
,CAST( MBDD.DDDTVA AS NUMERIC(10,2) )	AS ORDER_UNIT_PRICE
,CAST( MBDD.DDDTVA AS NUMERIC(10,2) )	AS SELLING_PRICE
,CAST( MBDD.DDAAF5 AS NUMERIC(10,2) )	AS COST
,'S'				AS COST_TYPE
,'USD'			AS COST_CURRENCY_CD
,CAST((MBDD.DDDTVA * MBDD.DDARQT - MBDD.DDDUVA)	AS NUMERIC(10,2))	AS DISCOUNT_AMOUNT
,CAST( MBDD.DDARQT AS NUMERIC(7,0) )	AS ORDER_LINE_ITEM_QTY
,MBDD.DDEKCD	AS UNIT_OF_MEASURE
,CAST( MBDD.DDDUVA AS NUMERIC(10,2) )	AS ORDER_LINE_ITEM_AMT
,MBBW.BWCBTX	AS PURCHASE_ORDER
,CASE 
	WHEN (MBBW.BWACDT = '0' OR MBBW.BWACDT IS NULL) THEN '0'
	WHEN MBBW.BWACDT <> '0' THEN CONVERT(VarChar(10), Convert(DateTime, Convert(VarChar(8), MBBW.BWACDT + 19000000)), 101)
 END				AS TRANSACTION_DATE

FROM
/* BEGIN BASE QUERY JOINS */
MBBWCPP MBBW
LEFT JOIN
	MBDHREP MBDH
	ON
	MBBW.BWAENB = MBDH.DHAENB	AND	-- COMPANY NO.
	MBBW.BWCVNB = MBDH.DHCVNB			-- CUSTOMER NO.
LEFT JOIN
	MBDDREP MBDD
	ON
	MBDH.DHAENB = MBDD.DDAENB	AND	-- COMPNAY NO.
	MBDH.DHDCCD = MBDD.DDDCCD	AND	-- INTERNAL HEADER
	MBDH.DHCVNB = MBDD.DDCVNB	AND	-- CUSTOMER NO.
	MBDH.DHK4NB = MBDD.DDK4NB	AND	-- SHIPMENT HEADER NO.
	MBDH.DHZ969 = MBDD.DDZ969			-- SHIPMENT NO.

/* 
BEGIN TERRITORY LOGIC
IF COSTOMER CLASS = 'XI'
*/
LEFT JOIN
	MBBFREP MBBF
	ON
	MBBW.BWAENB = MBBF.COMNO	AND
	MBBW.BWCVNB = MBBF.CUSNO

/* IF CUSTOMER_CLASS <> 'XI' */
LEFT JOIN
	ZIPFILE ZIPF
	ON
	MBDH.DHAAGX = ZIPF.CITY		AND
	LEFT(MBDH.DHB6CD, 5) = ZIPF.ZIP5

WHERE
MBBW.BWFNST = '50'	AND
MBBW.BWACDT >= '1050101'	AND /* JAN 1, 2005 */
MBBW.BWDCCD = '1'
;
GO

Diagnosis Doc?

 

[jbenson001]

Well I can't possilbly figure it out by looking at this.. LOL

But the problem seems to be in your Union. There is a column(s) datatyps that don't match. Check the select statment for each query and make sure the data types match.

Jim

 

[SQLDenis]

your problem is that you have ints and varchar in the same unioned column

example

--works
select 1
union all
select 2

--won't work
select 1
union all
select 'a'

you will have to cast one of your ints to varcar like this

select convert(varchar(30),1)
union all
select 'a'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Qmoto]

Thanks for the help guys!

I went through and found several gotchas, once I cleaned those up and CAST some of the fields to the required lengths the query works like it should.

ps - I really do apologize for such a long code post that's not normally my style, but it appears to have helped in this case.