Home » SQL & PL/SQL » SQL & PL/SQL » ORDER BY DECODE
ORDER BY DECODE [message #279092] |
Tue, 06 November 2007 21:18 |
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 #279138 is a reply to message #279093] |
Wed, 07 November 2007 01:59 |
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.
|
|
|
Goto Forum:
Current Time: Wed Nov 13 00:29:30 CST 2024
|