Thanks for the question, Sachin.

Asked: April 25, 2017 - 9:49 am UTC

Last updated: April 27, 2017 - 9:27 am UTC

Version: Oracle 11gR2

Viewed 1000+ times

You Asked

Hello TOM,

we are generating report with SQL Query and output save to text file. I want to know if we can avoid multiple execution of SQL queries by using any other better way to achieve this and also not want to impact performance.

we execute SQL query for three times as below:
1. to get max of Event time
2. to get the actual row data
3. to get the count of rows selected.

clear break
clear comp
clear col
 set pagesize 0
 set linesize 32767
 set trimspool on
 set tab off
 set echo off
 set feedback off
 set recsep off
 set WRAP off
 SET HEADING ON
 SET TRIMS   ON
 SET ARRAYSIZE   200
 SET TERM OFF
 SET TERMOUT OFF
 set serveroutput on
 column tm new_value file_time noprint
 select to_char(sysdate, 'YYYYMMDD') tm from dual ;
 prompt &file_time
spool M_AAI_HISTORY.&file_time..txt 

SELECT  UNISTR('HDR\00B6')    || UNISTR('MOBBATCH\00B6') 
  || UNISTR('IDP\00B6') || UNISTR('AAI_HISTORY\00B6') 
  || TO_CHAR(MIN(a.EVENT_TIMESTAMP),'YYYYMMDD')
        || UNISTR('\00B6') || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
FROM (
select event_timestamp from emp
) a;

SELECT a.RecordType || UNISTR('\00B6') || a.acn || UNISTR('\00B6') || a.ID || UNISTR('\00B6') 
  || a.ev || UNISTR('\00B6') || a.EVENT_TIMESTAMP
        || UNISTR('\00B6') || EVENT_TYPE  || AMD 
FROM ( select * from emp ) a;

SELECT  UNISTR('TRL\00B6')    || COUNT(*)
FROM (select event_timestamp from emp
) a;

Sample out put looks as below

HDR¶MOBBATCH¶IDP¶AAI_HISTORY¶20120602¶20170420053434
BDY¶5007660000089642¶24-JUL-13 03.39.42.965000 AM¶created¶createOrUpdateAlertRegistrations¶PUSH
BDY¶5007660000089642¶24-JUL-13 03.39.42.965000 AM¶created¶createOrUpdateAlertRegistrations¶PUSH
TRL¶274078

and Chris said...

Well, you can merge many statements into one using union all. But I'm guessing that's not really what you're looking for ;)

You can get all the information accessing the table just once. Use analytics to find min/max/count/etc. Concatenate all the table columns together at the same time:

select to_char(min(hiredate) over (), 'yyyymmdd') mn,
       to_char(count(*) over ()) ct,
       ename || '|' || job || '|' || mgr || '|' || 'etc.' vls
from   scott.emp e;

MN        CT  VLS                        
19801217  14  SMITH|CLERK|7902|etc.      
19801217  14  ALLEN|SALESMAN|7698|etc.   
19801217  14  WARD|SALESMAN|7698|etc.    
19801217  14  JONES|MANAGER|7839|etc.    
19801217  14  MARTIN|SALESMAN|7698|etc.
... etc.

Then unpivot these columns to turn them into rows. Use distinct to squash out the duplicates for the header and footer (min and count).

To ensure the correct order, map the header values to 0, table values to 1 and footer to 2 using case or similar. Then stick this expression in your order by:

with rws as (
select to_char(min(hiredate) over (), 'yyyymmdd') mn,
       to_char(count(*) over ()) ct,
       ename || '|' || job || '|' || mgr || '|' || 'etc.' vls
from   scott.emp e
), vals as (
  select *
  from   rws 
  unpivot ( val for col in (mn, ct, vls))
)
  select distinct col, val from vals
  order  by case
    when col = 'MN' then 0 
    when col = 'VLS' then 1 
    else 2 
  end;

