Derive table in universe for webi report

[Former Member]

hi all i am using derived table in the universe to create webi report which is using SQl shown below.   Now i have to add third table  in this SQL so that i can change the Date prompt (SHOWN IN BOLD) with the object from third table.

DW.TABLE3  With three object DATE_START, DATE_END and  AMOUNT

SELECT MEMBNO, COMPNO, GRPNUM,

  FROM (

SELECT R.MEMBNO, R.COMPNO, R.PARENT, R.GRPNUM

FROM DW.TABLE1 R

INNER JOIN (SELECT MEMBNO, GRPNUM, COMPNO

                   FROM DW.TABLE2 M

                   WHERE      (     M.COMPNO = '2')

                    ) M

             ON   M.MEMBNO=R.MEMBNO

             AND  M.GRPNUM=R.GRPNUM

             AND  M.COMPNO=R.COMPNO

            AND R.DATE <= @prompt(' Date:','D',,Mono,free,Not_Persistent)          (HERE i have to add START_DATE from TABLE3)

            GROUP BY R.MEMBNO, R.COMPNO, R.PARENT, R.GRPNUM)

GROUP BY  MEMBNO, PARENT, GRPNUM,

       COMPNO

The above query is working fine

what changes should i make in this sql so that i can access TABLE3 objects IN THIS SQL

Thank You

[Former Member]

Hi Malik,

Is this a SQl Server query?

I think, you can do another Inner Join after the 'ON M.MEMBNO=R.MEMBNO'  followed with the 'AND' conditions thereafter alongwith AND R.DATE > = DW.Table3.Start Date AND R.DATE < = DW.Table3.End Date

i.e

..............

.............

ON M.MEMBNO=R.MEMBNO

Inner Join DW.Table 3

However, I can also see a lot of redundancy, for e.g if the outer query is not selecting "R.Parent" so it can be kept only in the Group By and excluded from Select...Also same is the case with Start,End Date and Amount fields, they are not selected by the outer query, so don't see any reason of using them in inner query.

Thanks

[former_member188911]

I BI4.0 you can use the results from another query at report level, so you may consider another option different from the derived table.

best regards,

Simone