5 Replies Latest reply on Nov 20, 2018 4:05 PM by Robert Angel

    Sub-query in the select of OBIEE





      Trying to figure out how to add a sub-query in the select part of OBIEE generated query.

      We have two tables which cannot be joined due to some modelling issues in snow flake schema set up.

      We need to take one value from the second table using a sub-select.

      Select Reference,Amount,Currency,(select additional reference from partpay where partpay.reference=fullpay.reference and partpay.event_id=fullpay.event_id) from fullpay where Filter set

        • 1. Re: Sub-query in the select of OBIEE
          Christian Berg

          You can't just "add a sub-query" that's not how OBI works as a technology.


          Either solve the issue at its source or everything we can propose to you will not be a "solution" but a mere "hack" or "workaround". It will not solve the issue and you will pay in terms of maintenance, performance etc

          • 2. Re: Sub-query in the select of OBIEE

            Hello Christian,


            Thanks for the response.

            It is difficult to resolve at the source as there are 500s of existing reports with the same model. We can make an opaque view and join it in the rpd level to fetch the record or use DDR from answers. If something else can resolve this, it would be much better because most of the OBIEE features disabled for DDR request.

            • 3. Re: Sub-query in the select of OBIEE
              Christian Berg

              In that case I would definitely go for the opaque view. It's the "least bad" option.

              • 4. Re: Sub-query in the select of OBIEE
                Thomas Dodds

                Why not do a lookup in the RPD?   Assuming you are dealing on the same grain level of 'reference' and 'event_id' ...

                • 5. Re: Sub-query in the select of OBIEE
                  Robert Angel

                  I always think of lookups as being useful for translation purposes, whether that is turning a code in to a descriptor, or changing english into whatever other language, but it can be a life saver at the end of a long project when the client produces the 'must have' that was never specified, discussed or talked about previously and you can shoe horn an extra table worth of fields onto a dimension without a massive ETL job.


                  But, the 11th hour job can come at a price when that overlooked field becomes the thing that the end user is using to filter his reports on 9/10 times...  as it will not be as performant as fixing the issue via the ETL, particularly if it is sparse.


                  Thomas Dodds putting this for the 'record' as I know you know this, but I don't know Sunny86 and others who come after him will.


                  Though given the OP is in snow flake territory already perhaps this is the least of his issues.