7 Replies Latest reply: Nov 9, 2009 3:44 AM by William Lesmana RSS

    Using FILTER function in oracle answers

    732230
      Gurus,

      I have a question related to using Filter function in oracle answers.

      When trying to insert a Filter (expr) Using (expr) clause in the formula area of a fact table field, It errored out with msg saying about using a wrong measure.

      I know this can be done with a case expression but I tried filter clause since this is available in oracle answers.

      Please help me figuring out this scenario.

      Thanks.
        • 1. Re: Using FILTER function in oracle answers
          David_T
          If you want help, you are going to have to be more descriptive. What was the error message?? Don't ask the question as if we already know what you are talking about or are at your desk looking at the screen with the message. Put a screen shot or type the message. Otherwise, you'll just get a lot of "shot in the dark" answers or an answer like, "Well, use the 'correct' measure, then."

          Please try again.
          • 2. Re: Using FILTER function in oracle answers
            672047
            'FILTER' require atleast one measure colums in its clause..

            Regards,
            Raghu
            • 3. Re: Using FILTER function in oracle answers
              David_T
              What the error message means is that the FILTER function only works on measures ("facts"), not dimensions. So for example you could say, "filter Ledger Balance using year = 2009" but you can't use "filter Region using product = 'electronics' since "Region" is not a fact.

              Got it? Hope that clears things up. If not, more details...
              • 4. Re: Using FILTER function in oracle answers
                732230
                David / Raghu - Thanks for u'r replies and apologizes for not posting question with proper material.

                Am posting my code and the error message from the screen.

                Code :

                IFNULL(FILTER("Fact - MBS Loan Transactions"."OUTSTANDING PRINCIPAL" USING "Dim - MBS Loan"."LOAN TYPE HPD/HDC/BNK" = 'HDC'),0)

                Error :

                nQSError: 10058] A general error has occurred. [nQSError: 22032] Function FILTER requires at least one measure attribute in its first argument. (HY000)
                SQL Issued: SELECT "Dim - MBS Loan"."LOAN AMOUNT", "Dim - MBS Loan"."LOAN TYPE HPD/HDC/BNK", "Dim - MBS Loan"."LOAN TYPE SEN/SUB", "Dim - MBS Project"."PROJECT NAME", "Dim - MBS Project"."PROJECT NUMBER", "Fact - MBS Loan Transactions"."AR BALANCE INTEREST", "Fact - MBS Loan Transactions"."GL BALANCE INTEREST", IFNULL(FILTER("Fact - MBS Loan Transactions"."OUTSTANDING PRINCIPAL" USING "Dim - MBS Loan"."LOAN TYPE HPD/HDC/BNK" = 'HDC'),0) FROM "Financials - MBS"
                OK (Ignore Error)

                Please continue answering my queries. Since am a newbie your answers won't be just a reply but it's actually learning for me.

                Thanks.
                • 5. Re: Using FILTER function in oracle answers
                  William Lesmana
                  Based on the error message, "Fact - MBS Loan Transactions" is not a Fact (or Measure) dimension, probably in Business Model, "Fact - MBS Loan Transactions" is not joined properly, try to check the physical and business model diagram.

                  -Will
                  • 6. Re: Using FILTER function in oracle answers
                    732230
                    William,

                    Please explain me the basic cause of this error. Am trying to understand some concept also.

                    As a developer am not having access to the BI Admin. your / other experts explanation will help me understand more.

                    Thanks.
                    • 7. Re: Using FILTER function in oracle answers
                      William Lesmana
                      The basic cause is, most likely, your Fact table is not joined properly to the other dimensions, so instead of creating a Fact table, it is treating the table as a dimension. Try to ask your person in charge for doing the BMM about the correctness of the diagram.

                      -Will