I am facing TWO MAJOR PROBLEMS!!
PROBLEM 1:
I have two tables and want to show the required data into a specific gridview by using UNION in SQL. Table 1 contains columns {[Date] datetime, [Head] nvarchar(50), [Details] nvarchar(360), [ExpId] int, [Amount] decimal(18,2)}
Table 2 contains columns {[Purchase_Date] datetime, [VendorName] nvarchar(50), [Remarks] nvarchar(50), [Bill_No] nvarchar(50), [AmountPaid] decimal(18,2) }
My stored procedure is;
DECLARE @Ledger TABLE
(
DATE DATETIME,
DESCRIPTION NVARCHAR(350),
REF_NO NVARCHAR (50),
AMOUNT INT
)
INSERT INTO @Ledger
SELECT
[Date], [Head] + ' - ' + [Details], [ExpId], [Amount]
FROM
[dbo].[Table1]
UNION
SELECT
[Purchase_Date], 'PURCHASE' + ' ' + [VendorName] + ' ' + [Remarks], [Bill_No], [AmountPaid]
FROM
[dbo].[Table2]
SELECT * FROM @Ledger
When is execute the query I get an error
Conversion failed when converting the nvarchar value 'ABC113973' to data type int.
I wonder why its throwing this error when I try to execute it without Table1 it's fine. Is is due to the column ExpId
with datatype int
? If yes then how to deal with it?
PROBLEM 2:
In the above @Ledger
table when I change Amount
datatype to decimal(18,0)
as I want to show the result in decimal figure it throws error
Conversion failed when converting varchar into numeric
sort of error. as the datatype of amount columns of both the actual tables are decimal(18,2)
.
Can anyone tell me the solution and the reasons of this problem? Thanks
Best Answer
Try this:
You are getting an error because you are trying to insert
bill_no
andexpid
into avarchar
column,but the data type of both fields areint
. So, you will have to eithercast
orconvert
theint
values and then insert it into the table.Edit:
If you want to store the amount in Ledger table as decimal then change the data type of amount column to
decimal(18,2)
and make sure that columns of both the actual tables are also of the same data type.