manipulate & format the output of spool command

Shell Programming and Scripting
1

Hi All,

I am spooling the data some sql queries into a single file but wanted to know how to format the data of the file generated by spool.

#!/bin/sh
unset -f USAGE
USAGE () {
clear
echo "############################USAGE#######################\n"
echo "Incorrect number of parameters\n"
echo "Usage: ${1} <DBUser>"
echo "Usage: ${2} <DBPassword>"
echo "########################################################\n"
exit -1;
}
 
if [ $# -ne 2 ]; then
USAGE
fi
 
username=$1
password=$2
 
sqlplus -s $username/$password@DB_TGT01 << END
SET SERVEROUTPUT OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET ECHO OFF
set LINESIZE 5000
set TRIMSPOOL on
spool reco-counts-SRC_TGT.txt
 
select TABLE_NAME "Table_Name", ROWCOUNTS "Source_CU01" from DBS_Table_counts@DB_SRC order by TABLE_NAME;
 
select ROWCOUNTS "Target_CU01" from DBS_Table_counts order by TABLE_NAME;
 
select ROWCOUNTS "Target_CU04" from DBS_Table_counts@DB_TGT04 order by TABLE_NAME;
 
select ROWCOUNTS "Target_CU06" from DBS_Table_counts@_TGT06 order by TABLE_NAME;
 
select ROWCOUNTS "Target_CU07" from DBS_Table_counts@_TGT07 order by TABLE_NAME;
spool off;
exit;
END

Now the expected output what i want is

Col1   Col2   Col3   Col4   Col5   Col6   Col7   Col8
A       4       1       1       1       1       4       0
B       26      5       8       4      9       26      0
C       70      1      10      55     0       67      -3
 
where Col1 is Table_Name from 1st query
Col2 is Source_CU01 from 1st query
Col3 is Target_CU01 from 2nd query
Col4 is Target_CU04 from 3rd query
Col5 is Target_CU06 from 4th query
Col6 is Target_CU07 from 5th query
Col7 is values of (Col3+Col4+Col5+Col6)
Col8 is values of Col7 - Col2

Is this kind of formatting & manipulation is possible?

Thanks

2

this query will do....

select A.COL1,A,COL2,B.COL3,C.COL4,D.COL5,E.COL6,B.COL3+C.COL4+D.COL5+E.COL6 AS "COL7",(B.COL3+C.COL4+D.COL5+E.COL6)-A.COL2 AS "COL8" 
FROM
(select TABLE_NAME "COL1", ROWCOUNTS "COL2" from DBS_Table_counts@DB_SRC order by TABLE_NAME)  A,
(select ROWCOUNTS "COL3" from DBS_Table_counts order by TABLE_NAME) B,
(select ROWCOUNTS "COL4" from DBS_Table_counts@DB_TGT04 order by TABLE_NAME) C,
(select ROWCOUNTS "COL5" from DBS_Table_counts@_TGT06 order by TABLE_NAME) D,
(select ROWCOUNTS "COL6" from DBS_Table_counts@_TGT07 order by TABLE_NAME) E;
3

Thanks a lot, it worked :slight_smile: