1 2 Previous Next 18 Replies Latest reply: Sep 18, 2013 3:17 AM by Ivan2405 Go to original post RSS
      • 15. Re: Select query in a report
        Ivan2405

        I think it works now. The only problem is that the users have to put strings in quotations marks.

        Also, I think there will be a problem when I create 4 more such rows, in case users don't use all of them.


        Regards,

        Ivan

        • 16. Re: Select query in a report
          Ivan2405

          OK, I half expected the following problem, now I have to bypass it.

          This is the query:

           

          begin

          :P99_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,DZS_IDY_CL_ID,HNB_IP_CL_ID,EXG_RT_CRD_RSK_F from DWP.IZV_SLOG_DET

          where ' || :P99_X_COL || :P99_X_O || :P99_X_V || ' and '

          || :P99_X_COL2 || :P99_X_O2 || :P99_X_V2 || ' and '

          || :P99_X_COL3 || :P99_X_O3 || :P99_X_V3 || ' and '

          || :P99_X_COL4 || :P99_X_O4 || :P99_X_V4 || ' and '

          || :P99_X_COL5 || :P99_X_O5 || :P99_X_V5 ;

          return :P99_QUERY;

          end;

           

          The problem is that the query always ends in error in case users don't fill all the fields. Is there a way I could circumvent this? My only idea is to set default values, but this too probably will hinder the users since they won't want to filter by these columns; therefor they will be obliged to change everyhing...


          Regards,

          Ivan

          • 17. Re: Select query in a report
            nvncro

            try to set default value to null

             

            then modify your query for every "AND" statement in a way that if user didn't enter anything your query still returns rows.

             

            something like this:

            :P99_X_COL5 || :P99_X_O5 || :P99_X_V5 || ' OR  (:P99_X_COL5 is null and :P99_X_O5 is null  and  :P99_X_V5  is null) '



            ps- make your own workspace for putting here on forum your problems, it is easyer to work on examples

            • 18. Re: Select query in a report
              Ivan2405

              I tried something like that, but then the expression is not valid. This is the query I get:

               

              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,DZS_IDY_CL_ID,HNB_IP_CL_ID,EXG_RT_CRD_RSK_F

              from DWP.IZV_SLOG_DET

              where SRC_STM_ID = 'OPICS' or (SRC_STM_ID is null and = is null and 'OPICS' is null)

              and BNK_ID = 1 or (BNK_ID is null and = is null and 1 is null)

              and BNK_ID  like  '1%' or (BNK_ID is null and  like  is null and '1%' is null)

              and VALUTA  in  ('HRK','EUR') or (VALUTA is null and  in  is null and ('HRK','EUR') is null)

              and HNB_IP_CL_ID  like  'HNB1%' or (HNB_IP_CL_ID is null and  like  is null and 'HNB1%' is null);

               

              The problem is that I get a missing expression in the (SRC_STM_ID is null and = is null and 'OPICS' is null) .

               

              Regards,

              Ivan

              1 2 Previous Next