6 Replies Latest reply: Jul 31, 2014 3:52 PM by Bas RSS

    OBIEE 11g Physical Join Condition

    Bas

      Team

       

      How to create a complex physical join which has a substr function in the OBIEE 11g RPD.

       

      The following is the scenario.. A custom Dimension table has monthwid (201407,201408..) and customer number . The fact table has created date( 20140712, 20140715....) and customer number. We have to create a physcial join as

      Dimension table.Customer Number = Fact table. Customer Number AND Dimension table.monthwid = substr(facttable.created date,1,6).

       

      When I tried, getting the generic nqserror 27002 . Is there any other to create a join in the above scenario.

       

      Thanks a lot

      Regards

      Bas

        • 1. Re: OBIEE 11g Physical Join Condition
          Christian Berg

          Are you sure that you're actually comparing the same data types on both sides of the join? That is....AFTER the substr happens? Do you REALLY store numerical WIDs as strings??

          • 2. Re: OBIEE 11g Physical Join Condition
            Bas

            Thanks Christian for the reply. We are not storing the wids as strings. We are storing the wids as number fields.

            We are getting the list of customers for a month in a flat file (we load it in a custom dimension). The wid (numeric filed) in the dimension has data (201407).
            We want to pull all the fact transactions for that month and customer combination. The fact transactions are at day level so in the fact tables the created date wid (Numeric Field)  has data  (20140728) . To create a join between the Dim Wid (201407) and Fact Wid (20140728, ..anything in that month), I tried with the Substr function so that I can compare the first 6 digits.

             

            Even I tried with the following syntax in the join.
            cast(dim.monthwid AS varchar(10))= substr(cast(fact.datewid AS varchar(10)),1,6)

             

            CAST works fine. But the moment I put the substr it is erroring out. Is there any other way to create a join for the above scenario?. Again thanks for your time and input
            Regards
            Bas

            • 3. Re: OBIEE 11g Physical Join Condition
              Christian Berg

              Hi Bas,

               

              I just saw that the syntax is off. It's "substring(... from ... for ...)" like so:

               

              cast("01 - Sample App Data (ORCL)".."BISAMPLE"."ZZZZ-SRORDERS"."GUID"  as varchar(10))

              =

              substring(cast("01 - Sample App Data (ORCL)".."BISAMPLE"."ZZZZ-SCORDERS"."GUID" as varchar(10)) FROM 0 FOR 6)

              • 4. Re: OBIEE 11g Physical Join Condition
                Bas

                Thanks Christian for the right syntax. The syntax that you provided worked perfectly. Now the join is working fine. Thanks a lot for your guidance on this.

                 

                One quick question.. Where did you get the syntax from?. Is there any OBIEE document that has the right OBIEE syntax?.

                 

                Regards

                Bas

                • 5. Re: OBIEE 11g Physical Join Condition
                  Christian Berg

                  When you're unsure of the syntax the best way is to use the Expression Builder of the Admin Tool when creating the complex join. The Expression Builder gives you access to all built-in and supported functions and transformations.

                  • 6. Re: OBIEE 11g Physical Join Condition
                    Bas

                    Awesome. Thank you so much

                     

                    Regards

                    Bas