Home » SQL & PL/SQL » SQL & PL/SQL » Sorting and Sorting in oracle (2 threads merged by bb)
icon8.gif  Sorting and Sorting in oracle (2 threads merged by bb) [message #389810] Tue, 03 March 2009 11:57 Go to next message
Messages: 2
Registered: March 2009
Junior Member
Hey guys i have a PL/SQL code in htp like this
create or replace

p_machine in varchar2,
p_sort_by varchar2 default '2'

p_job_id number;
total_count number;
v_job_name varchar2(30);
v_last_end varchar2(30);
v_next_start varchar2(30);
v_total_count1 number := 0;
v_count number;
v_status varchar2(15);
type cur_type is ref cursor;
v_cur cur_type;
v_cur1 cur_type;
v_cur_str varchar2(4000);
v_cur_str1 varchar2(4000);
v_cur_str2 varchar2(4000);
v_cur1_str varchar2(4000);
v_joid number;
v_machine varchar2(10);
v_description varchar2(256);
v_sort_by varchar2(10);
v_err_date varchar2(1);
v_dy_mo_from_err varchar2(1);
v_dy_mo_from_leap_err_1 varchar2(1);
v_dy_mo_from_leap_err_2 varchar2(1);
v_dy_mo_to_err varchar2(1);
v_dy_mo_to_leap_err_1 varchar2(1);
v_dy_mo_to_leap_err_2 varchar2(1);
v_err_date_from_to varchar2(1);
v_from_date varchar2(30);
v_from_date_2 varchar2(30);
v_from_date_1 date;
v_to_date varchar2(30);
v_to_date_2 varchar2(30);
v_cat varchar2(30);
v_to_date_1 date;
v_mo_from number;
v_mo_from_char varchar2(30);
v_dy_from number;
v_yr_from number;
v_hr_from number;
v_mi_from number;
v_mo_to number;
v_mo_to_char varchar2(30);
v_dy_to number;
v_yr_to number;
v_hr_to number;
v_mi_to number;
v_err_cnt number;
-- v_status varchar2(10);


v_machine := p_machine;
v_sort_by := p_sort_by;

v_cur_str := 'select a.joid joid, a.job_name job_name, a.description, '||
' decode(b.status, '||
' 1,''Running'', '||
' 4,''Success'', '||
' 5,''Terminated'', '||
' 7,''On ice'', '||
' 8,''Inactive'', '||
' 11,''On hold'', '||
' b.status) '||
'status, '||
'to_char(date_conv(b.next_start),''dd-Mon-yyyy hh24:mi'') starttime, '||
'decode(a.machine, '||
' ''m1606uspln2.cust.aops-eds.com'',''adminprod'', '||
' ''m1744sjc1.cust.loudcloud.com'',''tie'', '||
' a.machine) '||
'machine '||
'from JOB_MVW a, JOB_STATUS_MVW b, dual '||
' where '||
'decode(a.machine, '||
' ''m1606uspln2.cust.aops-eds.com'',''adminprod'', '||
' ''m1744sjc1.cust.loudcloud.com'',''tie'', '||
' a.machine) = '||

-- 'where a.machine = '||
' '''||v_machine||

' and b.joid = a.joid '||
-- ' and b.status = 8 '||
' and substr(a.job_name,3,1) in (''1'',''2'',''3'',''4'',''5'')' ||
' order by '||p_sort_by;

htp.print('<LINK REL=''STYLESHEET'' TYPE=''text/css'' HREF='''||
htp.bodyopen(cbackground => '/icons/GrayTextured.gif');
htp.tabledata(cvalue => htf.bold(htf.anchor('http://camdcrpt.nike.com:7777/pls/autosys_eds/autorpt_eds.main_page_harish',
'Main Menu')),
calign => 'LEFT',
cattributes => 'BGCOLOR="PINK"');
htp.fontopen(ccolor => 'brown',
csize => '4');
htp.bold('JOBS ON MACHINE :'|| p_machine,'center');
htp.tableopen (cattributes => 'WIDTH="25%"');
htp.fontOpen(ccolor => 'blue',
csize => '+2');
htp.tabledata(cvalue => htf.bold('Current Time : '||to_char(sysdate,'dd-Mon-yy hh24:mi')),
calign => 'left',
cattributes => 'BGCOLOR="#00FF00"');

htp.tableopen (cattributes => 'WIDTH="60%"');

htp.tabledata(cvalue => htf.bold(htf.anchor('http://camdcrpt.nike.com:7777/pls/autosys_eds/autorpt_eds.run_detail'
, 'Job name')),
cattributes => 'NOWRAP');
htp.tabledata(cvalue => htf.bold('Next Start Time'),
calign => 'RIGHT',
cattributes => 'NOWRAP');
htp.tabledata(cvalue => htf.bold('Status'),
calign => 'RIGHT',
cattributes => 'NOWRAP');

htp.tableopen (cattributes => 'BORDER="1" WIDTH="60%"');

open v_cur for v_cur_str;
fetch v_cur into v_joid, v_job_name, v_description, v_status, v_next_start, v_machine;
exit when v_cur%notfound;
htp.tabledata(cvalue => htf.anchor('http://camdcrpt.nike.com:7777/pls/autosys_eds/autorpt_eds.run_detail'||
'p_job_id='||v_joid,v_job_name,cattributes => ' title="'||v_description||'"||"'));
htp.tabledata(cvalue => v_next_start,
calign => 'LEFT');

htp.tabledata(cvalue => v_status,
calign => 'LEFT');
end loop;

htp.tableopen (cattributes => 'BORDER="1" WIDTH="90%"');

-- htp.print(v_cur_str);
htp.img(curl => 'http://camdcrpt.nike.com:7777/icons/back.gif',
calt => 'Error Generated',
cismap => 'ISMAP');
htp.anchor('http://camdcrpt.nike.com:7777/pls/autosys_eds/autorpt_eds.main_page_harish','Check Jobs For Another Machine');

This code gets all the datas from 2 tables stored in the server.
It displays the info like this
Main Menu

JOBS ON MACHINE :adminprod

Current Time : 03-Mar-09 09:44
Job name Next Start Time Status
AP101C1100_CHINA_AUTH_BILL 04-Mar-2009 07:00 Success
AP102C1100_CHINA_EVENT_REPORT 04-Mar-2009 09:00 Success
AP102C1100_CHINA_INVOICING 04-Mar-2009 08:00 Success
AP102C1100_CHINA_MANAGE_PYMNT 03-Mar-2009 10:00 Success
AP102C1100_CHINA_SCORECARD 03-Mar-2009 10:00 Success
AP102C1100_CHINA_SHIP_CONFIRM 03-Mar-2009 23:00 Success
AP102C1110_CHINA_DELTA_LOADER 08-Sep-2001 17:46 Success
AP102C1110_CHINA_PENDING_PYMT 08-Sep-2001 17:46 Success
AP318C1100_MON_OMS_IN_NPQ 03-Mar-2009 10:00 Success
EM101C1100_EMEAPRODBASELINE 31-Oct-2008 02:30 On hold
EM101C1100_EMEAPROMWS 31-Oct-2008 04:00 On hold
EM101C1100_EMEAQABASELINE 23-Nov-2008 08:45 On ice
EM101C1100_EMEAWSBASELINE 23-Nov-2008 08:15 On ice
EM101C1100_EMEA_AUTH_BILL 03-Mar-2009 10:00 Success
EM101C1100_EMEA_BATCHSCORECARD 03-Mar-2009 23:00 Success
EM101C1100_EMEA_DIMENSION_ID 03-Mar-2009 19:00 Success
EM101C1100_EMEA_EVENT_REPORT 04-Mar-2009 06:00 Success
EM101C1100_EMEA_FACTORY_PROC 03-Mar-2009 19:00 Success
EM101C1100_EMEA_INVOICING 03-Mar-2009 11:00 Success
EM101C1100_EMEA_INV_UPDATE 03-Mar-2009 13:00 Success
EM101C1100_EMEA_KIOSK_IMPORT 04-Mar-2009 05:00 Terminated
EM101C1100_EMEA_LOADORDERTOOMS 03-Mar-2009 10:00 Success
EM101C1100_EMEA_MANAGE_PYMNT 03-Mar-2009 10:00 Success
EM101C1100_EMEA_ORDERPICKUP 03-Mar-2009 23:00 Success
EM101C1100_EMEA_ORDER_EXPORT 03-Mar-2009 19:00 Success
EM101C1100_EMEA_ORD_STATUS_UPD 03-Mar-2009 22:00 Success
EM101C1100_EMEA_PENDING_PAYMNT 03-Mar-2009 13:00 Success
EM101C1100_EMEA_SHIP_CONFIRM 03-Mar-2009 10:00 Success
EM101C1100_OS_EMEA_PROM_WS 03-Mar-2009 16:00 Success
EM101C1100_OS_EMEA_PR_BASELINE 03-Mar-2009 09:45 Success
EM101C1100_OS_EMEA_WS_BASELINE 03-Mar-2009 09:38 Running
EM101C1110_EMEA_INV_ARCHIVE 08-Sep-2001 17:46 Success
EM101C1110_EMEA_IP_SALES_ID 08-Sep-2001 17:46 Success
EM101C1110_EMEA_ISLAND_PACIFIC 08-Sep-2001 17:46 Success
EM101C1110_EMEA_REPORT_VERIFY 08-Sep-2001 17:46 Success
EM101C1110_EMEA_SCP_ORD_EXPORT 08-Sep-2001 17:46 Success
EM116C1110_EMEA_DELTA_LOADER 08-Sep-2001 17:46 Success
EM116C1110_EMEA_TO_ENDECA_PREV 08-Sep-2001 17:46 Success
EM116C1110_EMEA_TO_ENDECA_PROD 08-Sep-2001 17:46 On hold
EM116C1110_OS_SCP_EMEA_ENDECA 08-Sep-2001 17:46 Success
EM318C1100_MON_DMS_MSG_PNEM 26-Jan-2009 23:00 On hold
EM318C1100_MON_EMEA_ORDER_CNT 03-Mar-2009 10:00 Success
EM318C1100_MON_OMS_IN_NPQ 03-Mar-2009 10:00 Success
EM318C1100_NIKEPLUS_EMEA_EMAIL 04-Mar-2009 04:00 Success
EM318C1310_REF_EMEA_CAT_ENV1 26-Feb-2009 18:00 Success
EM318C1310_REF_EMEA_CAT_ENV2 08-Sep-2001 17:46 Success
EM318C1310_REF_EMEA_CAT_ENV4 08-Sep-2001 17:46 Terminated
EM318C1310_REF_EMEA_DSS_ENV1 08-Sep-2001 17:46 Inactive
EM318C1310_REF_EMEA_DSS_ENV2 08-Sep-2001 17:46 Inactive
EM318C1310_REF_EMEA_DSS_ENV4 08-Sep-2001 17:46 Inactive
EM318C1310_REF_EMEA_DSS_ENV5 08-Sep-2001 17:46 Inactive
EM318C1310_REF_EMEA_ENV1 08-Sep-2001 17:46 Inactive
EM318C1310_REF_EMEA_ENV2 08-Sep-2001 17:46 Terminated
EM318C1310_REF_EMEA_ENV4 08-Sep-2001 17:46 Inactive
EM318C1310_REF_EMEA_ENV5 08-Sep-2001 17:46 Inactive
GL101C1110_OMS_PROCESORDER_ETC 30-Jan-2009 10:30 On hold
GL101C1110_OMS_PROCESSORDER_EM 30-Jan-2009 10:30 On hold
GL101C1110_OMS_PROCESSORDER_US 03-Mar-2009 10:30 Success
GL102C1100_OMS_DELIVERY_STATUS 04-Mar-2009 04:01 Success
GL102C1110_OMS_EXPORT 08-Sep-2001 17:46 On hold
GL102C1110_OMS_PROCESSORDER_CN 30-Jan-2009 10:30 On hold
GL102C1120_OMS_DLVRY_STS_EMAIL 08-Sep-2001 17:46 Success
GL207C1100_WOMEN_SIGNUP_CNT 08-Mar-2009 00:15 Success
GL215C1100_GODDESS_WKLY_RPT 03-Mar-2009 10:00 Success
GL225C1100_GRIDIRON_TBL_DELETE 04-Mar-2009 00:00 Success
GL230C1100_FZ4_BATCH 04-Mar-2009 00:00 Success
GL230C1100_FZT_UPD_HIGHSCORE 13-Jun-2006 23:00 On ice

Sorry this is a long post.
Here my query is. Like in web pages if i click the column names like "next start time" or "status" all the values in that colimn must be automatically arranges in ascending order.
Please help me on this.
Re: Sorting [message #389815 is a reply to message #389810] Tue, 03 March 2009 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65154
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Add an "order by" clause.

icon4.gif  Sorting in oracle [message #389860 is a reply to message #389810] Tue, 03 March 2009 17:08 Go to previous messageGo to next message
Messages: 2
Registered: March 2009
Junior Member
Hey guys,
Nailed down the exact problem

I have two tables
and job_status@asys which has numeric values.

I am displaying the values after decoding through a htp programming interface.

Here the values that i display are next_start and status
I have attached the screenshot.

I have the names of the column ("next_start" and "status") as links. The question is , how do i sort the values under these names if i click on the links.

Do i need to use a cursor if the link is clicked and then a order by clause or is there any other way.

Please help.
Re: Sorting in oracle [message #389861 is a reply to message #389860] Tue, 03 March 2009 17:16 Go to previous message
Messages: 25578
Registered: January 2009
Location: SoCal
Senior Member
I apologize but I don't understand much of what you posted.

You need to help us by following the Posting Guidelines as stated below.
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Is this thread the same problem as

[Updated on: Tue, 03 March 2009 19:18]

Report message to a moderator

Previous Topic: question based on Oracle PL/SQL Programming book code
Next Topic: Select "FOR UPDATE"
Goto Forum:

Current Time: Wed Aug 23 01:10:59 CDT 2017

Total time taken to generate the page: 0.09693 seconds