COL  VAL                        
MN   19801217                   
VLS  ALLEN|SALESMAN|7698|etc.   
VLS  JAMES|CLERK|7698|etc.      
VLS  FORD|ANALYST|7566|etc.     
VLS  WARD|SALESMAN|7698|etc.    
VLS  TURNER|SALESMAN|7698|etc.  
VLS  CLARK|MANAGER|7839|etc.    
VLS  SCOTT|ANALYST|7566|etc.    
VLS  MILLER|CLERK|7782|etc.     
VLS  MARTIN|SALESMAN|7698|etc.  
VLS  BLAKE|MANAGER|7839|etc.    
VLS  ADAMS|CLERK|7788|etc.      
VLS  JONES|MANAGER|7839|etc.    
VLS  KING|PRESIDENT||etc.       
VLS  SMITH|CLERK|7902|etc.      
CT   14

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the update

Sachin, April 25, 2017 - 1:15 pm UTC

yes solution is useful, I will check the performance with both approch, as we are fectching 8k rows with some complex sql. I will put my findings

other options

Rajeshwaran, April 25, 2017 - 1:38 pm UTC

demo@ORA12C> select to_char( min(hiredate) over() ,'yyyymmdd') min_dt,
  2         count(*) over() cnt,
  3         ename||','||job||','||mgr||','||'etc.' vls
  4  from emp ;

MIN_DT          CNT VLS
-------- ---------- -------------------------
19801217         14 SMITH,CLERK,7902,etc.
19801217         14 ALLEN,SALESMAN,7698,etc.
19801217         14 WARD,SALESMAN,7698,etc.
19801217         14 JONES,MANAGER,7839,etc.
19801217         14 MARTIN,SALESMAN,7698,etc.
19801217         14 BLAKE,MANAGER,7839,etc.
19801217         14 CLARK,MANAGER,7839,etc.
19801217         14 SCOTT,ANALYST,7566,etc.
19801217         14 KING,PRESIDENT,,etc.
19801217         14 TURNER,SALESMAN,7698,etc.
19801217         14 ADAMS,CLERK,7788,etc.
19801217         14 JAMES,CLERK,7698,etc.
19801217         14 FORD,ANALYST,7566,etc.
19801217         14 MILLER,CLERK,7782,etc.

14 rows selected.

demo@ORA12C> select to_char(null) min_dt, to_number(null) as cnt,
  2         'MN' as vls , 1 x
  3  from dual union all
  4  select to_char( min(hiredate) over() ,'yyyymmdd') min_dt,
  5         count(*) over() cnt,
  6         ename||','||job||','||mgr||','||'etc.' vls ,2 x
  7  from emp union all
  8  select to_char(null) min_dt, to_number(null) as cnt,
  9         'CT' as vls , 3 x
 10  from dual ;

MIN_DT          CNT VLS                                X
-------- ---------- ------------------------- ----------
                    MN                                 1
19801217         14 SMITH,CLERK,7902,etc.              2
19801217         14 ALLEN,SALESMAN,7698,etc.           2
19801217         14 WARD,SALESMAN,7698,etc.            2
19801217         14 JONES,MANAGER,7839,etc.            2
19801217         14 MARTIN,SALESMAN,7698,etc.          2
19801217         14 BLAKE,MANAGER,7839,etc.            2
19801217         14 CLARK,MANAGER,7839,etc.            2
19801217         14 SCOTT,ANALYST,7566,etc.            2
19801217         14 KING,PRESIDENT,,etc.               2
19801217         14 TURNER,SALESMAN,7698,etc.          2
19801217         14 ADAMS,CLERK,7788,etc.              2
19801217         14 JAMES,CLERK,7698,etc.              2
19801217         14 FORD,ANALYST,7566,etc.             2
19801217         14 MILLER,CLERK,7782,etc.             2
                    CT                                 3

16 rows selected.

