SQL Server Performance Forum – Threads Archive
computed column
Hey gurus can you help me out on this ? insert va_contractselect ‘AA’,
A.POL_NBR,
ISS_DT,
POL_TYP,
ISS_ST_CD,
BLNKT_AUTH_IND,
TOT_DEPOS_AMT,
FULL_SURNDR_CHRG,
CASH_VAL, (can we able to modify here to get the sum or computed of CASH_VAL)
pio_status_cd,
PROD_DESC,
iss_age,
‘TG’,
GETDATE() from STG_POLICY_B A, STG_ANNTY_MISC_B B, xref_Acct_status
WHERE A.POL_NBR = B.POL_NBR
AND A.REC_TYPE = ‘D’ AND B.REC_TYPE = ‘D’
and source_status_cd = pol_stat_cd
and xref_acct_status.source_cd = ‘aa’ The value in CASH_VAL (it comes from STG_ANNTY_MISC) that is being inserted into va_contract:CASH_VAL is incorrect. Can the CASH_VAL value be changed to use a computed value? I need the total of stg_fund_alloc_b: CASH_VAL from all records in stg_fund_alloc_b where pol_nbr = pol_nbr
If I understand you correctly, the CASH_VAL would be a static computed value when inserted. You can compute values on the fly in this case by selecting within a select. Here is an example where you will see all the sysobjects and the number of user tables computed by the count. select name, (select count(name) from sysobjects where xtype > ‘u’) from sysobjects Your query might look something like /*************************************************************************/ insert va_contract
select ‘AA’,
A.POL_NBR,
ISS_DT,
POL_TYP,
ISS_ST_CD,
BLNKT_AUTH_IND,
TOT_DEPOS_AMT,
FULL_SURNDR_CHRG,
(SELECT sum(C.CASH_VAL) from STG_FUND_ALLOC_B C where C.POL_NBR = A.POL_NBR) as CASH_VAL,
pio_status_cd,
PROD_DESC,
iss_age,
‘TG’,
GETDATE() from STG_POLICY_B A, STG_ANNTY_MISC_B B, xref_Acct_status
WHERE A.POL_NBR = B.POL_NBR
AND A.REC_TYPE = ‘D’ AND B.REC_TYPE = ‘D’
and source_status_cd = pol_stat_cd
and xref_acct_status.source_cd = ‘aa’ /*************************************************************************/ It looks a bit confusing to use a table A when you have not even defined it yet but it works. The inner select runs once the outer one is evaluated so you only get the POL_NBR values that meet the outer select’s criteria. It may make more sense if you just test run the outer query and then see how the inner query would run against that results. This is combining the two steps. John
thanks for your reply cash_val
————- +000006604.27
+000041517.22
+000007707.11
+000000079.03
+000000000.00 cash_val is char so i tried cast and convert doesn’t work. Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a char data type as an argument. i would appreciate your help.
Why did you use char datatype to store numbers?
Use Proper datatype money, real, float or decimal datatype Use Sum(cast(col as decimal(12,2)) Madhivanan Failing to plan is Planning to fail
DECLARE @a DECIMAL(8,2)
SET @a = ‘000006604.27’
SELECT
CASE
WHEN @a > 0 THEN ‘+’ + REPLACE(STR(@a,12,2), ‘ ‘, ‘0’)
ELSE ‘-‘ + REPLACE(STR(@a,12,2), ‘ ‘, ‘0’)
END
——————-
+000006604.27 (1 row(s) affected) Change @a to the SUM of your query. Is this for some kind of electronic data transfer?
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
create table tbl (id int,val nvarchar(15))
insert into tbl select 1,’+000006604.27′
union all select 1,’+000041517.22′
union all select 2,’+000007707.11′
union all select 2,’+000000079.03′
select id,sum(cast(substring(val,2,len(val)-1) as float)) from tbl
group by id —————-
148121.490000000005
27786.1399999999994
]]>