8 Replies Latest reply: Feb 7, 2013 12:13 PM by 756618 RSS

    OBIEE 10G Direct Database Request

    756618
      Hey Everybody,

      I'm on OBIEE 10G in a solaris environment and I'm connected to Oracle 11.2 database. I'm having trouble getting the correct dataset returned from a Direct Database Request from a dashboard that users input data. I have the dashboard created and have all the dashboard prompts setup as prestentation variables. My DDR sql looks like this:

      Select from table where Batch_ID = '11' and PID = '1234' and Reported_Date = '01011900'*


      My issue is that I want the Reported Date dashboard prompt to be a optional field that user do not have to enter on the dashboard. However, if a user doesn't enter a value on the dashboard then an error is thrown because the DDR sql is expecting a value for the Reported Date.

      I've tried to enter the OBIEE standard "%" as an all choices option in the database and changed the DDR sql to look like this:

      Select from table where Batch_ID = '11' and PID = '1234' and Reported_Date like '%'*


      However, this throws an error saying an unidentified characters has been used even though if I run this above sql query directly against the database myself it gives back the wanted dataset.


      Anybody ever ran across this situation before?
      Thanks,

      Jeff
        • 1. Re: OBIEE 10G Direct Database Request
          Srini VEERAVALLI
          Try the below, this is working in my local. Assuming @ is referring to presentation variable
          Select COL1,COL2 from table where Batch_ID = '11' and PID = '1234' and Reported_Date LIKE
          (CASE WHEN '@{U}{}'='' THEN '%%' ELSE '@{U}' END)

          Ref: http://cool-bi.com/Tweaks/AllColumnValues.php

          Let me know for issues, if helps mark as correct

          Edited by: Srini VEERAVALLI on Feb 4, 2013 11:38 PM
          • 2. Re: OBIEE 10G Direct Database Request
            756618
            Thank you for the response but this did not work.

            When I put this code into the Direct Database Sql and I leave the Reported Date dashboard prompt empty I get an error saying "missing expression." It almost like it isn't able to use the "%%" for the presentation variable.

            Thanks.
            • 3. Re: OBIEE 10G Direct Database Request
              Srini VEERAVALLI
              Can you share your db query to my email or in the post. You have to use the code as is to work.
              • 4. Re: OBIEE 10G Direct Database Request
                756618
                Here is the code I used as my DDR. When I leave the @reported_date prompt empty it doesn't return anything. Thanks.

                select Batch_ID_0,
                Batch_Seq_Nbr_1,
                AC_TL_Seq_Nbr_2,
                Contributor_ID_3,
                Account_Nbr_4,
                Account_Type_5,
                Account_Status_6,
                Opened_Date_7,
                Reported_Date_8,
                Closed_Date_9,
                Current_Balance_10,
                AC_Assoc_Seq_Nbr_11,
                ECOA_Code_12,
                SSN_13,
                Phone_Nbr_14,
                Birth_Date_15,
                Last_Name_16,
                First_Name_17,
                Middle_Name_18,
                Generation_Code_19,
                Line1_of_Addr_20,
                Line2_of_Addr_21,
                City_22,
                State_23,
                Zip_24
                From (
                Select Ac_Trade.Batch_ID Batch_ID_0,
                Ac_Trade.Batch_Seq_Nbr Batch_Seq_Nbr_1,
                Ac_Trade.AC_TL_Seq_Nbr AC_TL_Seq_Nbr_2,
                Ac_Trade.Contributor_ID Contributor_Id_3,
                Ac_Trade.Account_Nbr Account_Nbr_4,
                Ac_Trade.Account_Type Account_Type_5,
                Ac_Trade.Account_Status Account_Status_6,
                Ac_Trade.Opened_Date Opened_Date_7,
                Ac_Trade.Reported_Date Reported_Date_8,
                Ac_Trade.Closed_Date Closed_Date_9,
                Ac_Trade.Current_Balance Current_Balance_10,
                Ac_Assoc.AC_Assoc_Seq_Nbr AC_Assoc_Seq_Nbr_11,
                Ac_Assoc.Ecoa_Code ECOA_Code_12,
                Ac_Assoc.SSN SSN_13,
                Ac_Assoc.Phone_Nbr Phone_Nbr_14,
                Ac_Assoc.Birth_Date Birth_Date_15,
                Ac_Assoc.Last_Name Last_Name_16,
                Ac_Assoc.First_Name First_Name_17,
                Ac_Assoc.Middle_Name Middle_Name_18,
                Ac_Assoc.Generation_Code Generation_Code_19,
                Ac_Assoc.Line1_of_Addr Line1_of_Addr_20,
                Ac_Assoc.Line2_of_Addr Line2_of_Addr_21,
                Ac_Assoc.City City_22,
                Ac_Assoc.State State_23,
                Ac_Assoc.Zip Zip_24,
                row_number() over(order by Ac_Trade.batch_id) row_limit
                From owb.Ac_Trade, owb.Ac_Assoc
                Where Ac_Trade.Batch_Id = Ac_Assoc.Batch_Id
                And Ac_Trade.Batch_Seq_Nbr = Ac_Assoc.Batch_Seq_Nbr
                And (Ac_Trade.Batch_Id = ( @{batch_id}{'1'} ))
                And (Ac_Trade.Processor_Id = ( @{processor_id}{'1'} ))
                And (Ac_Trade.Reported_Date like (CASE WHEN '@{reported_date}{}' = '' THEN '%%' ELSE '@{reported_date}' END) ))
                Where Row_Limit <= 25
                Order By Batch_ID_0,
                Batch_Seq_Nbr_1,
                AC_TL_Seq_Nbr_2,
                Contributor_ID_3,
                Account_Nbr_4,
                Account_Type_5,
                Account_Status_6,
                Opened_Date_7,
                Reported_Date_8,
                Closed_Date_9,
                Current_Balance_10,
                AC_Assoc_Seq_Nbr_11,
                ECOA_Code_12,
                SSN_13,
                Phone_Nbr_14,
                Birth_Date_15,
                Last_Name_16,
                First_Name_17,
                Middle_Name_18,
                Generation_Code_19,
                Line1_of_Addr_20,
                Line2_of_Addr_21,
                City_22,
                State_23,
                Zip_24
                • 5. Re: OBIEE 10G Direct Database Request
                  Srini VEERAVALLI
                  Looks like reported_date is in date type, make it char type and try it.
                  The column reported_date should be in char type so that the query works.
                  • 6. Re: OBIEE 10G Direct Database Request
                    756618
                    The reported_date field actually already is a 8 char length varchar field. I ran the dashboard with an actual reported date and the query came back fine. Here is where portion of the sql that OBIEE ran:

                    (Ac_Trade.Batch_Id = ( 197025 )) And (Ac_Trade.Processor_Id = ( 08300 )) And (Ac_Trade.Reported_Date like (CASE WHEN '01011900' = '' THEN '%%' ELSE '01011900' END) ))

                    Now I clear the dashboard and only enter a Batch Id and Processor Id and click the "GO" button and this is the underlying sql that runs. It looks correct but no data is returned:

                    (Ac_Trade.Batch_Id = ( 197025 )) And (Ac_Trade.Processor_Id = ( 08300 )) And (Ac_Trade.Reported_Date like (CASE WHEN '' = '' THEN '%%' ELSE '' END) ))
                    • 7. Re: OBIEE 10G Direct Database Request
                      Srini VEERAVALLI
                      Can you confirm this piece of code is works for you?
                      Ac_Trade.Reported_Date like '%%'
                      Since you are using DD Request the Results tab would helps to test

                      I would suggest start with small and simple so that we can narrow down to the issue. Take just this column and try if works then can extend to other columns.

                      That helps you to get confident/trust on my code :)
                      • 8. Re: OBIEE 10G Direct Database Request
                        756618
                        Yes, when I get the result just in the Direct Database Request area then the wildcard ('%%') search works without a problem. All my data is returning just like I would imagine