Home » SQL & PL/SQL » SQL & PL/SQL » Max function (PL SQL )
| Max function [message #665054] |
Sun, 20 August 2017 16:34  |
 |
OldDog
Messages: 10
Registered: May 2017
|
Junior Member |
|
|
I have two history tables.
Parent table:
CASE TYPE SUBTYPE INSERTED
PX-100 OP OBS 1/1/2017
PX-100 INP SRG 1/5/2017
Child table:
CASE ID CODE INSERTED
PX-100 1 99605 1/1/2017
PX-100 1 88423 1/1/2017
PX-100 2 99222 1/10/2017
PX-100 2 44444 1/10/2017
How do I get my output to look like this?
CASE TYPE SUBTYPE ID CODE
PX-100 INP SRG 1 99222
PX-100 INP SRG 2 44444
|
|
|
|
|
|
| Re: Max function [message #665065 is a reply to message #665054] |
Mon, 21 August 2017 01:18  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
What are your primary key and foreign key constraints?
If you provide the CREATE TABLE and INSERT statements, enclosed within [code] tags, perhaps all will become clear.
|
|
|
|
|
|
| Re: Max function [message #665091 is a reply to message #665054] |
Mon, 21 August 2017 12:54 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Sounds simple enough. I am going to make all assumptions because no details were given.
Join on case, use date >= 1/10/2017, use rownum and a descending order by.
|
|
|
|
| Re: Max function [message #665092 is a reply to message #665091] |
Mon, 21 August 2017 13:05 |
|
OldDog
Messages: 10
Registered: May 2017
|
Junior Member |
|
|
This is a simplified version of my table and queries because I am looking for concept first.
I only want to return the most resent values as these are history tables with multiple records for every case.
select C.CASE, C.TYPE, C.SUBTYPE, P.CODE, MAX(C.INSERTED), MAX(P.INSERTED)
FROM CASE C
LEFT OUTERJOIN PROCEDURE P ON C.CASE = P.CASE
GROUP BY C.CASE, C.TYPE, C.SUBTYPE, P.CODE
When I use the max value, I get the max value (obviously) instead of last entry and "LAST" function does not work.
PL/SQL 12.0.1.1814
|
|
|
|
|
|
|
|
|
|
| Re: Max function [message #665110 is a reply to message #665096] |
Tue, 22 August 2017 02:51 |
 |
quirks
Messages: 82
Registered: October 2014
|
Member |
|
|
Hello OldDog,
first, we expect you to do everything you can to let us focus on the problem itself. That's because there are so many requests that it would be a waste of time if everyone is creating the test data for himself. So, you better provide a create script for the test data or some kind of virtual recordset:
-- Parent Table
SELECT 'PX-100' AS CASE, 'OP' AS TYPE, 'OBS' AS SUBTYPE, TO_DATE('1/1/2017', 'MM/DD/YYYY') INSERTED FROM DUAL
UNION
SELECT 'PX-100', 'INP', 'SRG', TO_DATE('1/5/2017', 'MM/DD/YYYY') FROM DUAL;
-- Child Table
SELECT 'PX-100' AS CASE, 1 AS ID, 99605 AS CODE, TO_DATE('1/1/2017', 'MM/DD/YYYY') AS INSERTED FROM DUAL
UNION
SELECT 'PX-100', 1, 88423, TO_DATE('1/1/2017', 'MM/DD/YYYY') FROM DUAL
UNION
SELECT 'PX-100', 2, 99222, TO_DATE('1/10/2017', 'MM/DD/YYYY') FROM DUAL
UNION
SELECT 'PX-100', 2, 44444, TO_DATE('1/10/2017', 'MM/DD/YYYY') FROM DUAL;
With that off the table, your expected result should somehow make sense. But in your case there is a problem, because in the first record the value for ID does not match the value for CODE. So either your example is wrong or your explanation.
I guess the value for ID must be 2? I suspect that because you wrote:
OldDog wrote on Mon, 21 August 2017 13:22I am not able to pull the most recent record from both the parent and child tables in a meaningful fashion.
Which leads me to the assumption that you extract your data from some kind of data warehouse. Usualy you don't have figure out the last valid recordset, because the join between the tables is NEVER done by natural values but by generic keys. Please watch out for these in you productive data. It might save you some headaches.
Whatever... We have to work with what you gave us. I've once seen a somehow misconcepted data structure where indeed the join was made by the natural keys. But to come over the resulting problems they flagged the last valid record. Please watch out in your productive data for such a flag. In my Answer I create this flag manually (but you should not have to do that, if so, it's bad data design).
WITH
PARTENT_TABLE
AS
(SELECT 'PX-100' AS CASE, 'OP' AS TYPE, 'OBS' AS SUBTYPE, TO_DATE('1/1/2017', 'MM/DD/YYYY') INSERTED FROM DUAL
UNION
SELECT 'PX-100', 'INP', 'SRG', TO_DATE('1/5/2017', 'MM/DD/YYYY') FROM DUAL),
CHILD_TABLE
AS
(SELECT 'PX-100' AS CASE, 1 AS ID, 99605 AS CODE, TO_DATE('1/1/2017', 'MM/DD/YYYY') AS INSERTED FROM DUAL
UNION
SELECT 'PX-100', 1, 88423, TO_DATE('1/1/2017', 'MM/DD/YYYY') FROM DUAL
UNION
SELECT 'PX-100', 2, 99222, TO_DATE('1/10/2017', 'MM/DD/YYYY') FROM DUAL
UNION
SELECT 'PX-100', 2, 44444, TO_DATE('1/10/2017', 'MM/DD/YYYY') FROM DUAL),
PREPARE_PARENT_TABLE
AS
(SELECT CASE
,TYPE
,SUBTYPE
,INSERTED
,CASE WHEN INSERTED = MAX(INSERTED) OVER (PARTITION BY CASE) THEN 'Y' ELSE 'N' END LAST_VALID_ENTRY
FROM PARTENT_TABLE),
PREPARE_CHILD_TABLE
AS
(SELECT CASE
,ID
,CODE
,INSERTED
,CASE WHEN INSERTED = MAX(INSERTED) OVER (PARTITION BY CASE) THEN 'Y' ELSE 'N' END LAST_VALID_ENTRY
FROM CHILD_TABLE)
SELECT PREPARE_PARENT_TABLE.CASE
,PREPARE_PARENT_TABLE.TYPE
,PREPARE_PARENT_TABLE.SUBTYPE
,PREPARE_CHILD_TABLE.ID
,PREPARE_CHILD_TABLE.CODE
FROM PREPARE_PARENT_TABLE JOIN PREPARE_CHILD_TABLE ON (PREPARE_PARENT_TABLE.CASE = PREPARE_CHILD_TABLE.CASE)
WHERE PREPARE_PARENT_TABLE.LAST_VALID_ENTRY = 'Y' AND PREPARE_CHILD_TABLE.LAST_VALID_ENTRY = 'Y';
I hope my post was a little bit helpful. Next time you'll be better prepared. I'd like to send you a warm welcome to our forum.
Don't hesitate to send further request but do your homework before 
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 13:50:31 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
