cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Error while Executing Query

Go to solution
Former Member

on ‎04-15-2017 1:40 PM

0 Kudos

Dear Experts

when i run this query individually there is no Error

but when i run all together , am getting an error

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

'Error converting data type nvarchar to numeric.'

Pls find the attached query

Pls help me to sort it out

Regards

select Distinct ODLN.DocNum AS 'DocNumber',ODLN.NumAtCard as 'Doc Ref No',OCRD.CardFName as 'Customer Ref No', ODLN.CardName AS 'Customer Name',ODLN.DiscSum as 'Discount Sum',ODLN.DocTotal as 'Doc Total',ODLN.U_PMOD AS 'Payment Type',
ODLN.DocDate as 'Posting Date',NNM1.SeriesName,ODLN.DocType as 'Document Series',ODLN.ObjType from ODLN


 Left Outer JOIN DLN1 ON ODLN.DocEntry = DLN1.DocEntry
 left outer  join NNM1 on ODLN.Series = NNM1.Series
 left outer join ocrd on ODLN.CardCode = OCRD.CardCode
 Left Outer JOIN OITM on DLN1.ItemCode = OITM.ItemCode
WHERE ODLN.CANCELED <> 'C' 


 union


select Distinct ODLN.DocNum AS 'DocNumber',ODLN.NumAtCard as 'Doc Ref No',OCRD.CardFName as 'Customer Ref No', ODLN.CardName AS 'Customer Name',ODLN.DiscSum as 'Discount Sum','-'+cast(ODLN.DocTotal as nvarchar) as  'Doc Total',ODLN.U_PMOD AS 'Payment Type',
ODLN.DocDate as 'Posting Date',NNM1.SeriesName,ODLN.DocType as 'Document Series',ODLN.ObjType from ODLN


 Left Outer JOIN DLN1 ON ODLN.DocEntry = DLN1.DocEntry
 left outer  join NNM1 on ODLN.Series = NNM1.Series
 left outer join ocrd on ODLN.CardCode = OCRD.CardCode
 Left Outer JOIN OITM on DLN1.ItemCode = OITM.ItemCode
where ODLN.CANCELED = 'C'




Union


select Distinct ORDN.DocNum AS 'DocNumber',ORDN.NumAtCard as 'Doc Ref No',OCRD.CardFName as 'Customer Ref No', ORDN.CardName AS 'Customer Name',ORDN.DiscSum as 'Discount Sum','-' + cast(ORDN.DocTotal as nvarchar) as 'Doc Total',ORDN.U_PMOD AS 'Payment Type',
ORDN.DocDate as 'Posting Date',NNM1.SeriesName,ORDN.DocType as 'Document Series',ORDN.ObjType  from ORDN


 Left Outer JOIN RDN1 ON ORDN.DocEntry = RDN1.DocEntry
 left outer  join NNM1 on ORDN.Series = NNM1.Series
 left outer join OCRD on ORDN.CardCode = OCRD.CardCode
 Left Outer JOIN OITM on RDN1.ItemCode = OITM.ItemCode
 where ORDN.CANCELED <> 'C' 
 
 union


 select Distinct ORDN.DocNum AS 'DocNumber',ORDN.NumAtCard as 'Doc Ref No',OCRD.CardFName as 'Customer Ref No', ORDN.CardName AS 'Customer Name',ORDN.DiscSum as 'Discount Sum', + cast(ORDN.DocTotal as nvarchar) as 'Doc Total',ORDN.U_PMOD AS 'Payment Type',
ORDN.DocDate as 'Posting Date',NNM1.SeriesName,ORDN.DocType as 'Document Series' ,ORDN.ObjType from ORDN


 Left Outer JOIN RDN1 ON ORDN.DocEntry = RDN1.DocEntry
 left outer  join NNM1 on ORDN.Series = NNM1.Series
 left outer join OCRD on ORDN.CardCode = OCRD.CardCode
 Left Outer JOIN OITM on RDN1.ItemCode = OITM.ItemCode


 where ORDN.CANCELED = 'C'


Union


select Distinct ORIN.DocNum AS 'DocNumber',ORIN.NumAtCard as 'Doc Ref No',OCRD.CardFName as 'Customer Ref No', ORIN.CardName AS 'Customer Name',ORIN.DiscSum as 'Discount Sum','-' + cast(ORIN.DocTotal as nvarchar) as 'Doc Total',ORIN.U_PMOD AS 'Payment Type',
ORIN.DocDate as 'Posting Date',NNM1.SeriesName,ORIN.DocType as 'Document Series' ,ORIN.ObjType from ORIN


 Left Outer JOIN RIN1 ON ORIN.DocEntry = RIN1.DocEntry
 left outer  join NNM1 on ORIN.Series = NNM1.Series
 left outer join OCRD on ORIN.CardCode = OCRD.CardCode
 Left Outer JOIN OITM on RIN1.ItemCode = OITM.ItemCode
 
 where  ORIN.DocStatus = 'O'
Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

former_member185682
Active Contributor
‎04-16-2017 7:04 PM
0 Kudos

Hi Shahan,

