CODEWITHSUNDEEP
sqloracle-databasereportoracle-apex
Syd
Syd

Reputation: 27

Using item value as part of the select code

I'm trying to use a feature on oracle apex 20 that was working fine in apex 5.1. I have a interactive report, and at the very and I add value of the item like this: '''

SELECT                                                        /* PARALLEL(4)*/
       o.id,
       NULL
           pregled,
       o.OS_TIP,
       o.SIFRA,
       o.OS_TIP_PRETH,
       o.PRETHODI,
       o.OS_TIP_MASTER,
       o.MASTER,
       u.naziv || ' - ' || o.UGOVARATELJ
           Ugovaratelj,
       p.naziv || ' - ' || o.POSREDNIK
           Posrednik,
       t.naziv || ' ' || o.TRAJANJE
           Trajanje,
       o.POCETAK_OSIGURANJA,
       o.ISTEK_OSIGURANJA,
       o.POCETAK_PREMIJE,
       o.KRAJ_PREMIJE,
       o.DATUM_IZDANJA,
       o.BROJ_RATA,
       o.PREMIJA,
       (  SELECT CASE
                     WHEN MONTHS_BETWEEN (
                              TO_DATE (osd.kraj_premije, 'DD.MM.YYYY'),
                              TO_DATE (osd.pocetak_premije, 'DD.MM.YYYY')) <
                          12
                     THEN
                         ROUND (SUM (st.premija_neto), 2)
                     ELSE
                         ROUND (
                               SUM (st.premija_neto)
                             / NVL (
                                   (  (FLOOR (
                                           MONTHS_BETWEEN (
                                               TO_DATE (osd.kraj_premije,
                                                        'DD.MM.YYYY'),
                                               TO_DATE (osd.pocetak_premije,
                                                        'DD.MM.YYYY'))))
                                    / 12),
                                   1),
                             2)
                 END    godisnja
            FROM os_dokument osd, stavka_dokumenta st
           WHERE     st.os_tip(+) = osd.os_tip
                 AND st.sifra(+) = osd.sifra
                 AND osd.os_tip(+) = o.os_tip
                 AND osd.sifra(+) = o.sifra
        GROUP BY osd.pocetak_premije, osd.kraj_premije)
           godisnja_premija,
       r.naziv || ' - ' || o.REFERENT
           Referent,
       prodaja.F_STATUS_POLICE (O.OS_TIP, O.SIFRA)
           STATUS_POLISE,
       (SELECT forma
          FROM odobrenje
         WHERE odobrenje = o.odobrenje)
           status_odobravanja,
       sd.grupacija || sd.grupa || '.' || sd.cjenik
           tarifa,
       dms.postoji_dokument (o.id)
           postoji_dokumentacija,
       (SELECT k.tekst
          FROM os_dokument_komentar k
         WHERE k.OS_DOKUMENT_ID = o.id AND k.indikator = 1)
           poruka,
       o.korisnik,
       DECODE ((SELECT k.tekst
                  FROM os_dokument_komentar k
                 WHERE k.OS_DOKUMENT_ID = o.id AND k.indikator = 1),
               NULL, 'background-color:white',
               'background-color:lightblue')
           CSS_STYLE_PORUKA,
       DECODE (dms.postoji_dokument (o.id),
               'NE', 'background-color:white"',
               'background-color:lightblue')
           CSS_STYLE_DMS
  FROM OS_DOKUMENT       o,
       in2_stranka       p,
       in2_stranka       r,
       in2_stranka       u,
       in2_stranka       pl,
       trajanje          t,
       stavka_dokumenta  sd
 WHERE     o.posrednik = p.jmbg(+)
       AND o.referent = r.jmbg(+)
       AND o.ugovaratelj = u.jmbg(+)
       AND o.trajanje = t.trajanje(+)
       AND (o.os_tip = 'P08' OR o.os_tip LIKE 'N1%')
       AND o.id = sd.id_os_dokument(+)
       AND sd.rb(+) = 1
       AND o.platitelj = pl.jmbg(+)
       &FILTER.

''' FILTER is a hidden page item that is set with a process on page load, and it contains some code. In the simplest version, it will contain "and 1=1", or it can contain some additional condition like "and o.POSREDNIK = '12345'" As I said in Apex 5.1 this works just fine. But in Apex 20 I can not even validate the code in code editor, I get " ORA-20999: Failed to parse SQL query!

ORA-06550: line 53, column 27: ORA-00933: SQL command not properly ended

" Same select without &FILTER. at the end works just fine. Any suggestion how to get this to work in Apex 20? Or how to insert some code into the select ?

Upvotes: 0

Views: 230

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

Exactly, it doesn't work any more.

However, it will work if you choose function body returning SQL query as the source type (instead of SQL query).

Really nice feature of Apex 20 is that it automatically converts your query into the RETURN clause, using the q-quoting mechanism to prevent possible single quote issues. So: you just have to change the source type - Apex will do the rest for you and the report should work properly. At least, it does for me.

Upvotes: 0

Related Questions