Hi,

Below is my script which creates a file:


#!/bin/sh

if [ $# -ne 1 ]
then
echo "Enter bill period "
echo "Syntax: sh cpd.sh G08"
exit
fi

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
set SERVEROUTPUT off
set ECHO off

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


select distinct 'D'||'|'||ciam.external_id||'|'||ciem.external_id||'|'||p.element_id||'|'||
case when (p.element_id in (select /*+parallel(pc,8)*/ 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 cm,
dual,
CMF_COMPONENT_ELEMENT b,
CMF_PACKAGE_COMPONENT c,
PACKAGE_DEFINITION_VALUES d,
COMPONENT_DEFINITION_VALUES e,
CMF_PACKAGE f,
PRODUCT_ELEMENTS g,
DESCRIPTIONS h,
PRODUCT_CHARGE_MAP i
where ciam.account_no=p.billing_account_no
and ciem.subscr_no=p.parent_subscr_no
and p.billing_account_no=cm.account_no
and cm.account_no=ciam.account_no
and cm.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 /*+parallel(pc,8)*/ 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)
and p.component_id=b.component_id
AND p.tracking_id=b.association_id
AND p.tracking_id_serv=b.association_id_serv
AND c.component_inst_id=b.component_inst_id
AND d.package_id=c.package_id
AND e.component_id=p.component_id
AND c.package_inst_id=f.package_inst_id
AND c.package_inst_id_serv=f.package_inst_id_serv
AND g.element_id=p.element_id
AND g.description_code=h.description_code
and p.tracking_id=i.tracking_id
and p.tracking_id_serv=i.tracking_id_serv
and h.language_code=1
and b.ASSOCIATION_type=1
and b.association_id=p.tracking_id
and p.ELEMENT_ID in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
and c.COMPONENT_ID in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
and f.package_id in (18506,18505,18505,18510,18511,18513,19040,90004,90007)
and f.package_status=1
and c.COMPONENT_STATUS=1
and b.COMPONENT_ELEMENT_STATUS=1
and cm.account_no=c.parent_account_no
and cm.account_no=f.parent_account_no
and ciam.account_no in (select parent_account_no from service where parent_account_no in (select parent_account_no from service_view where view_status=2))
order by 1;


select distinct 'D'||'|'||ciam.external_id||'|'||ciem.external_id||'|'||0||'|'||
case
when (p.element_id in (select /*+parallel(pc,8)*/ pcm.member_id from package_component_members pcm, package_components pc
where pcm.member_id =10867
and pcm.member_type=1
and pc.component_id =10459
and pc.component_id=pcm.component_id
and pc.package_id=18506))
then '200'
when (p.element_id in (select /*+parallel(pc,8)*/ pcm.member_id from package_component_members pcm, package_components pc
where pcm.member_id in (10866,10866,10867,10868,10866,10866,90014,90021)
and pcm.member_type=1
and pc.component_id in (10458,10658,20105,10499,10647,10810,90014,90022)
and pc.component_id=pcm.component_id
and pc.package_id in (18505,18505,18510,18511,18513,19040,90004,90007)))
then '201'
end "A"
from
customer_id_acct_map ciam,
customer_id_equip_map ciem,
product p,
cmf cm,
dual,
CMF_COMPONENT_ELEMENT b,
CMF_PACKAGE_COMPONENT c,
PACKAGE_DEFINITION_VALUES d,
COMPONENT_DEFINITION_VALUES e,
CMF_PACKAGE f,
PRODUCT_ELEMENTS g,
DESCRIPTIONS h,
PRODUCT_CHARGE_MAP i
where ciam.account_no=p.billing_account_no
and ciem.subscr_no=p.parent_subscr_no
and p.billing_account_no=cm.account_no
and cm.account_no=ciam.account_no
and cm.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)
and p.component_id=b.component_id
AND p.tracking_id=b.association_id
AND p.tracking_id_serv=b.association_id_serv
AND c.component_inst_id=b.component_inst_id
AND d.package_id=c.package_id
AND e.component_id=p.component_id
AND c.package_inst_id=f.package_inst_id
AND c.package_inst_id_serv=f.package_inst_id_serv
AND g.element_id=p.element_id
AND g.description_code=h.description_code
and p.tracking_id=i.tracking_id
and p.tracking_id_serv=i.tracking_id_serv
and h.language_code=1
and b.ASSOCIATION_type=1
and b.association_id=p.tracking_id
and p.ELEMENT_ID in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
and c.COMPONENT_ID in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
and f.package_id in (18506,18505,18505,18510,18511,18513,19040,90004,90007)
and f.package_status=1
and c.COMPONENT_STATUS=1
and b.COMPONENT_ELEMENT_STATUS=1
and cm.account_no=c.parent_account_no
and cm.account_no=f.parent_account_no
and ciam.account_no in (select parent_account_no from service where parent_account_no in (select parent_account_no from service_view where view_status=2))
order by 1;
spool off;
exit

EOF

SRCFILE="THS_PROF.txt"
OUTFILE="THS_PROF_$1_"`date +%Y%m%d%H%M%S`".txt"

sed '/^D/!d;s/$$//g;/^ *$/d' $SRCFILE > $OUTFILE

rm THS_PROF.txt


The output file contains the reslut of the 2 queries like as shown below:

D|44081242|0198289472|10867|1
D|44081518|0198320689|10866|1
D|44083357|0192300152|10866|1
D|44083703|0192540130|10867|1
D|44084043|0192690150|10867|1
D|44084294|0131000|10866|1
D|44084542|0194114532|90014|1
D|44081242|0198289472|0|200
D|44081518|0198320689|0|201
D|44083357|0192300152|0|201
D|44083703|0192540130|0|200
D|44084043|0192690150|0|200
D|44084294|0131000|0|201
D|44084542|0194114532|0|201


Now i want this o/p to be in order for every a/c no i.e.
D|44081242|0198289472|10867|1
D|44081242|0198289472|0|200
D|44081518|0198320689|10866|1
D|44081518|0198320689|0|201
D|44083357|0192300152|10866|1
D|44083357|0192300152|0|201
D|44083703|0192540130|10867|1
D|44083703|0192540130|0|200
D|44084043|0192690150|10867|1
D|44084043|0192690150|0|200
D|44084294|0131000|10866|1
D|44084294|0131000|0|201
D|44084542|0194114532|90014|1
D|44084542|0194114532|0|201

The 2nd field is account no so i want that for evey account no 1st the row containing 1 should come and after that for the same a/c no the row containing 200 or 201 should come.

Plz help in this regard.

thanks