13 Replies Latest reply: Dec 3, 2008 4:41 PM by 673535 RSS

    Parameters in SubQuery

    HR/Payroll
      Hi,
      I am new to discoverer and i have a doubt. I have a query and within it is a subquery. I want to use a parameter in subquery. The query i want to achive is

      select distinct pap.full_name ,papf.payroll_name , pap.employee_number, pap.national_identifier, pap.original_date_of_hire, paf.effective_start_date
      from per_all_people_f pap,
      per_all_assignments_f paf,
           pay_all_payrolls_f papf
      where pap.person_id = paf.person_id
      and paf.payroll_id = papf.payroll_id
      and papf.payroll_name like '%'
      and paf.effective_start_date between '01-JAN-2000' and '31-DEC-4712'
      and not exists (select paf2.effective_start_date from per_all_assignments_f paf2
      where paf2.assignment_id = paf.assignment_id
           and paf2.effective_start_date < '01-JAN-2000'
      and paf2.payroll_id = paf.payroll_id)


      How can i map the paf2.effective_date in the desktop version.

      papf.payroll_name and paf.effective_start_date can be mapped using parameters since they are in outer select. how do i do the same for paf2.effective_date

      TIA
        • 1. Re: Parameters in SubQuery
          BI_Creation
          Believe or not, sub queries are no longer supported in Discoverer.

          In previous releases it was possible. But even if you are using a release that allows you to create sub-queries, I don't know if it's a good idea to use it, since discoverer do not support it anymore.

          Regards,
          Marcos
          • 2. Re: Parameters in SubQuery
            RussProudman
            Hey Shash. Love seeing the Oracle Apps tables being hit - been there many times!

            Unfortunately, you cannot pass any parameters into a query - period.

            You're query could return all values and in Disco Desktop / Plus, etc. you could have a condition that limits the data coming back to it with parameters entered by the user ... but in the actual SQL - no way.

            I know it sucks, but I've had to deal with it time and again. You have to try and create that SQL with some kind of coded date such as it's automatically gets the SYSDATE and runs the month previous to SYSDATE, or days, weeks, etc. before or after (as remember, Oracle date manipulation is pretty damn good).

            However, yoiu simply cannot get any parameter into a SQL statement (whether it's the main SQL query or a sub-query).

            Russ
            • 3. Re: Parameters in SubQuery
              394073
              Hi Russ,


              Refer to the following:

              Re: Parameters in Discoverer Administration

              Regards,

              John
              • 4. Re: Parameters in SubQuery
                Rod West
                Hi,

                Look at Re: Passing multiple parameters into Custom Folder... for a solution to implement effective date in Discoverer.

                Rod West
                • 5. Re: Parameters in SubQuery
                  RussProudman
                  I'm going to have to try this ...

                  A heck of alot of work - and quite the kludge - but if it works ... I'll let you know if I got it working as it's a useful trick to keep in my back pocket.

                  Russ
                  • 6. Re: Parameters in SubQuery
                    Rod West
                    Russ,

                    Not to much work really. Just run the script below in your APPS schema, map the functions into the EUL and away you go.

                    CREATE OR REPLACE CONTEXT EUL_DISCO USING EUL_DISCO
                    /

                    CREATE OR REPLACE PACKAGE EUL_DISCO
                    AS
                    FUNCTION set_context(p_name VARCHAR2,
                    p_value VARCHAR2) RETURN VARCHAR2;
                    FUNCTION show_context(p_name VARCHAR2) RETURN VARCHAR2;
                    END EUL_DISCO;
                    /

                    CREATE OR REPLACE PACKAGE BODY EUL_DISCO
                    IS

                    FUNCTION set_context(p_name VARCHAR2,
                    p_value VARCHAR2) RETURN VARCHAR2
                    AS
                    BEGIN
                    dbms_session.set_context('EUL_DISCO', p_name, p_value);
                    RETURN p_value;
                    END set_context;

                    FUNCTION show_context(p_name VARCHAR2) RETURN VARCHAR2
                    IS
                    BEGIN
                    RETURN SYS_CONTEXT('EUL_DISCO', p_name);
                    END show_context;

                    END EUL_DISCO;
                    /
                    • 7. Re: Parameters in SubQuery
                      622819
                      Rod,
                      Thanks for posting that reply. It makes a lot of sense, but I am not sure how the parameter sheet will get refreshed everytime I run my other workbook? So if the sheet that sets the parameters is in a different workbook or the same workbook from the one that has the actual data, how do i make sure that the parameter worksheet runs and set the contexts before they are accessed?

                      Thanks!
                      Sheena
                      • 8. Re: Parameters in SubQuery
                        Rod West
                        Hi,

                        Basically, your users will have to run a separate sheet to input the parameters.

                        Often it is helpful, to combine this with a logon trigger, so that default values are loaded into the contexts at logon. The users will then just need to run a separate parameter sheet if they want to change the session parameters.

                        Once the parameters in the contexts are set, they remain set for the rest of the Discoverer session.

                        Rod West
                        • 9. Re: Parameters in SubQuery
                          622819
                          Rod, Thanks for the response. How does this differ then from setting the USERENV in disco. I use that currently to pass a parameter back to the view but I have not been successful in passing more than one param. This variable is set in the actual worksheet and not a seperate param worksheet and works fine. So why does the setting context has to be set beforehand and cannot be done in the same sheet??

                          Much Appreciate your response!
                          Sheena
                          • 10. Re: Parameters in SubQuery
                            Rod West
                            Hi,
                            How does this differ then from setting the USERENV in disco
                            Every database session has a USERENV context that you could use. Defining your own context enables you to have multiple parameters. Also many applications use the USERENV context and setting your own values could overwrite values set by the application.
                            So why does the setting context has to be set beforehand and cannot be done in the same sheet
                            When you run a worksheet in Discoverer a single SQL SELECT statement is run in the database. In an SQL SELECT statement you can use a PLSQL function call to set a context and a call to SYS_CONTEXT to check a context value. If you try and set and check a context in the same SQL statement you will find that usually the PLSQL function call is made after calls to SYS_CONTEXT so that the value you retrieve from SYS_CONTEXT is not the value you set with the function. However, there is no rules about when or how often PLSQL functions are called within an SQL statement, so trying to set and get a context in the same SQL statement will always be unreliable, and you will get different results depending on how the SQL execution plan is processed. Therefore you should always set and get a context in separate SQL SELECT statements. This means that in Discoverer you need a separate worksheet to set the parameters. Of course, the worksheet you use to set the parmaeters could be in the same workbook as the worksheet you use to run the report.

                            Rod West
                            • 11. Re: Parameters in SubQuery
                              673535
                              Hello Rod,

                              Your input for passing parameters was useful to me while generating Discoverer report with dynamic date parameter used in subquery. I have created seperate worksheet to pass this parameter.

                              But now we have requirement to pass another parameter which is based on database column and may have multiple values. Ideally we would have used p_name in (value1,value2,...)
                              But how to handle this if I want to pass it through parameter worksheet?

                              Thanks.
                              • 12. Re: Parameters in SubQuery
                                Rod West
                                Hi,

                                You cannot use this approach to pass a parameter that has multiple values. There are two workarounds that you can use:

                                - use multiple parameters, so you define a fixed number of optional parameters, say, PARAM1,PARAM2,PARAM3,PARAM4 and PARAM5. Then set them in a separate workbook and then use a condition col IN (SYS_CONTEXT('NAMESPACE','PARAM1'), SYS_CONTEXT('NAMESPACE','PARAM2'), SYS_CONTEXT('NAMESPACE','PARAM3'), SYS_CONTEXT('NAMESPACE','PARAM4'), SYS_CONTEXT('NAMESPACE','PARAM5'))

                                - join your subquery to you list of values folder. If you have a parameter that is based on a LOV then there will be a folder that defines the LOV. You should be able to join the subquery folder to the LOV folder either in the EUL or in a custom folder and use the parameter to select from the LOV folder.

                                Rod West
                                • 13. Re: Parameters in SubQuery
                                  673535
                                  Thanks Rod... I tried first option to pass parameters.. and it seems to be working fine for me...