proper ordering of o/p values

ss_ss · January 19, 2009, 9:57pm

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

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

angheloko · January 19, 2009, 10:26pm

Hi ss,

Why not sort it in the query itself? That would be more faster and easier.

ss_ss · January 19, 2009, 10:30pm

Queries o/p is ordered by a/c no.

angheloko · January 19, 2009, 10:44pm

Why not do a complex sort:

order by 1, A

Sort by 1 then by column A...

It would be far more difficult to sort it using the o/p.

ss_ss · January 19, 2009, 10:56pm

order by 1 represents that im ordering by column 1 and there is only 1 column i.e. A so order by 1, A is same as order by 1 or order by A.

Here need to order the output of 2 queries. is it possible to take the o/p of the queries into an array or something like that then comparing the 2 arrays and ordering the o/p?

angheloko · January 19, 2009, 11:10pm

Ok then...well, le'me give it a try:

sort -t"|" -k 2n -k 5n input_file

I still suggest though that sorting be done in the query before the output.

ss_ss · January 19, 2009, 11:24pm

your piece of code:

Code:
sort -t"|" -k 2n -k 5n input_file
worked fine but the problem is that, that the sorted values im getting as the server o/p not in the file.

is it because of the place where i've put the code????

copying script for ur reference:

#!/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

sort -t"|" -k 2n -k 5n $OUTFILE

rm THS_PROF.txt

See the sorted serverotput:
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

angheloko · January 20, 2009, 12:21am

Placing is ok...But you'll have to overwrite the file then:

sort -t"|" -k 2n -k 5n $OUTFILE > $OUTFILE.tmp; mv $OUTFILE.tmp $OUTFILE

ss_ss · January 20, 2009, 12:44am

ok and thanks a lot it worked fine

ss_ss · January 20, 2009, 12:56am

1 more thing how can i take the output of a qry containing multiple rows into an array and then comparing that array's values with the argument passed?????????

Im doing it this way but not getting the desired result:

bill_period=`sqlplus uname/pwd@dbname <<eof!
set verify off
set heading off
set feedback off
select bill_period from bill_period_ref order by 1;
exit
eof!
`
if [ $# -ne bill_period ]
then
echo "Enter valid bill period"
exit
fi

angheloko · January 20, 2009, 1:26am

Not tested:

bill_period=`sqlplus uname/pwd@dbname <<eof!
set verify off
set heading off
set feedback off
select bill_period from bill_period_ref order by 1;
exit
eof!
`

# Assuming that the 1st argument contains the bill period input
if [ `echo $bill_period | grep ^$1` = "" ]
then
    echo "Enter valid bill period"
    exit
fi

ss_ss · January 20, 2009, 1:47am

Not working and getting following error:

./cpd.sh[19]: test: Specify a parameter with this command.

The select query returns multiple rows and single column.

angheloko · January 20, 2009, 1:56am

Updated:

bill_period=`sqlplus uname/pwd@dbname <<eof!
set verify off
set heading off
set feedback off
select bill_period from bill_period_ref order by 1;
exit
eof!
`

# Assuming that the 1st argument contains the bill period input
if [ "`echo $bill_period | grep ^$1`" = "" ]
then
    echo "Enter valid bill period"
    exit
fi

Or, I think you can do with this too:

bill_period_input="'""$1""'"
bill_period=`sqlplus uname/pwd@dbname <<eof!
set verify off
set heading off
set feedback off
select bill_period from bill_period_ref where bill_period=$bill_period_input order by 1;
exit
eof!
`

# Assuming that the 1st argument contains the bill period input
if [ "$bill_period" = "" ]
then
    echo "Enter valid bill period"
    exit
fi

ss_ss · January 20, 2009, 2:03am

Thanx a lot again, nw it worked fine

ss_ss · January 20, 2009, 3:44am

In

Code:
bill_period_input="'""$1""'"bill_period=`sqlplus uname/pwd@dbname <<eof!set verify offset heading offset feedback offselect bill_period from bill_period_ref where bill_period=$bill_period_input order by 1;exiteof!`# Assuming that the 1st argument contains the bill period inputif [ "$bill_period" = "" ]then echo "Enter valid bill period" exitfi

how can the sql query be modified to accomodate a where clause i.e.

select bill_period from bill_period_ref where bill_period like 'G%' order by 1