How to get the last date of record from multiple table in Oracle SQL

Question

Wrote this SQL and just need some help how to show the last WO created date on my sql. I tried the max(evt_created "WO Date Created" and group all my selected field and it's not working.

select
obj_udfchar09 "700 Whse - Slot #",
obj_person "Assign To",
obj_category "Category",
obj_class "Class",
obj_commiss "Commission Date",
OBJ_COSTCODE "Cost Code",
R5REP.REPGETDESC('EN','PERS',obj_user,NULL,NULL) "Created By",
OBJ_CREATED "Date Created",
obj_mrc "Department",
obj_desc "Description",
obj_variable1 "Equipment Book #",
obj_code "Equipment/Asset Tag",
obj_location "Location",
obj_udfchar03 "Maintenance Division",
obj_manufact "Manufacturer",
obj_manufactmodel "Model",
obj_notused "Out of Service",
obj_variable2 "Part",
obj_production "Production",
obj_variable5 "Refrigerant Amount",
obj_variable6 "Refrigerant Type",
obj_serialno "Serial Number",
STC_PARENT "System",
STC_PARENTTYPE "Type",
EVO_TOTAL AS "Cost Summary",
evt_code "WO",
evt_created "WO Date Created",
stc_parent "System Code",
     nvl(
     (select sum(nvl(a.evo_total, 0))
      from r5eventcost a 
      where a.evo_event in (select evt_code 
                from r5events 
                where evt_object = obj_code 
                and evt_object_org = obj_org
                and evt_rstatus in ('R', 'C')
                and evt_rtype in ('JOB', 'PPM'))), 0)
      + 
     nvl(
     (select sum(nvl(b.avc_total, 0))
      from r5eventcost_archive b 
      where b.avc_event in (select aev_code 
                from r5events_archive 
                where aev_object = obj_code
                and aev_object_org = obj_org
                and aev_rstatus in ('R', 'C')
                and aev_rtype in ('JOB', 'PPM'))), 0) "Total Cost"


from
r5objects, r5structures, r5events, r5eventcost

where
obj_code = stc_child (+) and
obj_rstatus = 'I' and
obj_code like '1%' and
EVT_CODE = EVO_EVENT (+) AND
EVT_OBJECT = OBJ_CODE (+) and
obj_code in ('1064016','1004188','1108146') and
STC_PARENTTYPE = 'S'

Please use modern `JOIN` syntax. Using `(+)` just makes your life more difficult. — The Impaler, Oct 17 '18 at 18:02
Also, please add prefixes to **all** the columns. It's very confusing and error prone if you don't. For example, instead of `stc_parent`, use `r.stc_parent`. — The Impaler, Oct 17 '18 at 18:07
Finally, in order to use the `MAX(...)` function, you need to aggregate the rows by some condition using `GROUP BY ...`, or by a window function using `OVER(...)`. — The Impaler, Oct 17 '18 at 18:09
Lots of people use Oracle's join format. Nothing wrong with posting a question in that format. — Bobby Durrett, Oct 17 '18 at 22:04
Could you show your query where you tried to group by all of the other columns besides evt_created and what error you got? That sounds like the right approach if you want the max evt_created value assuming all of the other values are held constant. — Bobby Durrett, Oct 17 '18 at 22:14
Thanks everyone for all your feedback. I am not a programmer and just inherited and tons of Cognos reports that are written in SQL. I tried the max(evt_created) and group all selected fields but was getting an error "SQL command not properly ended". — IGarland, Oct 18 '18 at 15:07

Giovanni Esposito · Answer 1 · 2018-10-18T17:32:32.770

0

You can order your query by WO Date Created desc and return the first row.

Maybe there are best ways to do what you need but it works.

An example,

SELECT  * 
FROM    <your_table> pu 
        INNER JOIN (
            SELECT *
            FROM    (SELECT ROWNUM, a.max_date
                    FROM (SELECT MAX(<your_date_column>) AS max_date
                          FROM <your_table>
                          GROUP BY <your_date_column>
                          ORDER BY <your_date_column> DESC) a)
            WHERE ROWNUM = 1) b ON pu.<your_date_column> = b.max_date;

edited Oct 18 '18 at 17:32

answered Oct 17 '18 at 20:34

Giovanni Esposito

76
8

Thanks Gio! I'll try this one. SQL is still foreign language to me so hopefully this will works. :) – IGarland Oct 18 '18 at 15:09
I tried this one and it didn't work. I have three equipment/asset tag on my where's clause and adding row 1 only shows one asset tag and the date didn't sorted properly so the date is not the latest. Thank you though for taking the time to read through my post and suggest. – IGarland Oct 18 '18 at 15:32
Hello, Thank you for your response. I've edited my post, please try this way and tell me if this works for you. – Giovanni Esposito Oct 18 '18 at 17:32
Thank you. I'll give it a try. I get confuse when joining four tables so hopefully I'll get this right this time. :) – IGarland Oct 18 '18 at 18:11

How to get the last date of record from multiple table in Oracle SQL

1 Answers1