Home » SQL & PL/SQL » SQL & PL/SQL » How to insert table alias in query (Oracle 11g)
How to insert table alias in query [message #669012] |
Wed, 28 March 2018 23:56 |
|
ssyr
Messages: 65 Registered: January 2017
|
Member |
|
|
Hi All,
I have a one table which store list of all the delete queries .But now I have to insert table alis at run time how can I do this?
Please help me on this.
Thanks in advance.
select * from pre_qry;
DELETE FROM ABC WHERE TRACK_DATETIME < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
DELETE FROM XYZ WHERE TRACK_DATETIME < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
DELETE FROM XTR WHERE SRC_DTTM < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
DELETE FROM GHR WHERE TRANSACTION_DTTM < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
O/p I want below
SELECT rowid,t.* ABC t WHERE TRACK_DATETIME < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1);
SELECT rowid,t.* XYZ t WHERE TRACK_DATETIME < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1);
SELECT rowid,t.* XTR t WHERE SRC_DTTM < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1);
SELECT rowid,t.* GHR t WHERE TRANSACTION_DTTM < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1);
[EDITED by LF: fixed [code] tags]
[Updated on: Thu, 29 March 2018 00:54] by Moderator Report message to a moderator
|
|
|
Re: How to insert table alias in query [message #669013 is a reply to message #669012] |
Thu, 29 March 2018 00:34 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> SELECT * FROM pre_query
2 /
DELETES
----------------------------------------------------------------------------------------
DELETE FROM ABC WHERE TRACK_DATETIME < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
DELETE FROM XYZ WHERE TRACK_DATETIME < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
DELETE FROM XTR WHERE SRC_DTTM < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
DELETE FROM GHR WHERE TRANSACTION_DTTM < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
4 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT 'SELECT rowid,t.* FROM '
2 || REGEXP_SUBSTR (deletes, '[^ ]+', 1, 3)
3 || ' t '
4 || SUBSTR (deletes, REGEXP_INSTR (deletes, '[^ ]+', 1, 4)) selects
5 FROM pre_query
6 /
SELECTS
----------------------------------------------------------------------------------------------------
SELECT rowid,t.* FROM ABC t WHERE TRACK_DATETIME < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
SELECT rowid,t.* FROM XYZ t WHERE TRACK_DATETIME < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
SELECT rowid,t.* FROM XTR t WHERE SRC_DTTM < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
SELECT rowid,t.* FROM GHR t WHERE TRANSACTION_DTTM < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
4 rows selected.
|
|
|
Re: How to insert table alias in query [message #669015 is a reply to message #669012] |
Thu, 29 March 2018 01:53 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> select regexp_replace(deletes,'^.*?(FROM.*?)( WHERE.*$)','SELECT rowid,t.* \1 t\2') res
2 from t
3 /
RES
------------------------------------------------------------------------------------------------------
SELECT rowid,t.* FROM ABC t WHERE TRACK_DATETIME < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
SELECT rowid,t.* FROM XYZ t WHERE TRACK_DATETIME < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
SELECT rowid,t.* FROM XTR t WHERE SRC_DTTM < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
SELECT rowid,t.* FROM GHR t WHERE TRANSACTION_DTTM < ADD_MONTHS(TRUNC(SYSDATE,'MONTH'), (-10*12)+1)
4 rows selected.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:46:00 CDT 2024
|