4 Replies Latest reply: Dec 2, 2012 9:06 PM by user10569054 RSS

    Like Condition in Subquery

    user10569054
      Hello,
      I've a report with three prompts 1.Division 2.Deptid and 3.Date in query manager..
      First 2 prompts are in sub query which are passing Emplids to main query from the division and department prompt.
      Now in Department prompt what i want is if User select for eg. marketing department... MK00000 then under Marketing MK0.. all dept should be selected. just like in sql deptid like 'MK%'.
      So i'm trying with like clause in a Prompt with Edit type NO TABLE EDIT and a prompt table but in Query actually selected is complete dept id i.e. deptid like MK00000 so i'm getting only one dept data.
      How do i pass a value to department prompt with value like MK0% or MK% and return with all department data?

      Tools Release      8.49.27
      Application Release      HRMS and Campus Solutions 9.00.00.000
      DB Oracle 10.2

      Thanks in Advance!
        • 1. Re: Like Condition in Subquery
          HakanBiroglu
          How do i pass a value to department prompt with value like MK0% or MK% and return with all department data?
          Exactlly as you say it, because the prompt is of no table edit, you can enter what you want and in order to properly construct your sql you need to enter MK% in the prompt as value.


          An example:
          I created a query with the following sql:
          SELECT A.OPRID, A.EMPLID
          FROM PS_OPRDEFN2 A
          WHERE ( A.OPRID LIKE :1 )

          Where :1 is a prompt on PSOPRDEFN with no table edit.
          When I run the query the prompt pops up, when I enter V% it returns all OPRIDs starting with V
          1 VDIXON
          2 VKING
          3 VP1
          4 VPORTAVINO
          5 VSNYDER

          Regards,

          Hakan
          • 2. Re: Like Condition in Subquery
            user10569054
            Thanks for the reply.
            Actually my concern is that user will select/click looking at the description not the code. And the prompt table will pass deptid e.g. MK00000
            User will not type anything e.g. MK% . He/she will only click on look up value from prompt table and select a department.
            Now when he/she selects one of the department e.g. MK00000 i need to pass value to query deptid like *'MK000%'*

            Regards!
            • 3. Re: Like Condition in Subquery
              Michel
              That can only be accomplished by creating a view which supplies a 'fake' value back to the query. You'd have to create a view which has, besides all the other fields in the lookup you're using now (probably the DEPT_TBL), an extra field with something like "SUBST(DEPTID,1,5)||'%'". You then use this view as the table prompt for the prompt value.

              When a user runs this query he will be able to select a DEPTID. When the DEPTID is selected the first key value will be supplied back to the query, which in this case will be 'MK000%'.
              • 4. Re: Like Condition in Subquery
                user10569054
                Thanks. Your Suggestion was really helpful.

                Regards!