How to assign SQL query for an Object in IDT - DF

[Former Member]

Dear Experts,

We are creating an Relational Universe based on SQL queries given by the user, need to assign SQL query for an object in IDT- Data foundation layer

Below is the code...

E.EFFDT=(Select Max(E_ED.EFFDT) From Table

Where E.SETID = E_ED.SETID

AND     E.ACCOUNT = E_ED_ACCOUNT

AND    E_ED.EFFDT <=SYSDATE)

Can any one help me on this how to assign the above SQL query for an Object(EFFDT)

Regards

Ravi Shankar

[former_member183330 · Accepted solution]

Dear Ravi,

Create A derived table using below code

Select

     E_ED.SETID,

     E_ED.ACCOUNT,

     Max(E_ED.EFFDT) As MaxDate

     From E_ED

Where(E_ED.EFFDT <=SYSDATE)

Join this table with E table based on three column which are in derived table.

I hope this would be your requirement.

Thanks,

G Sampath Kumar

[Former Member · Accepted solution]

Dear Sampath,

Thanks for the solution, it works perfectly.

[Former Member · Accepted solution]

Hi Sampath,

Or write up the sql in the Join expression between E and what ever other table that want to use E_ED fields.

I had a similar requirement where Material and Cost_area needed to use Client_ID, LANG_ID and Flag stuff from PROFILE table and I used similar sql in the join exp and it was worked.

Please clarify if your logic is correct only if we want to use the logic b/w E and E_ED tables but can use my logic if there was already a E_ED table and put a join b/w E and say EX tables.

Thanks, Kris

[Former Member]

Hi Ravi,

Can you please provide the complete query as I can see

in your query you have used E_ED & E as two different alias .

Please Provide it so that solution can be provided easily.

Hi Ravi,

Can you please provide the complete query as I can see

in your query you have used E_ED & E as two different alias .

Please Provide it so that solution can be provided easily.

[Former Member]

Hi Swapnil,

E & ED are same tables, we just given as references for understanding.

We have created derived table successfully.

Thanks for your time.

[Former Member]

Why aren't you building the universe based on the database design then using predefined conditions and other techniques for the more complex parts?

Instead of derived tables, I'd also consider using materialised views as they'd perform far better

[Former Member]

Hi Mark,

For specific reasons we are not supposed to use materialized views for the data model

[Former Member]

I can only assume performance isn't a reason! Good luck

[Former Member]

Hello,

please check the below link,

but the new release later this month  In SAP BI 4.1 Support Package 6 its possible with web intelligence rich client , when you create a new document , free hand sql option will be available.

see below link

regards,

sree