1、问题:
BIEE 11g往sqlserver回写插入数据时,插入时没有报错,但是插入操作失败,数据库没有刚插入的数据。但是更新操作是可以的?????
回写模板WriteBack文件内容如下:
<WebMessage name="SetAccountingRegion">
<XML>
<writeBack connectionPool="VcreditDW Conn">
<insert>INSERT INTO Financial_DivisionMapping (StoreName,LendingSide,AccountDivision,MonthsDivision)
VALUES ('@1',isnull('@2',' '),isnull('@3',' '),isnull('@4',' '))</insert>
<update>UPDATE Financial_DivisionMapping set LendingSide=isnull('@2',' '), AccountDivision=isnull('@3',' '),MonthsDivision=isnull('@4',' ')
where StoreName='@1'</update>
</writeBack>
</XML>
</WebMessage>
2、问题分析:
查看日志发现执行INSERT回写操作发送的sql语句为UPDATE语句(条件为StoreName=’输入的值‘),而在数据库中StoreName还没有此条数据,所以插入失败。
3、解决方法:
修改回写模板WriteBack文件内容如下,即可:
<WebMessage name="SetAccountingRegion">
<XML>
<writeBack connectionPool="VcreditDW Conn">
<insert>MERGE INTO Financial_DivisionMapping A
USING (SELECT ltrim(rtrim('@1')) as StoreName
,ltrim(rtrim('@2')) as LendingSide
,ltrim(rtrim('@3')) as AccountDivision
,ltrim(rtrim('@4')) as MonthsDivision
) B
ON (A.StoreName=B.StoreName)
WHEN MATCHED THEN
UPDATE
set
A.LendingSide = B.LendingSide,
A.AccountDivision = B.AccountDivision,
A.MonthsDivision=B.MonthsDivision
WHEN NOT MATCHED THEN
INSERT
VALUES
(
B.StoreName
,B.LendingSide
,B.AccountDivision
,B.MonthsDivision
);</insert>
<update>MERGE INTO Financial_DivisionMapping A
USING (SELECT ltrim(rtrim('@1')) as StoreName
,ltrim(rtrim('@2')) as LendingSide
,ltrim(rtrim('@3')) as AccountDivision
,ltrim(rtrim('@4')) as MonthsDivision
) B
ON (A.StoreName=B.StoreName)
WHEN MATCHED THEN
UPDATE
set
A.LendingSide = B.LendingSide,
A.AccountDivision = B.AccountDivision,
A.MonthsDivision=B.MonthsDivision
WHEN NOT MATCHED THEN
INSERT
VALUES
(
B.StoreName
,B.LendingSide
,B.AccountDivision
,B.MonthsDivision
);
</update>
</writeBack>
</XML>
</WebMessage>
注:在SQLSERVER中MERGE语句后面一定要加分号,在oracle中则不用加分号。