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 Go to next message
ssyr
Messages: 64
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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 Go to previous message
Michel Cadot
Messages: 66808
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.
Previous Topic: ORA-21561: OID generation failed !!
Next Topic: How to get this table from that Database
Goto Forum:
  


Current Time: Wed Jan 29 02:01:55 CST 2020