How to create view in SAP Information Steward 4.2 ?

[former_member1108223]

Hi,

I want to create a view in SAP Information Steward 4.2 based on the following SQL:

select * from CUS_MSTR where CUS_CIF_NBR not in (select CUS_CIF_NBR from EXCLU_CUS where RULE_ID = 'RULE01')

I find that the SAP IS 4.2 does not support lookup functions in view filter. So please tell me how to create this view.

Thanks.

[former_member298829 · Accepted solution]

Hi,

Assuming that CUS_MSTR.CUS_CIF_NBR is mandatory, your query can be re-written as follows:

select CUS_MSTR.*

from   CUS_MSTR

            left outer join EXCLU_CUS

              on CUS_MSTR.CUS_CIF_NBR = EXCLU_CUS.CUS_CIF_NBR

              and EXCLU_CUS.RULE_ID = 'RULE01'

where EXCLU_CUS.CUS_CIF_NBR is null

This can now be implemented in IS.  However, depending on how IS handles the 'RULE01' condition in the ON clause, you may need to implement this view in 2 stages:

1) view1: select CUS_CIF_NBR from EXCLU_CUS where RULE_ID = 'RULE01'

2) view2: select CUS_MSTR.* from CUS_MSTR left outer join view1 on CUS_MSTR.CUS_CIF_NBR = view1.CUS_CIF_NBR where view1.CUS_CIF_NBR is null.

If CUS_MSTR.CUS_CIF_NBR is optional, you would need to adjust the SQL slightly to exclude records where CUS_MSTR.CUS_CIF_NBR is null.

Alternatively, if permitted in your environment, you could just create your required view in the underlying database and import this into IS.

Regards

Nigel

[former_member1108223 · Accepted solution]

OK, Thanks Nigel.

[former_member1108223 · Accepted solution]

Hi Nigel,

Do you have any recommendations on Informations Steward performance improving. It is very difficult for me.

Thanks.

[former_member298829 · Accepted solution]

Generally you'll get better performance if you define the view in the underlying database.  However the downside of this is that it is not very transparent; an IS user can't see how the logic behind the view.

[former_member1108223 · Accepted solution]

OK, thanks.