Query with specific partition table

this query only for table with partition

=========


SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF

-- Dates
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';

COLUMN S NEW_VALUE start_date
SELECT trunc(sysdate-1) S FROM dual;

---- + Debug
DEFINE ptab_td = 'p_tab_trans_data_&start_date'
DEFINE ptab_tr = 'p_tab_trans_&start_date'
---- - Debug

-- Spool File
DEFINE spool_file = /home/sql/trans_&start_date..txt

SET PAGESIZE 0
SET LINESIZE 100
SET SPACE 1

CLEAR COLUMNS

SPOOL &spool_file

select
to_char(tr.ID)||'|'||to_char(tr.CREATED,'YYYYMMDDHH24MISS')||'|'||'62'||substr(tr.INITIATOR,2)||'|'||
(select (case when substr(td.value,1,1) = 0 then concat(62,substr(td.value,2)) else 62 ||td.value end) from vtudadm.trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'target_msisdn')||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'amount')||'|'||
to_char(vtudadm.get_tld(tr.INITIATOR))||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'origcmd')||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'lacid')||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'cellid') LINEREC
from trans partition (&ptab_tr) tr
where tr.created between to_timestamp('&start_date'||'000000.000','YYYYMMDDHH24MISS.FF3')
and to_timestamp('&start_date'||'235959.999','YYYYMMDDHH24MISS.FF3')
and tr.type_ = 'LOAD'
and tr.state = 2
and tr.result = '0';


SPOOL OFF

CLEAR COLUMNS
CLEAR BREAKS
SET TERMOUT ON
SET VERIFY ON
SET FEEDBACK ON