demo@ORA12C> select decode(x,1,vls,2,'VLS',3,vls) col,
  2         decode(x,1, min(min_dt) over( order by x rows between
  3                        unbounded preceding and unbounded following ),
  4                  2,vls,
  5                  3, min(cnt) over( order by x rows between
  6                        unbounded preceding and unbounded following) ) val
  7  from (
  8  select to_char(null) min_dt, to_number(null) as cnt,
  9         'MN' as vls , 1 x
 10  from dual union all
 11  select to_char( min(hiredate) over() ,'yyyymmdd') min_dt,
 12         count(*) over() cnt,
 13         ename||','||job||','||mgr||','||'etc.' vls ,2 x
 14  from emp union all
 15  select to_char(null) min_dt, to_number(null) as cnt,
 16         'CT' as vls , 3 x
 17  from dual ) ;

COL        VAL
---------- ------------------------------
MN         19801217
VLS        SMITH,CLERK,7902,etc.
VLS        ALLEN,SALESMAN,7698,etc.
VLS        WARD,SALESMAN,7698,etc.
VLS        JONES,MANAGER,7839,etc.
VLS        MARTIN,SALESMAN,7698,etc.
VLS        BLAKE,MANAGER,7839,etc.
VLS        CLARK,MANAGER,7839,etc.
VLS        SCOTT,ANALYST,7566,etc.
VLS        KING,PRESIDENT,,etc.
VLS        TURNER,SALESMAN,7698,etc.
VLS        ADAMS,CLERK,7788,etc.
VLS        JAMES,CLERK,7698,etc.
VLS        FORD,ANALYST,7566,etc.
VLS        MILLER,CLERK,7782,etc.
CT         14

16 rows selected.

demo@ORA12C>

thanks for your help

Sachin, April 26, 2017 - 6:35 am UTC

Both option looks good and work great, but I am not able to implement then as we are using delimiter as UNISTR('\00B6')

If I try to implement both solution I am getting below error

<b>Option 1 error</b>
vals as (select * from rws unpivot(val for col in (mn, ct, vls)))
                                                           *
ERROR at line 169:
ORA-12704: character set mismatch

<b>Option 2 error</b>

        || UNISTR('\00B6') || ALERT_STATUS || UNISTR('\00B6') || ALERT_CORRELATION_ID AS vls ,2 x
                                                               *
ERROR at line 12:
ORA-12704: character set mismatch

April 27, 2017 - 9:27 am UTC

You just need to place calls to unistr for all the columns in the main select. Like you have in your original queries:

with rws as (
select to_char(min(hiredate) over (), 'yyyymmdd') || UNISTR('\00B6') mn,
       to_char(count(*) over ()) || UNISTR('\00B6') ct,
       ename || UNISTR('\00B6') || job || UNISTR('\00B6') || mgr || UNISTR('\00B6') || 'etc.' vls
from   scott.emp e
), vals as (
  select *
  from   rws 
  unpivot ( val for col in (mn, ct, vls))
)
  select distinct col, val from vals
  order  by case
    when col = 'MN' then 0 
    when col = 'VLS' then 1 
    else 2 
  end;

COL VAL
--- ----------------------------------
MN  19801217╢
VLS SCOTT╢ANALYST╢7566╢etc.
VLS TURNER╢SALESMAN╢7698╢etc.
VLS FORD╢ANALYST╢7566╢etc.
VLS KING╢PRESIDENT╢╢etc.
VLS SMITH╢CLERK╢7902╢etc.
VLS ALLEN╢SALESMAN╢7698╢etc.
VLS MARTIN╢SALESMAN╢7698╢etc.
VLS ADAMS╢CLERK╢7788╢etc.
VLS WARD╢SALESMAN╢7698╢etc.
VLS JONES╢MANAGER╢7839╢etc.
VLS BLAKE╢MANAGER╢7839╢etc.
VLS JAMES╢CLERK╢7698╢etc.
VLS MILLER╢CLERK╢7782╢etc.
VLS CLARK╢MANAGER╢7839╢etc.
CT  14╢

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks for the update

other options

thanks for your help

More to Explore

Analytics

Reporting with SQL, How we can merge multiple execution of SQL

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks for the update

other options

thanks for your help

More to Explore

Analytics