5 Replies Latest reply on Jul 9, 2020 6:59 PM by MarcLaf

    Query at runtime

    MarcLaf

      Hi,

       

      I'm running Oracle Reports 11g. I have one group query referencing user parameter (P_TA_CODE). Is there a way to display or insert into a table the query with the parsed parameter values (at runtime)??

       

      Query:

       

      SELECT ORG_TA.CODE TA_CODE_ORIG, ''''||TO_CHAR(CSG.DATE_RAISED, 'YDDD')||'''' DATE_RAISED, ''''||CSG.PACKED_BY||'''' PACKED_BY, ''''||CSG.SEQ_NUM||'''' SEQ_NUM,

      ''''||PCS.PID||'''' PID, PCS.TRANSP_STATUS, ''''||PCS.TRANSP_TA||'''', TO_CHAR(CEIL((PCS.M_WEIGHT*2.2046)), '9,999,999') I_WEIGHT,

      TO_CHAR(CEIL(PCS.M_WEIGHT),'999,999') M_WEIGHT, TO_CHAR(CEIL((PCS.M_VOLUME * 35.3144)), '999,999') I_VOLUME,

      TO_CHAR(CEIL(PCS.M_VOLUME),'99,999') M_VOLUME,PCS.DESCR, PTY.PTY_CODE PTY_CODE,CSG.INTF_REFERENCE_NO,

      ''''||ORG_DTA.CODE||'''' DTA, DEST_ORG_CODE.CODE DEST_ADR_ORG_CODE

      FROM CSG, ORG ORG_TA, PCS, PTY, ORG ORG_DTA, ORG DEST_ORG_CODE, OXA

      WHERE (PCS.M_VOLUME * 35.3144) > 100

      AND PCS.TRANSP_STATUS <> 'U'

      AND (:P_TA_CODE is NULL OR ORG_TA.CODE not in (:P_TA_CODE))

      AND CSG.DATE_ACCEPTED BETWEEN :P_START_DATE and :P_END_DATE

      AND CSG.ORG_ID_ORIG = ORG_TA.ORG_ID

      AND CSG.ORG_ID_DEST = ORG_DTA.ORG_ID

      AND CSG.OXA_ID_DEST = OXA.OXA_ID

      AND OXA.ORG_ID = DEST_ORG_CODE.ORG_ID

      AND PCS.CSG_ID = CSG.CSG_ID

      AND PCS.PTY_ID = PTY.PTY_ID

      ORDER BY ORG_TA.CODE, TO_CHAR(CSG.DATE_RAISED, 'YDDD'), CSG.PACKED_BY, CSG.SEQ_NUM

       

       

      Thanks in advance,

       

      Marc L.

        • 1. Re: Query at runtime
          Holger.Lehmann

          yes, you can use the before and after triggers in the reports.

           

          maybe after Parameter form Trigger.

          • 2. Re: Query at runtime
            MarcLaf

            Thanks for the reply. I inserted the parameter value in a table, works well.

             

            Cheers!

            • 3. Re: Query at runtime
              MarcLaf

              Hi,

               

              Is there a way to get (display/insert into table) the Query that the report is executing with the parameter? I got ONLY ONE qry defined, but I want to see how Oracle Reports builds the qry with the parameter value...

               

              Thanks in advance.

               

              Marc L

              • 4. Re: Query at runtime
                Holger.Lehmann

                Hi Marc,

                 

                in Forms there is a:SYSTEM.LAST_QUERY, but in reports there is a workaround:

                 

                copied from another page:

                 

                I copied the query text and concatenated in all the parameters like &LP_ORDER_BY and then displayed it with an SRW_MESSAGE in the BEFORE_REPORT trigger. Bit tedious, they should have some available field that holds the query.

                 

                another way could be looking in the session info of the database.... and get the query from there.

                • 5. Re: Query at runtime
                  MarcLaf

                  Ok,

                   

                  This is how I did my query in my report, it's a DYNAMIC WHERE clause. It is passed as a parameter from a FORM...

                   

                  QRY (in report):

                   

                  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

                  &CP_WHERE

                   

                  CP_WHERE is a parameter (Char 2000) in report.

                  P_WHERE is a parameter (Char 1000) in report.

                   

                  In my FORM:

                   

                  T_TA_STRING := 'where c.date_accepted between '''||:CB_TEMPVAR.START_DATE||''' and '''||

                                      :CB_TEMPVAR.END_DATE|| '''

                  and pxs.pxs_id_associated is NULL

                  and c.org_id_orig = o.org_id

                  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';

                   

                  add_parameter(pl_id,'p_where',TEXT_PARAMETER,t_ta_string);

                   

                  run_status := run_report(:PARAMETER.P_REP_ID, 'BATCH', 'SYNCHRONOUS', 'PDF', pl_id);

                   

                   

                  Back to report:

                   

                  function BeforePForm return boolean is

                  begin

                    :CP_WHERE := :P_WHERE;

                    return (TRUE);

                  end;

                   

                   

                  But when I run the form and click on execute report button, I get this error still:

                   

                  The report terminated with error:
                  REP-300: SQL command not properly ended 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 , '''' || .

                   

                   

                  Thanks in advance.

                   

                  Marc L