How to remove characters for sql query output?

Hi,

my sql query return below value for " $SQL_OPN_AMT "

CUSTCODE OH OHOPNAMT_GL OHREFNUM -------------------------------- -- ----------- ------------------------------ OHENTDATE FCC --------- --- 1.1072256 IN 74.85 000072061
0 01-MAY-13 MVR 1.1072256 IN 751.72 0000744751 01-JUN-13 MVR 1.1072256 IN 382.19 0000790408 01-AUG-13 MVR CUSTCODE OH OHOPNAMT_GL OHREFNUM -----------------------------
--- -- ----------- ------------------------------ OHENTDATE FCC --------- --- 1.1072256 IN 589.8 0000768242 01-JUL-13 MVR 1.1072256 IN 216 0000810616 01-SEP-13 MVR
CUSTCODE OH OHOPNAMT_GL OHREFNUM -------------------------------- -- ----------- ------------------------------ OHENTDATE FCC --------- --- 2.38.00.00.100010 IN 948.32
0000856580 01-NOV-13 MVR

I am using below code to display in tabular form.

echo $SQL_OPN_AMT | /usr/xpg4/bin/awk -F' ' '{for(i=13;i<=NF;i++){printf("%s%s",$i,i%6?" ":"\n")}}' >>  cust_amt_dtls.tmp

but my out put is showing as below.

1.1072256,DD,74.85,0000720610,01-MAY-13,MVR
1.1072256,DD,751.72,0000744751,01-JUN-13,MVR
1.1072256,DD,589.8,0000768242,01-JUL-13,MVR
1.1072256,DD,382.19,0000790408,01-AUG-13,MVR
1.1072256,DD,216,0000810616,01-SEP-13,MVR
2.38.00.00.100010,DD,948.32,0000856580,01-NOV-13,MVR
-----------,------------------------------,,DDC,---------,---
CUSTCODE,OH,,OHREFNUM,--------------------------------,--

I want to remove the extra lines/characters coming along with the tabular output.

i.e my output should be

1.1072256,DD,74.85,0000720610,01-MAY-13,MVR
1.1072256,DD,751.72,0000744751,01-JUN-13,MVR
1.1072256,DD,589.8,0000768242,01-JUL-13,MVR
1.1072256,DD,382.19,0000790408,01-AUG-13,MVR
1.1072256,DD,216,0000810616,01-SEP-13,MVR
2.38.00.00.100010,DD,948.32,0000856580,01-NOV-13,MVR

Thanks
Bhavish

grep "\." output

#should give you your output

that's some crappy output. perhaps you should paste the part of the code that creates your $SQL_OPN_AMT . I bet it can be reworked to make this easier rather than fixing the wrong problem. I couldn't reproduce the output with this input. It's slightly formatted wrong.

This will skip from "CUSTCODE" and the next 12 fields

mute@thedoctor:~/temp/ranabhavish$ awk '{for(i=13;i<=NF;i++){if($i=="CUSTCODE"){i+=11}else{printf("%s%s",$i,i%6?",":"\n")}}}' input
1.1072256,IN,74.85,0000720610,01-MAY-13,MVR
1.1072256,IN,751.72,0000744751,01-JUN-13,MVR
1.1072256,IN,382.19,0000790408,01-AUG-13,MVR
1.1072256,IN,589.8,0000768242,01-JUL-13,MVR
1.1072256,IN,216,0000810616,01-SEP-13,MVR
2.38.00.00.100010,IN,948.32,0000856580,01-NOV-13,MVR

I'd rather fix it the right way though.

Might I suggest that you alter your SQL call to something like this:-

sqlplus -S <<-EOSQL | read col1 col2 col2 col3 col4 col5 col6 
$user/$pass@$sid
set heading off
set pagesize 0

Your query here

EOSQL

This should get rid of much of the unwanted parts and (assuming that you have only one lines returned) will parse the output into values $col1, $col2, $col3, etc. for you to work with later in your script.

Using the format above hides the credentials from a ps command that anyone could be running. The two set commands turn off the titles and the page split respectively. The -S flag will prevent the output of the usual banner messages when starting SQL.

I hope that this helps. If I've missed the point, please post the section of code you are stuck with, including the SQL call.

Robin
Liverpool/Blackburn
UK

1 Like