Help - Search - Members - Calendar
Full Version: Trouble ordering by date
Oracle DBA Forums > Oracle > Oracle Forum
dvsoukup
Hey folks! Popped in here to see if I could receive some help. Been cruising all sorts of ways to order by. My last ditch effort my be to use the WITH clause, but I don't want to go there just yet...

Using Oracle 10G against a database that uses a COTS CHAR datatype schema against an in-house varchar2 datatype schema (hence all the trims you will see in there so I can get proper joins) sad.gif

Here is my query in question. Beware it's somewhat eye-bleeding!

select * from (
SELECT
int.part "Part",
int.lot_nbr "Lot",
int.stkrm "Stkrm",
int.location "Location",
coalesce(MST.DESCRIPTION, IM.VDESC) "Description",
te.vendor_name "Mfr Name",
te.lot_mfg_cage_code "Mfg Cage Code",
te.vendor_lot_num "Vendor Lot Num",
te.diffusion_run_num "Diffusion Run Num",
te.wafer_run_num "Wafer Run Num",
mfr.dpa_exception "DPA Exception",
mfr.dpa_comment "DPA Comment",
mfr.dpa_tst_qty "DPA Test Qty",
mfr.rad_tst_qty "RAD Test Qty",
mfr.qual "Mfr Qual",
mfr.data_review "EDA Required",
mfr.qci "QCI Required",
int.project "PTS From",
int.project_1 "PTS To",
cr_from.eas_activity_id "Activity ID From",
cr_to.eas_activity_id "Activity ID To",
apl.apl_status "Project To APL Status",
RTRIM(XMLAGG(XMLELEMENT("APL", apl.apl_code || ', ') ORDER BY apl.apl_code).EXTRACT('//text()'), ', ') "Project To APL Code",
te.vendor_date_code "Vendor Date Code",
te.vendor_code "Vendor Code",
te.vendor_name "Vendor Name",
NVL2(qm.part, 'Yes', 'No') "Quarantined",
RTRIM(XMLAGG(XMLELEMENT("PMPO", qm.pmpo || ', ') ORDER BY qm.pmpo).EXTRACT('//text()'), ', ') "PMPO",
te.po_num "PO Num",
te.po_line_item "PO Line Item",
pl.name "Planner",
int.ref_nbr "Ref Nbr",
mst.hi_rel "Hi Rel",
TIPQA.DPA "DPA",
tipqa.radiation_test "Radiation Test",
TIPQA.UPGRADE_SCREENING "Upgrade screening",
int.qty "Qty",
int.um "Unit",
int.date_add "Date"
from
(select PART, LOT_NBR, STKRM, location, project, PROJECT_1, REF_NBR, DATE_ADD, UM, abs(SUM(qty)) as QTY
from (select
TRIM(PART) as PART, TRIM(LOT_NBR) as LOT_NBR, TRIM(STKRM) as STKRM, TRIM(location) as LOCATION, TRIM(project) as PROJECT,
TRIM(PROJECT_1) as PROJECT_1, TRIM(REF_NBR) as REF_NBR, TRIM(DATE_ADD) as DATE_ADD, TRIM(UM) as UM, TRIM(QTY) as QTY
from WDS49.INT_TABLE
where
PART = 'JANS1N6642US'
--date_add BETWEEN '01-DEC-2011' AND '21-DEC-2011'
and tran_code = '36'
and REASON in ('PX', 'NC')
and QTY < 0
order by date_add desc
)
where rownum <= 1000
group by PART, LOT_NBR, STKRM, location, project, PROJECT_1, REF_NBR, DATE_ADD, UM) int,
(select trim(part) as part, trim(vdesc) as vdesc, trim(planner) as planner from wds49.im_table) im,
wds49.pl_table pl,
cc_bolton_49.item_master mst,
cc_bolton_49.tracit_trace_elements te,
cc_bolton_49.tce_mfr_part mfr,
cc_bolton_49.tracit_quar_master qm,
cc_bolton_49.cc_project_cross_ref cr_from,
cc_bolton_49.cc_project_cross_ref cr_to,
(
SELECT
prj.project, apl.item_no,
apl.apl_code, prj.apl_status
FROM
(
SELECT project, apl_code, apl_status
FROM cc_bolton_49.tce_apl_project
GROUP BY project, apl_code, apl_status
) prj,
cc_bolton_49.tce_apl_project_part apl
WHERE
prj.project = apl.project
AND prj.apl_code = apl.apl_code
) apl,
(
SELECT part_number, lot_number, dpa, radiation_test, upgrade_screening
from CC_BOLTON_49.TRACIT_TIPQA_PART_TRANSFER
where dpa IS NOT NULL OR radiation_test IS NOT NULL OR upgrade_screening IS NOT NULL
) tipqa
WHERE
--Joins
im.part = int.part
AND pl.planner = im.planner
AND mst.item_no_char(+) = int.part
AND int.part = te.part_num(+)
AND int.lot_nbr = te.lot_num(+)
AND mfr.item_no(+) = te.part_num
AND mfr.mfr_code(+) = te.lot_mfg_cage_code
AND int.part = qm.part(+)
AND int.lot_nbr = qm.lot_num(+)
AND int.project = cr_from.cc_project(+)
AND int.project_1 = cr_to.cc_project(+)
AND SUBSTR(int.project_1, 0, 8) = apl.project(+)
AND int.part = apl.item_no(+)
AND int.part = tipqa.part_number(+)
and int.LOT_NBR = TIPQA.LOT_NUMBER(+)
GROUP BY
int.part,
int.lot_nbr,
int.stkrm,
int.location,
coalesce(mst.description, im.vdesc),
mfr.mfr_name,
te.lot_mfg_cage_code,
te.vendor_lot_num,
te.diffusion_run_num,
te.wafer_run_num,
mfr.dpa_exception,
mfr.dpa_comment,
mfr.dpa_tst_qty,
mfr.rad_tst_qty,
mfr.qual,
mfr.data_review,
mfr.qci,
int.project,
int.project_1,
cr_from.eas_activity_id,
cr_to.eas_activity_id,
apl.apl_status,
te.vendor_date_code,
te.vendor_code,
te.vendor_name,
NVL2(qm.part, 'Yes', 'No'),
te.po_num,
te.po_line_item,
pl.name,
int.ref_nbr,
mst.hi_rel,
tipqa.dpa,
tipqa.radiation_test,
TIPQA.UPGRADE_SCREENING,
int.qty,
int.UM,
int.date_add) order by "Date" desc
;



