proper ordering of o/p values


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting proper ordering of o/p values
# 1  
Old 01-19-2009
proper ordering of o/p values

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
# 2  
Old 01-19-2009
Hi ss,

Why not sort it in the query itself? That would be more faster and easier.
# 3  
Old 01-19-2009
Queries o/p is ordered by a/c no.
# 4  
Old 01-19-2009
Why not do a complex sort:
Code:
order by 1, A

Sort by 1 then by column A...

It would be far more difficult to sort it using the o/p.
# 5  
Old 01-19-2009
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?
# 6  
Old 01-20-2009
Ok then...well, le'me give it a try:

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

I still suggest though that sorting be done in the query before the output.
# 7  
Old 01-20-2009
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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Bash - re-ordering list of parameters

Hello. I have a script that writes parameters in alphabetic order. But I have a parameter which have 3 lines. There is no continuation character ( '\' ). Each of the three lines finish with 'cr'. But line 2 and 3 of the concerning parameter start with a tab char (but should be one or more... (7 Replies)
Discussion started by: jcdole
7 Replies

2. Shell Programming and Scripting

Ordering batch number

Hi, Could some one please help to order the batch number in sequence. I will be getting bunch of files with batch number in folder1 which are not in sequence. I need to move all files from folder1 to folder2 with batch number in sequence. Header record looks like PROCESS1... (8 Replies)
Discussion started by: zooby
8 Replies

3. Shell Programming and Scripting

Ordering Folders having Date as Names

Hi All, I have directories under /development/arun/weekly/ 20120421 20120414 . . . . I need to arrange these directories in descending order. folder name with recent date will be on top and then others. (1 Reply)
Discussion started by: Arun Mishra
1 Replies

4. Shell Programming and Scripting

Random ordering

1 2 4 5 3 I would like to use a script so that i can randomly rearrange these numbers such as 3 5 2 4 1 Thanks! (3 Replies)
Discussion started by: johnkim0806
3 Replies

5. Shell Programming and Scripting

ordering

file1 1 SNP2 3 1 SNP3 3 1 SNP5 4 2 SNP1 4 2 SNP4 4 file2 SNP1 1 1 1 SNP5 5 5 5 SNP4 4 4 4 SNP2 2 2 2 SNP3 1 1 1 desired output (1 Reply)
Discussion started by: johnkim0806
1 Replies

6. Shell Programming and Scripting

Re ordering lines - Awk

Is it possible to re-order certain rows as columns (of large files). Few lines from the file for reference. input Splicing Factor: Tra2beta, Motif: aaguguu, Cutoff: 0.5000 Sequence Position Genomic Coordinate K-mer Score 97 chr1:67052604 uacuguu 0.571 147... (3 Replies)
Discussion started by: quincyjones
3 Replies

7. Shell Programming and Scripting

Re-ordering data

input Predictions for job: 1299399580 ********************************************** gg18_qqq10_100017878_100017978_- ============================================================================== zzz Factor: XXX, ttt: crsmsgw, Cutoff: 0.6429 seqe Position fff Coordinate K-mer Score ... (3 Replies)
Discussion started by: quincyjones
3 Replies

8. Shell Programming and Scripting

ordering a data file

With an input file like this: How can I get an output like this? (In the quoted examples, the "_" sign represents an empty space) Note that there are some minus signs and no spaces, in the example above the first character of the first line is an empty space, so each number spans 10... (16 Replies)
Discussion started by: lego
16 Replies

9. Homework & Coursework Questions

word ordering problem HELP please (linux)

Hi guys I need you ,please help me i have to do this for tomorow and i don't understand how to do Q1 : Order the words of RADIO.txt by frequency Q2 : Order the words of RADIO.txt in alphabétique order Q3 : Order the words of RADIO.txt par ordre "rhymique" (exemple, put togeder words which are... (1 Reply)
Discussion started by: Lili
1 Replies

10. Shell Programming and Scripting

File ordering by portion of filename

Hi, Lets say I have a few xml files: 1234567894.xml abc_1234567895.xml abc_000_1234567890.xml abc_0000000_1234567893.xml 684_abc_000_1234567899.xml The naming convention of the files is: xxxxx_timestamp.xml OR timestamp.xml where x can be anything and I would like to order them by... (4 Replies)
Discussion started by: Leion
4 Replies
Login or Register to Ask a Question