Hello..
Please bear with me as I am new to all this.
can someone assist me in figuring out the most appropriate method to obtain the most recently entered "NOTES" against the corresponding ORDER_NUMBER
I've used the below code. However this will only give the first entry made on "NOTES"
Is there a more efficient method of obtaining the most recent entry made under "NOTES" without having to change the ROWNUM
Reason is that an ORDER_NUMBER can have multiple ORDER_LINES and each line will have "NOTES" entered against it depending on the updates entered by the users. So when the report is executed the ORDER_NUMBER and corresponding ORDER_LINE should only show the most recently entered "NOTE_PAD"."NOTES"
Would really appreciate any assistance, as I have been trying to look for some answers online, but not been very lucky
SELECT *
FROM ( SELECT ORDER_DETAIL.CREATED_DATE,
ORDER_DETAIL.ORDER_NUMBER,
ORDER_DETAIL.ORDER_TYPE,
ORDER_AWB.ORDER_LINE,
ORDER_HEADER.RELATION_CODE,
ORDER_AWB.NOTES,
NOTE_PAD.NOTES_TEXT,
NOTE_PAD.CREATED_DATE AS NOTE_DATE,
ROW_NUMBER ( ) OVER ( PARTITION BY ORDER_DETAIL.ORDER_NUMBER,
ORDER_DETAIL.ORDER_LINE
ORDER BY ORDER_DETAIL.CREATED_DATE DESC ) AS SEQNUM
FROM ORDER_DETAIL,
ORDER_HEADER,
ORDER_AWB,
NOTE_PAD
WHERE ( "ORDER_DETAIL"."ORDER_NUMBER" = "ORDER_HEADER"."ORDER_NUMBER" )
AND ( "ORDER_HEADER"."ORDER_NUMBER" = "ORDER_AWB"."ORDER_NUMBER" )
AND ( "ORDER_AWB"."NOTES" = "NOTE_PAD"."NOTES" ( + ) ) )
WHERE ORDER_NUMBER = '991428'
AND ROWNUM <= 1
The result I need is as below, the "NOTES_TEXT" column needs to show the most recently entered data and not all the values entered. If there is no value entered then it should show blank as see here;
The order_awb table is "joined" to order_number, but not to a specific order line.
Also, you refer to rownum in the outer select, whereas you alias the field seqnum in the inner select.
select *
from (select d.created_date
,d.order_number
,d.order_type
,d.order_line
,h.relation_code
,a.notes
,n.notes_text
,n.created_date as note_date
,row_number() over(partition by d.order_number
,d.order_line
order by n.created_date desc
)
as seqnum
from order_header as h
inner join order_detail as d
on d.order_number=h.order_number
inner join order_awb as a
on a.order_number=d.order_number
and a.order_line=d.order_line
left outer join note_pad as n
on n.notes=a.notes
where h.order_number='991766'
) as t
where seqnum<=1
;
If you still don't get notes, try changing "inner join" to "left outer join" on table order_awb
Thank you very much, this works. However I've noticed that if I enter two NOTES_TEXT on the same date it will only show me one entry against the NOTES_TEXT. Even though the notes are an hour apart. I am guessing this is to do with the time/date format.
Is there a method I can covert in the NOTE_PAD.CREATED_DATE within the ROW_NUMBER ? so this way when it's sorts the result will be the most recent entry.
I've tried using below, but no luck: ORDER BYTO_CHAR (NOTE_PAD.CREATED_DATE, 'DD-MM-YYYY HH:MM:SS')
select *
from (select d.created_date
,d.order_number
,d.order_type
,d.order_line
,h.relation_code
,a.notes
,n.notes_text
,n.created_date as note_date
,row_number() over(partition by d.order_number
,d.order_line
order by n.created_date desc
,n.notes_line desc
)
as seqnum
from order_header as h
inner join order_detail as d
on d.order_number=h.order_number
inner join order_awb as a
on a.order_number=d.order_number
and a.order_line=d.order_line
left outer join note_pad as n
on n.notes=a.notes
where h.order_number='991766'
) as t
where seqnum<=1
;
