Shell Programming and Scripting

Hi ss,

I thought all along that the source file would only contain the pipe-delimited rows (we did have a lot of misunderstanding). So this means that the source file also include the query as well.

Anyway, to remove the undesired lines try this:

Code:

sed '/^SQL/d;/^[0-9]/d;s/$$//g;/^ *$/d' $OUTFILE > $OUTFILE.tmp
mv $OUTFILE.tmp $OUTFILE

Will remove lines beginning with SQL and numbered lines, $ character, and blank lines

or

Code:

sed '/^D/!d;s/$$//g;/^ *$/d' $OUTFILE > $OUTFILE.tmp
mv $OUTFILE.tmp $OUTFILE

Will remove lines NOT beginning with D, $ character and blank lines

instead of:

Code:

sed -e '/SQL/d' $OUTFILE > THS_PROF.txt
 
sed -e '/2,21/d' $OUTFILE > THS_PROF.txt
 
sed -e '/D/d' $OUTFILE > THS_PROF.txt

Hi,

sed '/^D/!d;s/$$//g;/^ *$/d' $OUTFILE > $OUTFILE.tmp
mv $OUTFILE.tmp $OUTFILE

It worked perfectly :-)

Thanx a lot for your help n continous efforts of troubleshooting the problem :-)

1 last thng is that when im running the script the o/p is also getting written on the screen, how that can be avoided?

Hi,

Thanx again.

The thing what i asked is sql thng jus need to put set serveroutput off
and worked fine.

Thanks :-)

Nw everythng is fine except that in output file getting duplicate rows whereas if i execute the qry alone no duplicate records.

i.e qry o/p is:
select'D'||'|'||ciam.external_id||'|'||ciem.external_id||'|'||p.element_id||'|'||
casewhen(p.element_id in(select pcm.member_id from package_component_members pcm, package_components pc
where member_id in(10867,10866,10866,10867,10868,10866,10866,90014,90021)
and pcm.member_type=1
and pcm.component_id in(10459,10458,10658,20105,10499,10647,10810,90014,90022)
and pc.component_id=pcm.component_id))then'1'
end"A"
from customer_id_acct_map ciam, customer_id_equip_map ciem, product p, cmf c, dual
where ciam.account_no=p.billing_account_no
and ciem.subscr_no=p.parent_subscr_no
and p.billing_account_no=c.account_no
and c.account_no=ciam.account_no
and c.bill_period='G03'
and ciem.external_id_type=1
and ciam.external_id_type=1
and ciem.external_id_type=ciam.external_id_type
and p.element_id in(select pcm.member_id from package_component_members pcm, package_components pc
where member_id in(10867,10866,10866,10867,10868,10866,10866,90014,90021)
and pcm.member_type=1
and pcm.component_id in(10459,10458,10658,20105,10499,10647,10810,90014,90022)
and pc.component_id=pcm.component_id)

D|44083641|0192300160|10866|1
D|44083641|0192300160|10867|1

and the generated file o/p is:

D|44083641|0192300160|10866|1
D|44083641|0192300160|10866|1
D|44083641|0192300160|10867|1
D|44083641|0192300160|10867|1

Hi ss,

I think our code is getting a little messy now so should try the updating the ff. segment of code (the part after the query) to this:

Code:

SRCFILE="THS_PROF.txt"
OUTFILE="THS_PROF_$1_"`date +%Y%m%d%H%M%S`".txt"
sed '/^D/!d;s/$$//g;/^ *$/d' $SRCFILE > $OUTFILE

The loop, tmp file and some of the code is no longer needed.