1 2 Previous Next 20 Replies Latest reply: May 13, 2012 10:47 PM by arms777 RSS

    Displaying result of query informs

    arms777
      Hi. I would like to display records (output of a query) in the top portion of a form like this:

      BIRTHDATE

      5/8/2000
      5/8/1969
      5/8/2001

      This is a result of a query (Select BIRTHDATE from EMP where to_date(to_char(BIRTHDATE, 'dd-mon'), 'dd-mon') = to_date(to_char(:parameter, 'dd-mon'), 'dd-mon').

      And on the bottom portion are several items from the same table such that when I click on each record (birthdate) on the top portion of the form, the corresponding value of the other items (Name, Age, Address) will be updated on the bottom portion.

      Name: Age: Address:


      I hope you understand my point. Actually, the displaying of the top portion is my problem, how do I do this from a forms query? Thanks
        • 1. Re: Displaying result of query informs
          HamidHelal
          Build the upper part block from ,from clause query.
          Check this link...http://sites.google.com/site/craigsoraclestuff/oracle-forms---how-to-s/forms-how-to---base-a-block-on-a-from-clause-query


          Hope this helps
          • 2. Re: Displaying result of query informs
            arms777
            Thanks. I tried to create a listbox and then create a parameter date item and a push button that contains this:


            DECLARE
            rg_list_id RECORDGROUP;
            rg_name VARCHAR2(20) := 'RG_LIST';
            ret_code NUMBER;
            v_select VARCHAR2(500);
            BEGIN
            v_select := 'SELECT to_char(BIRTHDATE, ''dd-mon-yyyy hh24:mi:ss''), to_char(BIRTHDATE, ''dd-mon-yyyy hh24:mi:ss'')
            FROM EMP
            where to_date(to_char(BIRTHDATE, ''dd-mon-yyyy''), ''dd-mon-yyyy'') = to_date(to_char(:query_date, ''dd-mon-yyyy''), ''dd-mon-yyyy'')';

            rg_list_id := FIND_GROUP(rg_name);
            IF NOT Id_Null(rg_list_id) THEN
            DELETE_GROUP(rg_list_id);
            END IF;
            rg_list_id := CREATE_GROUP_FROM_QUERY(rg_name, v_select);
            ret_code := POPULATE_GROUP(rg_list_id);
            POPULATE_LIST('LIST141','RG_LIST');
            DELETE_GROUP(rg_list_id);
            END;

            However, I get a FRM-41337 error. But if i replace my parameter :query_date with an actual date, say '28-FEB-2012', it is working fine. What is the problem with my parameter? Though after solving this problem, I will also try to use the From Clause query you have suggested. Thanks again.
            • 3. Re: Displaying result of query informs
              HamidHelal
              arms777 wrote:
              v_select := 'SELECT to_char(BIRTHDATE, ''dd-mon-yyyy hh24:mi:ss''), to_char(BIRTHDATE, ''dd-mon-yyyy hh24:mi:ss'')
              FROM EMP
              where to_date(to_char(BIRTHDATE, ''dd-mon-yyyy''), ''dd-mon-yyyy'') = to_date(to_char(:query_date, ''dd-mon-yyyy''), ''dd-mon-yyyy'')';
              However, I get a FRM-41337 error. But if i replace my parameter :query_date with an actual date, say '28-FEB-2012', it is working fine.
              try this..
              v_select := 'SELECT to_char(BIRTHDATE, ''dd-mon-yyyy hh24:mi:ss''), to_char(BIRTHDATE, ''dd-mon-yyyy hh24:mi:ss'')
              FROM EMP
              where to_date(to_char(BIRTHDATE, ''dd-mon-yyyy''), ''dd-mon-yyyy'') = to_date(to_char('||:query_date||', ''dd-mon-yyyy''), ''dd-mon-yyyy'')';
              Hope this helps
              • 4. Re: Displaying result of query informs
                Sarah
                hi

                are you able to get the result in sql?
                plz check your select statement in sql first then try.


                sarah
                • 5. Re: Displaying result of query informs
                  arms777
                  Yes, actually i could get the result when i use an explicit value for :query_date like '28-Feb-2012'. But when i use the parameter :query_date, i get the form error. Thanks
                  • 6. Re: Displaying result of query informs
                    Sarah
                    hi

                    try to use single quotes instead of double.

                    what is the datatype of that item?
                    if its not date plz check.
                    sarah
                    • 7. Re: Displaying result of query informs
                      HamidHelal
                      hey, try this...
                      v_select := 'SELECT to_char(BIRTHDATE, ''dd-mon-yyyy hh24:mi:ss''), to_char(BIRTHDATE, ''dd-mon-yyyy hh24:mi:ss'')
                      FROM EMP
                      where to_date(to_char(BIRTHDATE, ''dd-mon-yyyy''), ''dd-mon-yyyy'') = to_date(to_char('||:query_date||', ''dd-mon-yyyy''), ''dd-mon-yyyy'')';
                      • 8. Re: Displaying result of query informs
                        InoL
                        This looks to me like a standard form with a base table block on table EMP.
                        - Give the block number of records displayed = 5 (or something else).
                        - Give the item birthdate number of records displayed = 0
                        - Give the other items number of records displayed = 1

                        The part where you want to filter on dd-mon (instead of filtering on a complete date of birth) can be handled in the pre-query trigger.
                        • 9. Re: Displaying result of query informs
                          arms777
                          Thanks. But i used two single quotes not double quotes, and i don't think i can use single quotes since the whole query is already inside single quotes. Also, the datatype of the item is date. I'm just having a problem with replacing the explicit value like '28-FEB-2012' with a parameter. Thanks again.
                          • 10. Re: Displaying result of query informs
                            arms777
                            I'm sorry i failed to see your response the first time. I tried your suggestion but i had another error, 'FRM-41072 Cannot create group RGLIST'. I researched on the possible causes of this error and i found three:
                            1. Duplicate column names in SQL statement. 2. Invalid record group name. 3. Query is invalid.

                            First, i tried removing the other selected item (to_char(BIRTHDATE, ''dd-mon-yyyy hh24:mi:ss'')) in the SELECT statement. The form compiled but still the same error when run.

                            Second, for the invalid group name, I don't know how to check if this (RG_LIST) is an invalid group name, but i guess it is a valid one.

                            And lastly, i tried running the same query in TOAD (of course removing the extra quotes) and it is working fine.

                            I don't know what is causing the error. Thanks for your response.
                            • 11. Re: Displaying result of query informs
                              arms777
                              InoL wrote:
                              This looks to me like a standard form with a base table block on table EMP.
                              - Give the block number of records displayed = 5 (or something else).
                              - Give the item birthdate number of records displayed = 0
                              - Give the other items number of records displayed = 1
                              I tried giving the block property, number of records displayed a value of 10 but the block contains several radio buttons, which do not have the number of records displayed property so i cannot change them to a value of 1.

                              - The part where you want to filter on dd-mon (instead of filtering on a complete date of birth) can be handled in the pre-query trigger.
                              I'm sorry, i do not get this. The thing is, if i replace the parameter, query_date with an explicit value like '11-MAY-2012', i get the correct results. I think it is just a matter of the correct syntax for the parameter in this context, but i don't know how. Thanks
                              • 12. Re: Displaying result of query informs
                                Amatu Allah Neveen Ebrahim
                                Hi

                                May i ask u why r keeping up the date of birth in the header form ?
                                Not depart_no in header then the employess details in the detail block with all of ur stuff...
                                what u wanna do exactly ?

                                Regards,

                                Amatu Allah.
                                • 13. Re: Displaying result of query informs
                                  arms777
                                  Actually i just used the EMP table as an example to simplify my query, the same is true with the birthdate. My actual table contains line outages details. What i wanted to do was to produce an output of all outages (corresponding to birthdate) for any day that the user inputs on the parameter :query_date. Sample output when '28-FEB-2012' was used instead of the parameter :query_date is like this:

                                  V_OUT_DATE

                                  2/28/2012 09:56:00
                                  2/28/2012 22:16:00
                                  2/28/2012 07:47:00
                                  2/28/2012 14:45:00
                                  2/28/2012 11:27:00
                                  2/28/2012 16:42:00


                                  Once i get these, i wanted to display the details of every v_out date selected above, meaning if i click my mouse on the third record, *2/28/2012 07:47:00* , i would like to display the details (e.g. Name of Line, Length of line, etc.)of that particular outage at the bottom of my form. I hope you get my point.

                                  I tried using the FROM clause query and i have this on my pre-query:

                                  DECLARE
                                       
                                  MYQUERY      VARCHAR2(1500);
                                       
                                  BEGIN
                                       
                                  MYQUERY := '(SELECT to_date(to_char(V_OUT_DATE, ''dd-mon-yyyy hh24:mi:ss''),''dd-mon-yyyy hh24:mi:ss'') V_OUT_DATE
                                  FROM SPM_DAILY_OUTS
                                  WHERE to_date(to_char(v_out_date, ''dd-mon-yyyy''), ''dd-mon-yyyy'') = TO_DATE(TO_CHAR('||''''||:SPM_DAILY_OUTS1.QUERY_DATE||''''||',''DD-MON-RRRR''),''DD-MON-RRRR''))';

                                  SET_BLOCK_PROPERTY('SPM_DAILY_OUTS1', QUERY_DATA_SOURCE_NAME, MYQUERY);

                                  END;


                                  However, it says Query caused no records to be retrieved. I tried displaying the :system.last_query and i get these output:

                                  SELECT V_OUT_DATE
                                  FROM (SELECT to_date(to_char(V_OUT_DATE, 'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss') V_OUT_DATE
                                  FROM SPM_DAILY_OUTS
                                  WHERE to_date(to_char(v_out_date, 'dd-mon-yyyy'), 'dd-mon-yyyy') =
                                  TO_DATE(TO_CHAR(TO_DATE('','DD-MON-RRRR'),'DD-MON-RRRR'),'DD-MON-RRRR'));

                                  I had tried changing the datatype of :query_date from date to char but still i get the same result. It does not seem to get the value of my parameter. What am i doing wrong?
                                  • 14. Re: Displaying result of query informs
                                    Amatu Allah Neveen Ebrahim
                                    Hi arms77

                                    Sorry to say ,this won't work; i mean the way u r trying to display ur query pls look for Craig's Stuff on how to base a tabel on a Form Clause Query and read carefully if u want...

                                    But,once i had to format or organize the data display of one tabel to tab canvas ,stacked etc. and take this as an example my partner at work display date in the header form from LOV using...

                                    The following code in KEY-ENTQUERY Trigger
                                    IF :SYSTEM.MODE = 'ENTER-QUERY' THEN
                                    
                                    GO_BLOCK('EMP');
                                    
                                    EXECUTE_QUERY;
                                    
                                    ELSE
                                    
                                    MESSAGE ('Pls select ur Search Date First');
                                    
                                    RAISE FORM_TRIGGER_FAILURE;
                                    
                                    END IF;
                                    By this way u force the user to seleCT the date or freeze and u will be abel to display ur related data record by record...

                                    Hope this way saves u time & effort...

                                    Regards,

                                    Amatu Allah.
                                    1 2 Previous Next