1 2 Previous Next 18 Replies Latest reply: Sep 18, 2013 3:17 AM by Ivan2405 RSS

    Select query in a report

    Ivan2405

      Hi!

       

      Is it possible to modify a select query for a report in the following way:

       

      I have created two fields where users input values and operators.

      Operators selection is a static list consisting of: =, like , in

      Value field is a text field.


      The current query is:

       

      SELECT

      A,

      B,

      C

      from TABLE

       

      I would like to add:

       

      WHERE

      A : P1_OPERATOR : P1_VALUE

       

      but it doesn't work; I get a notification that the operator is incorrect.

      It has a defaulft value = and when I set let's say 5 in the value field the whole query should be:

       

      SELECT

      A,

      B,

      C

      from TABLE

      WHERE

      A = 5

       

      Regards!

        • 1. Re: Select query in a report
          nvncro

          It probably doesn't work because you get string from items, so your query looks like

           

          SELECT

          A,

          B,

          C

          from TABLE

          WHERE

          A '=' '5'

           

           

          Try to set function returning SQL query where you will pass these parameters and then return sql string

          • 2. Re: Select query in a report
            Ivan2405

            I tried to create a procedure that would generate a statement that would then be executed.

            However, I get an error that SELECT keyword is expected.

            So I don't know how to bypass that...

             

            Regards,

            Ivan

            • 3. Re: Select query in a report
              nvncro

              What is the query your function returns?

               

              You dont even need to write function if you have simple functionality as above mentioned, just write for example

               

              return SELECT A,B,C from TABLE WHERE A '  || :P1_OPERATOR ||  :P1_VALUE;

              • 4. Re: Select query in a report
                Ivan2405

                I tried that already, and it didn't work. I don't quite understand the one ' after A.
                This is the exact where clause:

                where :P7_X_FC1 || :P7_X_O1 || :P7_X_O1

                 

                where the values are column name, operator, value.


                Regards,
                Ivan

                • 5. Re: Select query in a report
                  nvncro

                  http://apex.oracle.com/pls/apex/f?p=4550:1:0:::::

                  MULTICOM

                  FORUMHELP

                  IVAN

                   

                   

                  page 2, look at source of report, I recreated your problem

                  When you choose operator and dept no press reset

                  • 6. Re: Select query in a report
                    Ivan2405

                    That is Workspace, Username and Password?
                    I can't login with those parameters...


                    Regards,

                    Ivan

                    • 7. Re: Select query in a report
                      nvncro

                      Sorry , wrong password, please try again, I have updated password above

                      • 8. Re: Select query in a report
                        Ivan2405

                        Hmm, I get the following error:

                         

                        Updatable report parse error:

                        ORA-20001: Query must begin with SELECT or WITH


                        The only difference I see is that your type is SQL Query (PL/SQL function body returning SQL query) while I have SQL Query (updateable report) hardcoded.

                        I have APEX 4.2.2, I'm not quite sure how to change this...

                        This is my code:

                         

                        begin

                         

                         

                        :P7_QUERY := 'select ROWID,BNK_ID,MSR_PRD_ID,SRC_STM_ID,ID,ID_RETKA,RSP,OZNAKA_RETKA,DATUM_STANJA,OZNAKA_IZVJESCA,OZNAKA_KOMITENTA,MBR_KOMITENTA,KOMITENT_NEREZ,ZUPANIJA,DRZAVA,SEKTOR_NEREZIDENTA,VRSTA_POVEZANOSTI,INSTRUMENT,ISIN,VALUTA,OTKAZNI_ROK,IZVORNO_DOSPIJECE,VRSTA_INDEKSACIJE,VALUTA_INDEKSACIJE,PORTFELJ,UTRZIVOST_KREDITA,ZNACAJKE_KAPITALA,RIZICNA_SKUPINA,UGRADJENI_DERIVAT,ODNOSNA_VARIJABLA,PREDZNAK,IZNOS,IZNOS_ACTUAL,VRSTA_IZNOSA,KOMITENT_PBR,UDJELI_POVEZ_C,AR_ID,AU_ID,ACT_AR_BAL_KN,ACT_AR_BAL,AR_BUSS_ID,MTI_CCY_TP_ID,REG_NO,REG_SFX,JMBG_ID_NO,IP_ID,NO_DYS_OO,TAX_ID_NO,INSTRUMENT_OLD,PREDZNAK_OLD,IZNOS_ACTUAL_OLD,ACT_AR_BAL_KN_OLD,ACT_AR_BAL_OLD,NAPOMENA,NOVI_POSAO_F,LISTA_SUMARNA,LISTA_REKAP,POSTOTAK1,POSTOTAK2,POSTOTAK3,DZS_IDY_CL_ID,HNB_IP_CL_ID,EXG_RT_CRD_RSK_F from DWP.IZV_SLOG_DET

                        where ' || :P7_X_FC1 || :P7_X_O1 || :P7_X_O1;

                         

                         

                        return :P7_QUERY;

                         

                         

                        end;


                        I have also set Use Generic Column Names (parse query at runtime only) nad P7_QUERY as page item to submit.

                         

                        Regards,

                        Ivan

                        • 9. Re: Select query in a report
                          nvncro

                          You dont have option SQL Query (PL/SQL function body returning SQL query) ?

                           

                          provide some screen shots please, it is easyer to do it like that.

                           

                          Is that report you use classic/interacitve report?

                          • 10. Re: Select query in a report
                            Ivan2405

                            I can't seem to upload a picture, the icon is gray.

                            Do you have an e-mail address?

                            I used classic report.

                             

                            Btw. when I ran your query I got the same error:

                             

                            failed to parse SQL query: ORA-00920: invalid relational operator 


                            Regards,

                            Ivan

                            • 11. Re: Select query in a report
                              nvncro

                              upload image here

                              http://www.deviantpics.com/

                              and post direct link for image

                               

                              you put my query in your application or?

                              You have to press reset button refresh the report in my example

                              • 12. Re: Select query in a report
                                Ivan2405

                                I can't open the link while on my computer on work, the page is blocked.

                                While I'm in your application I go to Page 2 and then run. But now it works.

                                Like I said before, I modified your query to suit my table, but I get the error:

                                 

                                begin

                                 

                                 

                                :P7_QUERY := 'select ROWID,BNK_ID,MSR_PRD_ID,SRC_STM_ID,ID,ID_RETKA,RSP,OZNAKA_RETKA,DATUM_STANJA,OZNAKA_IZVJESCA,OZNAKA_KOMITENTA,MBR_KOMITENTA,KOMITENT_NEREZ,ZUPANIJA,DRZAVA,SEKTOR_NEREZIDENTA,VRSTA_POVEZANOSTI,INSTRUMENT,ISIN,VALUTA,OTKAZNI_ROK,IZVORNO_DOSPIJECE,VRSTA_INDEKSACIJE,VALUTA_INDEKSACIJE,PORTFELJ,UTRZIVOST_KREDITA,ZNACAJKE_KAPITALA,RIZICNA_SKUPINA,UGRADJENI_DERIVAT,ODNOSNA_VARIJABLA,PREDZNAK,IZNOS,IZNOS_ACTUAL,VRSTA_IZNOSA,KOMITENT_PBR,UDJELI_POVEZ_C,AR_ID,AU_ID,ACT_AR_BAL_KN,ACT_AR_BAL,AR_BUSS_ID,MTI_CCY_TP_ID,REG_NO,REG_SFX,JMBG_ID_NO,IP_ID,NO_DYS_OO,TAX_ID_NO,INSTRUMENT_OLD,PREDZNAK_OLD,IZNOS_ACTUAL_OLD,ACT_AR_BAL_KN_OLD,ACT_AR_BAL_OLD,NAPOMENA,NOVI_POSAO_F,LISTA_SUMARNA,LISTA_REKAP,POSTOTAK1,POSTOTAK2,POSTOTAK3,DZS_IDY_CL_ID,HNB_IP_CL_ID,EXG_RT_CRD_RSK_F from DWP.IZV_SLOG_DET

                                where ' || :P7_X_FC1 || :P7_X_O1 || :P7_X_O1;

                                 

                                 

                                return :P7_QUERY;

                                 

                                 

                                end;

                                 

                                I think that it won't work because I cant choose SQL Query (PL/SQL function body returning SQL query)...

                                • 13. Re: Select query in a report
                                  Ivan2405

                                  I have created a new report from scratch. Now I can choose SQL Query (PL/SQL function body returning SQL query).

                                  I'm still working on it, I'll get back to you once I create all the elements.


                                  Regards,

                                  Ivan

                                  • 14. Re: Select query in a report
                                    nvncro

                                    yes for sure you have to choose SQL Query (PL/SQL function body returning SQL query)

                                    1 2 Previous Next