Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query assistance (12.2)
SQL Query assistance [message #673466] |
Tue, 20 November 2018 19:55 |
|
jkingqst
Messages: 23 Registered: August 2017
|
Junior Member |
|
|
Hi All
Use Oracle for our DB backend and I'm trying to use SQL to generate a basic report. The we have a multi company db, meaning that each table we have is appended with a 3 digit company number; for example; a table name would be ttcemm030 however for company 100 it would be ttcemm030100 for company 200 it would be ttcemm030200 and etc.
The report I'm trying to generate needs to be in a CSV format with | as the separator and it needs to search each company table of cemm030xxx and bring back 2 columns from within that table, however within the CSV I would also need to put another column called "Company" that would be the last 3 digits of the table name.
I can manage to use a select query to bring back the table name and by using the regexp_substr put into a "Company" column the last 3 digits, but Im struggling with how to use that data for another select and then combine the output to my csv file.
Any help would be much appreciated.
Thanks Jason
|
|
|
|
|
|
|
Re: SQL Query assistance [message #673471 is a reply to message #673470] |
Wed, 21 November 2018 00:54 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
If I understand, you want one single csv file for each "table type".
Using your example, for "table type" "ttcemm030" with, say, tables "ttcemm030100", "ttcemm030200", "ttcemm030250"..., assuming they have the same columns "col1", "col2", you want a single "ttcemm030.csv" with columns "col1", "col2", "company" with "company" as "100", "200", "250" depending on the original table.
Is this correct?
|
|
|
|
Re: SQL Query assistance [message #673473 is a reply to message #673472] |
Wed, 21 November 2018 01:22 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Here's a way with the following script:
def table_type=&1
set echo off feedback off heading off termout off
spool .\dump_tmp.sql
select 'select '||table_name||'.*, '''||substr(table_name,length('&table_type')+1)||''' from '||table_name||';'
from user_tables
where table_name like upper('&table_type')||'%'
order by 1
/
set colsep '|'
spool .\&table_type..csv
@.\dump_tmp
spool off
Here's an example.
Settings:
create table ttcemm030100 (col1 int, col2 varchar2(20));
create table ttcemm030200 (col1 int, col2 varchar2(20));
create table ttcemm030250 (col1 int, col2 varchar2(20));
insert into ttcemm030100 values (1, 'This is from 100');
insert into ttcemm030100 values (2, 'This is from 100');
insert into ttcemm030100 values (3, 'This is from 100');
insert into ttcemm030200 values (1, 'This is from 200');
insert into ttcemm030200 values (2, 'This is from 200');
insert into ttcemm030250 values (1, 'This is from 250');
insert into ttcemm030250 values (2, 'This is from 250');
insert into ttcemm030250 values (3, 'This is from 250');
insert into ttcemm030250 values (4, 'This is from 250');
commit;
select * from ttcemm030100 ;
select * from ttcemm030200 ;
select * from ttcemm030250 ;
SQL> select * from ttcemm030100 ;
COL1 COL2
---------- --------------------
1 This is from 100
2 This is from 100
3 This is from 100
3 rows selected.
SQL> select * from ttcemm030200 ;
COL1 COL2
---------- --------------------
1 This is from 200
2 This is from 200
2 rows selected.
SQL> select * from ttcemm030250 ;
COL1 COL2
---------- --------------------
1 This is from 250
2 This is from 250
3 This is from 250
4 This is from 250
4 rows selected.
Execution:
SQL> @.\dump_table ttcemm030
SQL> -- Result
SQL> host type .\ttcemm030.csv
1|This is from 100 |100
2|This is from 100 |100
3|This is from 100 |100
1|This is from 200 |200
2|This is from 200 |200
1|This is from 250 |250
2|This is from 250 |250
3|This is from 250 |250
4|This is from 250 |250
SQL>
The script generates a temporary intermediate script:
SQL> host type .\dump_tmp.sql
select TTCEMM030100.*, '100' from TTCEMM030100;
select TTCEMM030200.*, '200' from TTCEMM030200;
select TTCEMM030250.*, '250' from TTCEMM030250;
|
|
|
Re: SQL Query assistance [message #673474 is a reply to message #673466] |
Wed, 21 November 2018 01:36 |
John Watson
Messages: 8937 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
How about normalizing your tables into a view, and then run your query against that? FOr example,
create view ttcemm030 as
select 100 src,ttcemm030100.* from ttcemm030100
union all
select 200 src,ttcemm030200.* from ttcemm030200
union all
select 300 src,ttcemm030300.* from ttcemm030300
--and so on...
/
|
|
|
|
Re: SQL Query assistance [message #673476 is a reply to message #673473] |
Wed, 21 November 2018 05:29 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
We can use DBMS_XMLGEN instead of generating intermediary dump_tmp.sql. Also, OP is on 12.2 where SQL*Plus supports CSV markup. Script dump_table.sql:
accept table_type prompt "Please enter table type: "
set termout off
set head off
set echo off
set verify off
set feed off
set markup csv on delimiter | quote off
spool &table_type..csv
with t as (
select dbms_xmlgen.getxml('select t.*,''' || substr(table_name,length('&&table_type') + 1 ) || ''' col3 from ' || table_name || ' t') xmldoc
from user_tables
where table_name like upper('&&table_type') || '%'
)
select x.*
from t,
xmltable(
'/ROWSET/ROW'
passing xmltype(xmldoc)
columns
col1 varchar2(4000) path 'COL1',
col2 varchar2(4000) path 'COL2',
col3 varchar2(4000) path 'COL3'
) x
/
spool off
set head on
set verify on
set feed on
set markup csv off
set termout on
Execution:
SQL> @dump_table.sql
Please enter table type: ttcemm030
SQL>
Output file ttcemm030.csv:
1|This is from 100|100
2|This is from 100|100
3|This is from 100|100
1|This is from 200|200
2|This is from 200|200
1|This is from 250|250
2|This is from 250|250
3|This is from 250|250
4|This is from 250|250
SY.
|
|
|
|
|
|
|
Re: SQL Query assistance [message #673481 is a reply to message #673480] |
Wed, 21 November 2018 05:57 |
gazzag
Messages: 1118 Registered: November 2010 Location: Bedwas, UK
|
Senior Member |
|
|
And this is the content of your dump_tmp.sql:
SQL> select 'select '||table_name||'.*, '''||substr(table_name,length('&table_type')+1)||''' from '||table_name||';'
2 from user_tables
3 where table_name like upper('&table_type')||'%'
4 order by 1
5 /
old 1: select 'select '||table_name||'.*, '''||substr(table_name,length('&table_type')+1)||''' from '||table_name||';'
new 1: select 'select '||table_name||'.*, '''||substr(table_name,length('set')+1)||''' from '||table_name||';'
old 3: where table_name like upper('&table_type')||'%'
new 3: where table_name like upper('set')||'%'
no rows selected
SQL> set colsep '|'
SQL> spool .\&table_type..csv
|
|
|
|
Re: SQL Query assistance [message #673483 is a reply to message #673481] |
Wed, 21 November 2018 06:02 |
|
jkingqst
Messages: 23 Registered: August 2017
|
Junior Member |
|
|
gazzag wrote on Wed, 21 November 2018 05:57And this is the content of your dump_tmp.sql:
SQL> select 'select '||table_name||'.*, '''||substr(table_name,length('&table_type')+1)||''' from '||table_name||';'
2 from user_tables
3 where table_name like upper('&table_type')||'%'
4 order by 1
5 /
old 1: select 'select '||table_name||'.*, '''||substr(table_name,length('&table_type')+1)||''' from '||table_name||';'
new 1: select 'select '||table_name||'.*, '''||substr(table_name,length('set')+1)||''' from '||table_name||';'
old 3: where table_name like upper('&table_type')||'%'
new 3: where table_name like upper('set')||'%'
no rows selected
SQL> set colsep '|'
SQL> spool .\&table_type..csv
This is my dump_tmp.sql file
old:select 'select '||table_name||'.*, '''||substr(table_name,length('&table_type')+1)||''' from '||table_name||';'
from user_tables
where table_name like upper('&table_type')||'%'
order by 1
new:select 'select '||table_name||'.*, '''||substr(table_name,length('ttcemm030')+1)||''' from '||table_name||';'
from user_tables
where table_name like upper('ttcemm030')||'%'
order by 1
select TTCEMM030050.*, '050' from TTCEMM030050;
select TTCEMM030070.*, '070' from TTCEMM030070;
select TTCEMM030090.*, '090' from TTCEMM030090;
select TTCEMM030100.*, '100' from TTCEMM030100;
select TTCEMM030104.*, '104' from TTCEMM030104;
select TTCEMM030110.*, '110' from TTCEMM030110;
select TTCEMM030111.*, '111' from TTCEMM030111;
select TTCEMM030114.*, '114' from TTCEMM030114;
select TTCEMM030120.*, '120' from TTCEMM030120;
select TTCEMM030121.*, '121' from TTCEMM030121;
select TTCEMM030200.*, '200' from TTCEMM030200;
select TTCEMM030250.*, '250' from TTCEMM030250;
select TTCEMM030300.*, '300' from TTCEMM030300;
select TTCEMM030301.*, '301' from TTCEMM030301;
select TTCEMM030400.*, '400' from TTCEMM030400;
select TTCEMM030500.*, '500' from TTCEMM030500;
select TTCEMM030559.*, '559' from TTCEMM030559;
select TTCEMM030600.*, '600' from TTCEMM030600;
select TTCEMM030618.*, '618' from TTCEMM030618;
select TTCEMM030619.*, '619' from TTCEMM030619;
select TTCEMM030650.*, '650' from TTCEMM030650;
select TTCEMM030900.*, '900' from TTCEMM030900;
select TTCEMM030910.*, '910' from TTCEMM030910;
|
|
|
|
|
Re: SQL Query assistance [message #673492 is a reply to message #673487] |
Wed, 21 November 2018 07:13 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
jkingqst wrote on Wed, 21 November 2018 07:16
It looks like this is calling the main table but how is referencing the col1, col2 and col3 in the lower select?
The actual column name from the table is dsca and enterprise_unit.
And how are we supposed to know that? Anyway, all you need to do is change:
col1 varchar2(4000) path 'COL1',
col2 varchar2(4000) path 'COL2',
to:
col1 varchar2(4000) path 'DSCA',
col2 varchar2(4000) path 'ENTERPRISE_UNIT',
SY.
|
|
|
|
|
|
Re: SQL Query assistance [message #673497 is a reply to message #673480] |
Wed, 21 November 2018 07:51 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 21 November 2018 06:52
Also have a look at Solomon's solution which bypasses all the SQL*Plus stuff and takes advantage of the new SQL*Plus facilities (but it requires a fix number of columns).
Any column number solution,as long as all tables have same number of columns of same data type (your solution doesn't have that restriction):
accept table_type prompt "Please enter table type: "
set termout off
set head off
set echo off
set verify off
set feed off
variable v_cur refcursor
declare
v_stmt clob;
begin
with t as (
select row_number() over(order by table_name) rn,
case
when row_number() over(order by table_name) > 1 then ' union all '
end ||'select t.*,''' || substr(table_name,length('&&table_type') + 1 ) || ''' col3 from ' || table_name || ' t' col
from user_tables
where table_name like upper('&&table_type') || '%'
)
select listagg(col) within group(order by rn)
into v_stmt
from t;
open :v_cur for v_stmt;
end;
/
set markup csv on delimiter | quote off
spool &table_type..csv
print v_cur
spool off
set head on
set verify on
set feed on
set markup csv off
set termout on
CSV:
1|This is from 100|100
2|This is from 100|100
3|This is from 100|100
1|This is from 200|200
2|This is from 200|200
1|This is from 250|250
2|This is from 250|250
3|This is from 250|250
4|This is from 250|250
SY.
|
|
|
Re: SQL Query assistance [message #673500 is a reply to message #673496] |
Wed, 21 November 2018 09:52 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
jkingqst wrote on Wed, 21 November 2018 14:45Michel Cadot wrote on Wed, 21 November 2018 07:33
And you can replace the "'||table_name||'.*" or "t.*" by "dsca, enterprise_unit" in both solutions.
Sorry Michel but not sure where you mean?
select 'select '||table_name||'.*, '''||substr(table_name,length('&table_type')+1)||''' from '||table_name||';'
select dbms_xmlgen.getxml('select t.*,''' || substr(table_name,length('&&table_type') + 1 ) || ''' col3 from '
|
|
|
|
|
|
|
Re: SQL Query assistance [message #673520 is a reply to message #673516] |
Thu, 22 November 2018 03:44 |
|
jkingqst
Messages: 23 Registered: August 2017
|
Junior Member |
|
|
SQL> desc ttcemm030100;
Name Null? Type
--------- -------- --------
T$EUNT NOT NULL CHAR(6)
T$DSCA NOT NULL CHAR(30)
T$LCMP NOT NULL NUMBER
T$FCMP NOT NULL NUMBER
T$EUCA NOT NULL CHAR(6)
T$CCAL NOT NULL CHAR(9)
T$DFPO NOT NULL CHAR(6)
T$DFCO NOT NULL CHAR(6)
T$RACT NOT NULL CHAR(6)
T$TXTN NOT NULL NUMBER
T$REFCNTD NOT NULL NUMBER
T$REFCNTU NOT NULL NUMBER
|
|
|
Goto Forum:
Current Time: Sun Jun 16 08:46:22 CDT 2024
|