9 Replies Latest reply: Jun 18, 2013 6:57 AM by LT.ora RSS

    query with master and detail

    3004
      i have two block one master &detail
      can the user query in detail block
      and how?
      Thanks
        • 1. query with master and detail
          3004
          <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by joujou:
          i have two block one master &detail
          can the user query in detail block
          and how?
          Thanks
          <HR></BLOCKQUOTE>


          Hi joujou,
          If u have related the master and detail blocks then the detail can be queried while querying the master.The detail cannot be queried without the master.Hope i have cleared ur doubt.
          null
          • 2. query with master and detail
            3004
            Hi Joujou,

            Solution 1 :
            You can query the detail, move your cursor into the detail block and execute the query, here you will not be able to see the master.

            Solution 2 :
            If the user has particular criteria to be executed for detail and you need to execute the master for the detail value.
            then store the criteria in local variables, get out of enter-query mode. change the default where clause of the master by selecting only those masters for the child criteria and execute the query.

            eg.
            master : Dept
            detail : Emp

            Now I want to query for Emp 10.

            key-execute trigger
            -------------------
            declare
            l_empno number(3) := :empno;
            def_where varchar2(100);
            begin
            If :system.cursor_block = 'EMP' Then
            If :system.mode = 'ENTER-QUERY' Then
            Exit_Form(No_Validate);
            /* come out of enter-query mode */
            Def_Where := 'select deptno from emp where empno = '&#0124; &#0124;l_empno;
            Set_Block_Property('Dept', Default_Where, def_where);
            gl_block('dept');
            Execute_Query;
            End If;
            End If;
            end;

            regards,

            Momen.
            • 3. Re: query with master and detail
              LT.ora

              Guru's I had a similar query so thought of starting from here;

               

              ok my Question is in the e.g.

              master : Dept

              detail : Emp

               

              Now I want to query [detail : Emp] for Saray >3000

              the result is more than 1


               

              7566JONESMANAGER783902-APR-81297520
              7698BLAKEMANAGER783901-MAY-81285030
              7788SCOTTANALYST756619-APR-87300020
              7839KINGPRESIDENT17-NOV-81500010
              7902FORDANALYST756603-DEC-81300020


              in this case is it possible that if we are in dept no is 20 the master show details of dept 20,if 30 then 30....and so on ?

              show master records according to search results.

              • 4. Re: query with master and detail
                mostafiz mitul

                modify


                Def_Where := 'select deptno from emp where empno = '&#0124; &#0124;l_empno;


                add here  <<     and salary > v_sal            >>


                and say

                answer of Momen is correct or not


                thanks

                mostafiz mitul

                • 5. Re: query with master and detail
                  LT.ora

                  Hi Mastafiz,

                   

                  is the KEY-EXEQRY trigger on master or detail block ?

                  requirement is showing master records according to search results in detail section.


                  moreover if the user chooses more than 1 condition ( dynamic ) what happens ?


                  EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO

                   




                  -LT

                  • 6. Re: query with master and detail
                    mostafiz mitul

                    .................................................

                    trigger is in detail block - > emp

                    to fullfill the requirement of salary > 3000

                     

                    Set_Block_Property('emp', Default_Where, def_where);



                    can i pass the exam??????????????



                    • 7. Re: query with master and detail
                      LT.ora

                      Chill mostafizmitul,

                       

                      Yep you pass with flying colors

                      but to be frank my real requirement is

                      user chooses more than 1 condition.


                      - LT

                      • 8. Re: query with master and detail
                        mostafiz mitul

                        LT.ora

                        Dear sir

                         

                        <<< requirement is showing master records according to search results in detail section. >>>

                        i'm enough sure that u r confirm more than me .... detail block show the query result, according to data of master block

                        .......................................................

                        <<<moreover if the user chooses more than 1 condition ( dynamic ) what happens ?>>>

                         

                        may be it can be a solution.....

                        1. take a non database block as master block

                        2. get all the possible field that can be search

                        3. get search condition field.. ... that is .... < less than ...  > greater than ... = equal to and so on

                         

                        and than generate a where clause of sql statement according to user provided search condition

                        set the statement to

                        Set_Block_Property('emp', Default_Where, def_where);

                        execute_query;

                         

                        thanks

                        mostafiz mitul

                        • 9. Re: query with master and detail
                          LT.ora

                          Hey Mostafiz Mitul,


                          That was really helpfull! thanks a lot buddy


                          - LT