This error occured because of the type of you column DocTotal. In the first query you leave the field DocTotal in your correct format as numeric, but in the other 4 queries you convert the field DocTotal as nvarchar. When the SQL tried to union the result of your 5 queries, the sql receive an exception because you have diferent types for one column. The query corrected below:

select Distinct 
	ODLN.DocNum AS 'DocNumber',
	ODLN.NumAtCard as 'Doc Ref No',
	OCRD.CardFName as 'Customer Ref No', 
	ODLN.CardName AS 'Customer Name',
	ODLN.DiscSum as 'Discount Sum',
	ODLN.DocTotal as 'Doc Total',
	ODLN.U_PMOD AS 'Payment Type',
	ODLN.DocDate as 'Posting Date',
	NNM1.SeriesName,
	ODLN.DocType as 'Document Series',
	ODLN.ObjType 
from ODLN
 Left Outer JOIN DLN1 ON ODLN.DocEntry = DLN1.DocEntry
 left outer  join NNM1 on ODLN.Series = NNM1.Series
 left outer join ocrd on ODLN.CardCode = OCRD.CardCode
 Left Outer JOIN OITM on DLN1.ItemCode = OITM.ItemCode
WHERE 
	ODLN.CANCELED <> 'C' 
union
select Distinct 
	ODLN.DocNum AS 'DocNumber',
	ODLN.NumAtCard as 'Doc Ref No',
	OCRD.CardFName as 'Customer Ref No', 
	ODLN.CardName AS 'Customer Name',
	ODLN.DiscSum as 'Discount Sum',
	(-1) * ODLN.DocTotal as  'Doc Total',
	ODLN.U_PMOD AS 'Payment Type',
	ODLN.DocDate as 'Posting Date',
	NNM1.SeriesName,
	ODLN.DocType as 'Document Series',
	ODLN.ObjType 
from ODLN
 Left Outer JOIN DLN1 ON ODLN.DocEntry = DLN1.DocEntry
 left outer  join NNM1 on ODLN.Series = NNM1.Series
 left outer join ocrd on ODLN.CardCode = OCRD.CardCode
 Left Outer JOIN OITM on DLN1.ItemCode = OITM.ItemCode
where 
	ODLN.CANCELED = 'C'
Union
select Distinct 
	ORDN.DocNum AS 'DocNumber',
	ORDN.NumAtCard as 'Doc Ref No',
	OCRD.CardFName as 'Customer Ref No', 
	ORDN.CardName AS 'Customer Name',
	ORDN.DiscSum as 'Discount Sum',
	(-1) * ORDN.DocTotal as 'Doc Total',
	ORDN.U_PMOD AS 'Payment Type',
	ORDN.DocDate as 'Posting Date',
	NNM1.SeriesName,ORDN.DocType as 'Document Series',
	ORDN.ObjType
from ORDN
 Left Outer JOIN RDN1 ON ORDN.DocEntry = RDN1.DocEntry
 left outer  join NNM1 on ORDN.Series = NNM1.Series
 left outer join OCRD on ORDN.CardCode = OCRD.CardCode
 Left Outer JOIN OITM on RDN1.ItemCode = OITM.ItemCode
where 
	ORDN.CANCELED <> 'C' 
union
 select Distinct 
	ORDN.DocNum AS 'DocNumber',
	ORDN.NumAtCard as 'Doc Ref No',
	OCRD.CardFName as 'Customer Ref No', 
	ORDN.CardName AS 'Customer Name',
	ORDN.DiscSum as 'Discount Sum', 
	ORDN.DocTotal as 'Doc Total',
	ORDN.U_PMOD AS 'Payment Type',
	ORDN.DocDate as 'Posting Date',
	NNM1.SeriesName,
	ORDN.DocType as 'Document Series' ,
	ORDN.ObjType 
from ORDN
 Left Outer JOIN RDN1 ON ORDN.DocEntry = RDN1.DocEntry
 left outer  join NNM1 on ORDN.Series = NNM1.Series
 left outer join OCRD on ORDN.CardCode = OCRD.CardCode
 Left Outer JOIN OITM on RDN1.ItemCode = OITM.ItemCode
where 
	ORDN.CANCELED = 'C'
Union
select Distinct 
	ORIN.DocNum AS 'DocNumber',
	ORIN.NumAtCard as 'Doc Ref No',
	OCRD.CardFName as 'Customer Ref No', 
	ORIN.CardName AS 'Customer Name',
	ORIN.DiscSum as 'Discount Sum',
	(-1) * ORIN.DocTotal as 'Doc Total',
	ORIN.U_PMOD AS 'Payment Type',
	ORIN.DocDate as 'Posting Date',
	NNM1.SeriesName,
	ORIN.DocType as 'Document Series' ,
	ORIN.ObjType from ORIN
 Left Outer JOIN RIN1 ON ORIN.DocEntry = RIN1.DocEntry
 left outer  join NNM1 on ORIN.Series = NNM1.Series
 left outer join OCRD on ORIN.CardCode = OCRD.CardCode
 Left Outer JOIN OITM on RIN1.ItemCode = OITM.ItemCode
 where  
	ORIN.DocStatus = 'O'

Hope it helps.

Kind Regards,

Diego Lother

Show replies
Show replies

Answers (0)

Ask a Question