Warm tip: This article is reproduced from serverfault.com, please click

Using item value as part of the select code

发布于 2020-12-01 17:37:29

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 ?
Questioner
Syd
Viewed
0
Littlefoot 2020-12-02 05:20:02

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.