July 1, 2011 at 12:05 am
Hi All,
I have an Error :Error converting data type nvarchar to numeric.
Tables
t_trn_loan = t1
t_trn_loan_lqr_extension = t2
Coloumns
System ID = Sorce_system data type char8 in t1
Source System Product Code= char_cust_element4 data type char 20 in t1
gross gl balance(IN Actual Currency)= outstanding_amount data type Decimal SN(38)vN(8) in t2
My Requirement:
The Product data is grouped by System ID and Source System Product Code to calculate the
sum of Gross GL Balance in Actual Currency for respective Reporting date.
And find the cumulative value of the Gross GL Balance in Actual Currency between the current reporting
day and the previous reporting day
I codded like this
Select t_trn_loan.[source_system],t_trn_loan_lqr_extension.[char_cust_element4],
COUNT(t_trn_loan_lqr_extension.outstanding_amount) AS Gross_GL_Balance
FROM t_trn_loan_lqr_extension INNER JOIN t_trn_loan ON
t_trn_loan.[source_system] = t_trn_loan_lqr_extension.[outstanding_amount]
GROUP BY t_trn_loan.[source_system],t_trn_loan_lqr_extension.[char_cust_element4]
What can i do...Plz any one guide me?
Adv Thanks,
July 1, 2011 at 12:44 am
bhargava.sumansiram (7/1/2011)
Hi All,I have an Error :Error converting data type nvarchar to numeric.
Tables
t_trn_loan = t1
t_trn_loan_lqr_extension = t2
Coloumns
System ID = Sorce_system data type char8 in t1
Source System Product Code= char_cust_element4 data type char 20 in t1
gross gl balance(IN Actual Currency)= outstanding_amount data type Decimal SN(38)vN(8) in t2
My Requirement:
The Product data is grouped by System ID and Source System Product Code to calculate the
sum of Gross GL Balance in Actual Currency for respective Reporting date.
And find the cumulative value of the Gross GL Balance in Actual Currency between the current reporting
day and the previous reporting day
I codded like this
Select t_trn_loan.[source_system],t_trn_loan_lqr_extension.[char_cust_element4],
COUNT(t_trn_loan_lqr_extension.outstanding_amount) AS Gross_GL_Balance
FROM t_trn_loan_lqr_extension INNER JOIN t_trn_loan ON
t_trn_loan.[source_system] = t_trn_loan_lqr_extension.[outstanding_amount]GROUP BY t_trn_loan.[source_system],t_trn_loan_lqr_extension.[char_cust_element4]
What can i do...Plz any one guide me?
Adv Thanks,
What you are trying to do?
The joining columns are diffrent data types thas why you are getting error.
If you want you can convert it, But really What you are going to achive to join the char and decimal?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 1, 2011 at 12:51 am
Tks for ur responce muthukkumaran Kaliyamoorthy
but my requirement is to calculate the gross gl balance....
Is there any possiable way 2 get by conveting plz update the quary and post me......
July 1, 2011 at 1:22 am
bhargava.sumansiram (7/1/2011)
Tks for ur responce muthukkumaran Kaliyamoorthybut my requirement is to calculate the gross gl balance....
Is there any possiable way 2 get by conveting plz update the quary and post me......
Hey its simple use the cast and convert to varchar(20) outstanding_amount.
But really i'm not understanding your requirement.
How are you going to mach the char value to decimal ?
(like) muthu=99.99
you can convert but you can't get the results becaue of inner join. Inner join only returns the matched records from both the tables.
No prob just repalce join contidition to
ON t_trn_loan.[source_system] =t_trn_loan_lqr_extension.[outstanding_amount]
from
ON t_trn_loan.[source_system] = cast(t_trn_loan_lqr_extension.[outstanding_amount] as varchar(20))
Edit:to add more
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 1, 2011 at 1:46 am
This was removed by the editor as SPAM
July 1, 2011 at 4:08 am
I changed the quary like this...
SELECT t_trn_loan.source_system,t_trn_loan.char_cust_element4,t_trn_loan.customer_nr,t_trn_loan_lqr_extension.outstanding_amount,
DATEADD(s,0,DATEADD(dd, DATEDIFF(d,0,getdate()),0)) as Current_reporting_date
FROM t_trn_loan
INNER JOIN t_trn_loan_lqr_extension
ON t_trn_loan.deal_id=t_trn_loan_lqr_extension.deal_id
order BY t_trn_loan.source_system
Now the problem is to calculate the Sum of outstanding_amount
July 1, 2011 at 4:15 am
table_1 table_2
Sorce_system char_cust_element4 outstanding amount
TUB RETAIL -54644.24000000
TUB RETAIL 17112.90000000
July 1, 2011 at 4:17 am
This was removed by the editor as SPAM
July 1, 2011 at 4:36 am
Really itz Very helpfull.....
Tkq stewartc-708166
In this same quary how can i get current reporting data field & previous repoorting date field........
I made few changes like this :
SELECT t_trn_loan.source_system as system_ID,t_trn_loan.char_cust_element4 as Source_System_Product_Code,
t_trn_loan.last_modified,
SUM(t_trn_loan_lqr_extension.outstanding_amount) AS Gross_GL_Balance
FROM t_trn_loan
INNER JOIN t_trn_loan_lqr_extension
ON t_trn_loan.deal_id=t_trn_loan_lqr_extension.deal_id
GROUP BY t_trn_loan.source_system,t_trn_loan.char_cust_element4,t_trn_loan.last_modified
Plz guide me hw ca i calculate the current reporting data field & previous repoorting date field
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply