| Author |
Topic |
|
Ali.M.Habib
Yak Posting Veteran
54 Posts |
 Posted - 2009-02-04 : 09:19:32
|
I am trying to update the view using this query -----------------------------------------------------------------insert into DDRH_VIEW select SOC_NO,'000000000'+'1442588' --------> the Account No ,999999999-cast(right(dbo.DateToJulian(convert(varchar,getdate(),112)),5) as int) --> today date,CASL_RATE,LMT_1_AMT,LMT_1_RATE,LMT_1_RATE_IND,LMT_1_EFF_DATE,right(dbo.DateToJulian(20081101),5) -------> the new expiry date ,LMT_1_STATUS,LMT_2_AMT,LMT_2_RATE,LMT_2_RATE_IND,LMT_2_EFF_DATE,LMT_2_EXPI_DATE,LMT_2_STATUS,LMT_3_AMT,LMT_3_RATE,LMT_3_RATE_IND,LMT_3_EFF_DATE,LMT_3_EXPI_DATE,LMT_3_STATUS,LMT_4_AMT,LMT_4_RATE,LMT_4_RATE_IND,LMT_4_EFF_DATE,LMT_4_EXPI_DATE,LMT_4_STATUS,EXCS_AMT,EXCS_RATE,EXCS_RATE_IND,COM_RATE,COM_RATE_IND,EXP_RATE,EXP_RATE_IND,ACCT_TYPE,INT_CAT,STATUSfrom DDRH_VIEW where MEMB_CUST_AC = '000000000'+'1442588' --> search by the name and EFFECTIVE_DATE=(select min(EFFECTIVE_DATE) from DDRH_VIEW where MEMB_CUST_AC = '000000000'+'1442588' ) but when running it give me this error:Server: Msg 4406, Level 16, State 1, Line 1Update or insert of view or function 'DDRH_VIEW' failed because it contains a derived or constant field.why please |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-04 : 09:23:59
|
| if view contains derived or constant fields, you cant update it directly. If you still want to update it, You need to use an instead of trigger which you create on view to do updates directly onto base tables. |
 |
|
|
Ali.M.Habib
Yak Posting Veteran
54 Posts |
Posted - 2009-02-04 : 09:27:56
|
it's a simple view which take from one table , how can I know if it take a constant or derived fields it's creation code is CREATE view dbo.DDRH_VIEW as select cast(SOC_NO as char(3)) as SOC_NO, cast(MEMB_CUST_AC as char(16)) as MEMB_CUST_AC, EFFECTIVE_DATE, CASL_RATE, LMT_1_AMT, LMT_1_RATE, cast(LMT_1_RATE_IND as char(1)) as LMT_1_RATE_IND, LMT_1_EFF_DATE, LMT_1_EXPI_DATE, cast(LMT_1_STATUS as char(1)) as LMT_1_STATUS, LMT_2_AMT, LMT_2_RATE, cast(LMT_2_RATE_IND as char(1)) as LMT_2_RATE_IND, LMT_2_EFF_DATE, LMT_2_EXPI_DATE, cast(LMT_2_STATUS as char(1)) as LMT_2_STATUS, LMT_3_AMT, LMT_3_RATE, cast(LMT_3_RATE_IND as char(1)) as LMT_3_RATE_IND, LMT_3_EFF_DATE, LMT_3_EXPI_DATE, cast(LMT_3_STATUS as char(1)) as LMT_3_STATUS, LMT_4_AMT, LMT_4_RATE, cast(LMT_4_RATE_IND as char(1)) as LMT_4_RATE_IND, LMT_4_EFF_DATE, LMT_4_EXPI_DATE, cast(LMT_4_STATUS as char(1)) as LMT_4_STATUS, EXCS_AMT, EXCS_RATE, cast(EXCS_RATE_IND as char(1)) as EXCS_RATE_IND, COM_RATE, cast(COM_RATE_IND as char(1)) as COM_RATE_IND, EXP_RATE, cast(EXP_RATE_IND as char(1)) as EXP_RATE_IND, cast(ACCT_TYPE as char(4)) as ACCT_TYPE, cast(INT_CAT as char(4)) as INT_CAT, cast(STATUS as char(2)) as STATUS from DDRH |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-04 : 09:44:00
|
| cast(SOC_NO as char(3)),cast(MEMB_CUST_AC as char(16))... are all derived fields |
|
|
|
|
|
|
Update the view give error why?????