Skip to Content
avatar image
Former Member

Error while Executing Query

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'
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Apr 16, 2017 at 06:04 PM

    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

    Add comment
    10|10000 characters needed characters exceeded