|
|
Hi Erman,
when we run below code, the field unique_date is not sorted in ascending since it is character. Is there a way I can convert unique_date to TIMESTAMP format 'DD-MON-YYYY HH12:MI:SS'?
select * FROM (select
distinct(a.apt_order_id) as orderid,
a.apt_date as aptdate,
/*coalesce(a.apt_mdate, a.apt_cdate) as unique_date,*/
coalesce(TO_CHAR(a.apt_mdate, 'DD-MON-YYYY HH12:MI:SS'), (TO_CHAR(a.apt_cdate, 'DD-MON-YYYY HH12:MI:SS'))) as unique_date,
b.cws_status as status,
(
select
status_description
from
L2TCP.gci_clm_status_mapping
where
status = b.cws_status
and rownum = '1'
) as statusdesc,
d.exa_area as area
from
L2TCP.gci_appointment a,
L2TCP.gci_contractor_work_status b,
L2TCP.gci_clmorder c,
L2TCP.gci_ref_exchange_area d
where
a.apt_order_id = B.CWS_ORDER_ID
and B.CWS_STATUS in (
select
status
from
L2TCP.gci_clm_status_mapping
where
status not in ('WWIP', 'WCOM')
)
and trunc(b.cws_cdate) = a.apt_date
and b.cws_cdate =(
select
max(cws_cdate)
from
L2TCP.gci_contractor_work_status
where
cws_order_id = a.apt_order_id
)
and a.apt_order_id = c.clm_order_id
and substr(c.clm_mdf, 1, 2) = d.exa_exchange
order by cws_cdate ASC
)
WHERE unique_date > TO_TIMESTAMP('24-Jul-2020', 'DD-MON-YYYY HH12:MI:SS')
ORDER BY UD ASC;
Thanks,
Roshan
|
Locked
