5 Replies Latest reply on Jul 14, 2020 11:58 AM by MarcLaf

    Where Clause in Placeholder




      I've got a report, build with Report Builder 11g. I have a Placeholder (CP_WHERE) and a parameter (P_WHERE) in my report. I pass the WHERE clause from a FORM in P_WHERE:


      where c.date_accepted between '09-JUL-19' and '12-JUL-19'

        and pxs.pxs_id_associated is NULL

        and c.org_id_orig = o.org_id --ta_code_orig

        and oxa_orig.oxa_id = c.oxa_id_orig

        and o2.org_id = oxa_orig.org_id

        and oxa_dest.oxa_id = c.oxa_id_dest

        and o3.org_id = oxa_dest.org_id

        and c.org_id_dest = o4.org_id

        and r.prc_id(+) = c.prc_id

        and p.csg_id = c.csg_id

        and pxs.pcs_id = p.pcs_id

        and pxs.shp_id = s.shp_id

        and o5.org_id = s.org_id_orig

        and o6.ORG_ID = s.ORG_ID_DEST

        and o7.org_id = s.ORG_ID_NEXT

        and o8.org_id = s.ORG_ID_CAR

        and p.chd_id = h.CHD_ID(+)

          order by o.code, o2.code, o3.code, o4.code


      In my report, I copy the parameter in the placeholder:


      function BeforeReport return boolean is


        :CP_WHERE := :P_WHERE;

         return (TRUE);



      My Group query is:


      select o.code T, ''''||to_char(c.date_raised,'YDDD') || '''' C, ''''||c.packed_by||'''' N, ''''||c.SEQ_NUM||'''' NUM,

      to_char(c.date_accepted, 'DD-MON-YY HH24:MI') CARF, ''''||o2.code||'''' FROM_CODE, ''''||o3.code||'''' DEST_CODE,

      ''''||o4.code||'''' DTA, r.code PRI, c.customs_flag, c.INTF_REFERENCE_NO CFSS,

      c.general_search GEN_SEARCH, o5.code W, ''''||s.year_num||'''' S, ''''||s.yearly_seq_num||'''' BL, ''''||o6.code||'''' WDTA, ''''||o7.code||'''' NTA, o8.code CAR,

      p.transp_status ind, to_char(s.dispatch_date, 'HH24:MI') TIME_, s.dispatch_date DISP,s.rdd, TO_CHAR(s.charge_amount, '$9,999,999.99') COST,

      s.payment_type_flag COLLECT_, s.ta_signature, TO_CHAR(ceil(p.m_weight*2.2046), '999,999,999') LBS, TO_CHAR(ceil(p.m_volume*35.3144), '99,999,999') CUFT, ''''||p.pid||'''' PID,

      p.dg_flag DG, TO_CHAR(p.CUST_FAIR_MARKET_VALUE, '$999,999,999.99') VALUE, p.descr, h.chd_code HARM

      from csg c, org o, org o2, org o3,org o4, org o5, org o6, org o7, org o8,prc r, pcs p, shp s, pxs, chd h, oxa oxa_orig, oxa oxa_dest



      When the WHERE Clause is passed in by the form, I GET AN ERROR when I execute the report:




      But I I hardcode the WHERE in my PLACEHOLDER (CP_WHERE)... Everything runs fine when I execute the report!!!


      First question, why does it give me an error when I pass the WHERE in the report


      Second question, is it possible to get the ERROR code and ERROR message and write it into a TABLE, when I get the TERMINATED_WITH_ERROR ???


      Thanks in advance,


      Marc L