Home » SQL & PL/SQL » SQL & PL/SQL » ORDER BY DECODE
ORDER BY DECODE [message #279092] Tue, 06 November 2007 21:18 Go to next message
skydancer
Messages: 1
Registered: November 2007
Location: Manila, Philippines
Junior Member

hello, could anyone help me with my code...

SELECT part.part_no_oem tool_part_number
,part.part_no_sdesc description
,tool.tool_cnt quantity
-- ,tool.sched_hr scheduled_hours
,to_char(decode(task_phase.start_dt
,NULL
,chk_phase.start_dt
,task_phase.start_dt)
,'dd-MON-yy') delivery_date
,to_char(chk_task.sched_start_dt
,'dd-MON-yy') required_date
,to_char(chk_task.sched_end_dt
,'dd-MON-yy') planned_end_date
,' ' tool_location
-- task information
,base_task.TASK_CD task_code
,chk_task.event_sdesc task_name
,task_ss.BARCODE_SDESC task_barcode
,chk_task.event_status_cd
,task_phase.start_dt task_delivery_dt
,task_phase.phase_cd task_phase
,chk_task.event_db_id
,chk_task.event_id
-- work package information
,chk_ss.wo_ref_sdesc work_order
,reg.ac_reg_cd tail_no
,chk.sched_start_dt sc_start_date
,chk.event_sdesc check_name
,chk.sched_end_dt sc_end_date
,loc_list.loc_cd
,loc_list.loc_name locations
,chk_phase.start_dt chk_delivery_dt
,chk_phase.phase_cd chk_phase
,chk.event_Status_Cd chk_status
,chk.event_db_id
,chk.event_id
-- report generation date and generated by
,to_char(SYSDATE
,'dd-MON-yyyy hh:mi:ss AM') print_date
,utl_user.first_name || ' ' || utl_user.last_name generated_by
FROM evt_event chk
,sched_stask chk_ss
,evt_Event chk_Task
,sched_stask task_ss
,(SELECT event_db_id
,event_id
,part_no_db_id
,part_no_id
,COUNT(1) tool_cnt --,sched_hr
FROM evt_tool
GROUP BY event_db_id
,event_id
,part_no_db_id
,part_no_id --, sched_hr
ORDER BY event_db_id
,event_id
,part_no_db_id
,part_no_id --, sched_hr
) tool
,evt_inv inv
,evt_loc
,inv_inv
,inv_ac_reg reg
,eqp_part_no part
,task_task base_task
,sched_prod_phase chk_phase
,sched_prod_phase task_phase
,(SELECT inv_loc.loc_id
,inv_loc.loc_Cd
,inv_loc.loc_db_id
,inv_loc.loc_name
FROM inv_loc
WHERE loc_type_cd IN ('TRACK', 'HGR')
CONNECT BY PRIOR loc_db_id = nh_loc_db_id
AND PRIOR loc_id = nh_loc_id
START WITH decode('{?as_ReportType}'
,'For Work Package only'
,'1'
,loc_name) =
decode('{?as_ReportType}'
,'For Work Package only'
,'1'
,'{?as_LocName}')
AND loc_type_cd = 'AIRPORT') loc_list
,utl_user
,ref_Task_class REF
WHERE
--filter all check
chk_ss.task_class_cd = 'CHECK'
AND chk_ss.sched_db_id = chk.event_db_id
AND chk_ss.sched_id = chk.event_id
AND chk.event_status_cd IN ('PLAN', 'COMMIT', 'IN WORK')
--report type filter
AND decode('{?ReportType}'
,'For Work Package only'
,chk.event_db_id
,1) = decode('{?ReportType}'
,'For Work Package only'
,{?aCheckDbId}
,1)
AND decode('{?ReportType}'
,'For Work Package only'
,chk.event_id
,1) = decode('{?ReportType}'
,'For Work Package only'
,{?aCheckId}
,1)
--check inventory
AND chk.event_db_id = inv.event_db_id
AND chk.event_id = inv.event_id
AND inv.main_inv_bool = 1
--check registration
AND inv.inv_no_db_id = inv_inv.inv_no_db_id
AND inv.inv_no_id = inv_inv.inv_no_id
AND inv_inv.h_inv_no_db_id = reg.inv_no_db_id
AND inv_inv.h_inv_no_id = reg.inv_no_id
--check location
AND chk.event_db_id = evt_loc.event_db_id
AND chk.event_id = evt_loc.event_id
--check location
AND evt_loc.loc_db_id = loc_list.loc_db_id
AND evt_loc.loc_id = loc_list.loc_id
--tasks in check
AND chk_task.h_event_db_id = chk.event_db_id
AND chk_task.h_event_id = chk.event_id
--task status filter
AND chk_task.event_status_cd IN ('ACTV', 'IN WORK')
--check prod phase link
AND chk_phase.sched_prod_db_id = chk_ss.sched_db_id
AND chk_phase.sched_prod_id = chk_ss.sched_id
AND chk_phase.sched_prod_phase_id = 1
--task prod_phase link
AND task_ss.sched_prod_db_id = task_phase.sched_prod_db_id(+)
AND task_ss.sched_prod_id = task_phase.sched_prod_id(+)
AND task_ss.sched_prod_phase_id = task_phase.sched_prod_phase_id(+)
--delivery date (phase start date) filter
AND ((upper('{?ChkDuration}') = 'TRUE') OR
(upper('{?ChkDuration}') = 'FALSE' AND
trunc(decode(task_phase.start_dt
,NULL
,chk_phase.start_dt
,task_phase.start_dt)) BETWEEN {?as_StartDate} AND {?as_EndDate}))
--details of task in check
AND chk_task.event_db_id = task_ss.sched_db_id
AND chk_task.event_id = task_ss.sched_id
--task class code
AND REF.task_class_db_id = task_ss.task_class_db_id
AND REF.task_class_cd = task_ss.task_class_cd
AND (task_ss.task_class_cd = 'CORR' OR REF.class_mode_cd = 'JIC')
--check task to baseline task defn
AND task_ss.task_db_id = base_Task.task_db_id(+)
AND task_ss.task_id = base_task.task_id(+)
--check tool reqts
AND chk_task.event_db_id = tool.event_db_id
AND chk_task.event_id = tool.event_id
AND tool.part_no_db_id = part.part_no_db_id
AND tool.part_no_id = part.part_no_id
--generated by info
AND utl_user.user_id = {?aUserId}

--order by decode('{?aSortOder}',
order by decode('Delivery Date',
'Tool Number/Driving Task ID',tail_no||part_number||task_cd,
'Driving Task ID/Tool Number',tail_no||task_cd||part_number,
'Delivery Date',tail_no||delivery_date,
'Work Order Number',tail_no||work_order,
'HM Facility Location',loc_list.loc_cd||loc_list.loc_name||tail_no,' ')


my question is - did i use the command ORDER BY DECODE properly? because when i executed the said script no error was generated. the requirement was - i have to sort the data based on the specified fields. if this is single field only -> no problem, but it requires more than one field. could anyone help me?

thanx
Re: ORDER BY DECODE [message #279093 is a reply to message #279092] Tue, 06 November 2007 21:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOW posting guidelines as stated here:
http://www.orafaq.com/forum/t/88153/0/

>my question is - did i use the command ORDER BY DECODE properly?

If you don't know the answer why do you expect any of us to know the answer?
We don't know the requirements.
We don't know the data.
We don't know what were the results.


>because when i executed the said script no error was generated. the requirement was - i have to sort the data based on the specified fields. if this is single field only -> no problem,

Congratulations

>but it requires more than one field.
What requires more than 1 field?

>could anyone help me?
Help you with what exactly?
I, for 1, don't know what is wrong or what would be the correct or desired results.





Re: ORDER BY DECODE [message #279138 is a reply to message #279093] Wed, 07 November 2007 01:59 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I very much doubt that you have used DECODE correctly in the Order by.
The decode statement:
decode('Delivery Date',
'Tool Number/Driving Task ID',tail_no||part_number||task_cd, 
'Driving Task ID/Tool Number',tail_no||task_cd||part_number, 
'Delivery Date',tail_no||delivery_date, 
'Work Order Number',tail_no||work_order, 
'HM Facility Location',loc_list.loc_cd||loc_list.loc_name||tail_no,' ') 
will always return
tail_no||delivery_date.

You should make the first parameter in the decode statement a variable, not a constant.
Previous Topic: NLS_TIMESTAMP_FORMAT behavior
Next Topic: Passing parameter
Goto Forum:
  


Current Time: Wed Nov 13 00:29:30 CST 2024