Now obviously no one would be able to run this... But here is an example of how this is spitting out the ordered by date:

31-Mar-2011 12:00:00 AM
31-Mar-2011 12:00:00 AM
31-Mar-2011 12:00:00 AM
28-Sep-2011 12:00:00 AM
28-Sep-2011 12:00:00 AM
28-Sep-2010 12:00:00 AM
28-Sep-2010 12:00:00 AM
28-Oct-2011 12:00:00 AM
27-Oct-2011 12:00:00 AM
27-Oct-2010 12:00:00 AM
26-Oct-2010 12:00:00 AM
24-Aug-2011 12:00:00 AM

It's like it's ordering it by the day only... Not sure how to go around this.
Anyone have any suggestions? Any more information I need to provide here?

Would greatly appreciate any help here.
burleson
Hi,

>> where rownum <= 1000

This rownum < nn delivers rows in randomized order . . . .

Also incorrect results:

http://www.dba-oracle.com/t_sql_tuning_rownum_equals_one.htm


*************************************************
>>> It's like it's ordering it by the day only...

I'll bet you nls_date_format is set to dd-MON-YYYY

http://dba-oracle.com/googlesearchsite_pro...ate_format&

Try:

CODE
alter session set nls_date_format = 'DD-Mon-YYYY HH24:MI:SS';

select date_add from yourtab;

select sysdate from dual;


Remember, there are 2 ways to sequence data rows:

1 - use an index and retrieve them in order
2 - do a back-end ORDER BY sort

>> 31-Mar-2011 12:00:00 AM
>> 31-Mar-2011 12:00:00 AM
>> 31-Mar-2011 12:00:00 AM

I hope that the date was not truncated when it was stored . . .

You can use the to_char with the date format specified to sort by ANY date format you choose:

http://www.dba-oracle.com/t_nls_date_format_sysdate.htm
dvsoukup
Just wanted to post back here and say thanks for the help on this! I went and fixed my NLS date format and now it's ordering properly. Had changed it several months ago so that I would 'automatically' see the timestamp.

Thank you!!
burleson
>> thanks for the help on this!

You are welcome!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.