Shell Programming and Scripting

After using this sed 's spool /SYSTEM/custom/modules/testing/THS_PROF.txt--g'

no getting the correct files also :-)

If only 1 file gets created instead of creating 1 file each for an a/c no i think will solve the problem of so many unwanted files getting created.

So for that i wanted to know that, how can i modify below mentioned sed stmt to create just 1 file for all the account nos with the value passed as argument should be in the file name in place of account no like THS_PROF_G03_20090114155816.txt where G03 is the value passed as argument while executing the script:


SRCFILE="THS_PROF.txt"
sed '1d' $SRCFILE | while read X; do
OUTFILE="THS_PROF_"`echo "$X" | awk -F"\t" '{ print $2 }'`"_"`date +%Y%m%d%H%M%S`".txt"
echo "$X" > $OUTFILE

Hmm...I'm still trying to parse what you just said...

Ok so you'd like just one output and the output will contain what exactly?

If the qry is returning 2 rows then the outptut file will contain those 2 rows.

For ex: If my file name is abc.sh
and while executing this abc.sh im passing an argument i.e.
sh abc.sh G03

then the output file name shld be like
THS_PROF_G03_20090114171434.txt

and it should contain the 2 rows:
D|44083669|0192652286|10866|1
D|44083455|0192300318|10867|1

Ok! Now I get it...

OUTFILE="THS_PROF_$1_`date +%Y%m%d%H%M%S`".txt"
SRCFILE="THS_PROF.txt"
sed '1d' $SRCFILE | while read X; do
echo "$X" >> $OUTFILE
done

Didn't test it yet. But go try.

ya it worked, jus double quotes were missing after $1_

OUTFILE="THS_PROF_$1_"`date +%Y%m%d%H%M%S`".txt"
SRCFILE="THS_PROF.txt"
sed '1d' $SRCFILE | while read X; do
echo "$X" >> $OUTFILE
done

But in the output file getting some unwanted values also along with the desired values.

Tried using sed to remove those lines but failed :-(

copiyng the code so if you can find out:

#!/bin/sh
sqlplus uname/pwd@dbname <<EOF

set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
set heading off

spool /SYSTEM/custom/modules/testing/THS_PROF.txt

select 'D'||'|'||ciam.external_id||'|'||ciem.external_id||'|'||p.element_id||'|'||
case when (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='$1'
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);
spool off;
exit

EOF

OUTFILE="THS_PROF_$1_"`date +%Y%m%d%H%M%S`".txt"
SRCFILE="THS_PROF.txt"
sed '1d' $SRCFILE | while read X; do
echo "$X" >> $OUTFILE
done

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 ss,

Could you post the output so that we can see what lines need to be removed.

the output of THS_PROF_G03_20090114192352.txt is:

SQL> $
SQL>
SQL> select 'D'||'|'||ciam.external_id||'|'||ciem.external_id||'|'||p.e
lement_id||'|'||$
SQL> select 'D'||'|'||ciam.external_id||'|'||ciem.external_id||'|'||p.element_id||'|'||
2 case when (p.element_id in (select pcm.member_id from package_comp
onent_members pcm, package_components pc$
2 case when (p.element_id in (select pcm.member_id from package_component_members pcm, package_components pc
3 where member_id in (10867,10866,10866,10867,10868,10866,10866,9001
4,90021)$
3 where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
4 and pcm.member_type=1$
4 and pcm.member_type=1
5 and pcm.component_id in (10459,10458,10658,20105,10499,10647,10810
,90014,90022)$
5 and pcm.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
6 and pc.component_id=pcm.component_id)) then '1'$
6 and pc.component_id=pcm.component_id)) then '1'
7 end "A"$
7 end "A"
8 from customer_id_acct_map ciam, customer_id_equip_map ciem, produc
t p, cmf c, dual$
8 from customer_id_acct_map ciam, customer_id_equip_map ciem, product p, cmf c, dual
9 where ciam.account_no=p.billing_account_no$
9 where ciam.account_no=p.billing_account_no
10 and ciem.subscr_no=p.parent_subscr_no$
10 and ciem.subscr_no=p.parent_subscr_no
11 and p.billing_account_no=c.account_no$
11 and p.billing_account_no=c.account_no
12 and c.account_no=ciam.account_no$
12 and c.account_no=ciam.account_no
13 and c.bill_period='G03'$
13 and c.bill_period='G03'
14 and ciem.external_id_type=1$
14 and ciem.external_id_type=1
15 and ciam.external_id_type=1$
15 and ciam.external_id_type=1
16 and ciem.external_id_type=ciam.external_id_type$
16 and ciem.external_id_type=ciam.external_id_type
17 and p.element_id in (select pcm.member_id from package_component_m
embers pcm, package_components pc$
17 and p.element_id in (select pcm.member_id from package_component_members pcm, package_components pc
18 where member_id in (10867,10866,10866,10867,10868,10866,10866,9001
4,90021)$
18 where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
19 and pcm.member_type=1$
19 and pcm.member_type=1
20 and pcm.component_id in (10459,10458,10658,20105,10499,10647,10810
,90014,90022)$
20 and pcm.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
21 and pc.component_id=pcm.component_id);$
21 and pc.component_id=pcm.component_id);
D|44083641|0192300160|10866|1$
D|44083641|0192300160|10866|1
D|44083641|0192300160|10867|1$
D|44083641|0192300160|10867|1
SQL> spool off;$
SQL> spool off;


out of which i jus want:

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

but without $.

i tried sed but prob couldnt be resolved.