Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join and MAX (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Outer Join and MAX [message #612094] Fri, 11 April 2014 18:51 Go to next message
OraFaq13
Messages: 14
Registered: May 2013
Junior Member
I wanted to pick the matching records for a detail table and if the detail table has multiple records for a program ID, then the most recent status from the detail table should be picked.

Master Table:
CREATE TABLE program_sa
  (PGM_ID NUMBER, PGM_NAME VARCHAR2(10)
  );
ALTER TABLE program_sa ADD CONSTRAINT pk1 PRIMARY KEY(pgm_id);




Detail Table:
CREATE TABLE program_assn
  (
    PGM_ID   NUMBER,
    BEGIN_DT DATE,
    END_DATE DATE,
    STATUS   VARCHAR2(3),
    CONSTRAINT fk1 FOREIGN KEY(pgm_id) REFERENCES program_sa(pgm_id)
  );


Insert Statement:
-- inserts for master
INSERT INTO program_sa VALUES
  (1,'Health'
  );
INSERT INTO program_sa VALUES
  (2,'Education'
  );
INSERT INTO program_sa VALUES
  (3,'Sports'
  );

-- for detail table
INSERT INTO program_assn VALUES
  (1,'01-Jan-2014', '10-Jan-14', 'AC'
  );
INSERT INTO program_assn VALUES
  (1,'11-Jan-2014', '20-Jan-14', 'PE'
  );
INSERT INTO program_assn VALUES
  (1,'21-Jan-2014', '31-Jan-14', 'DS'
  );



When I outer join the detail table by ID , it picks all the programs from program_sa table and its corresponding records from program_assn.

SELECT *
FROM program_sa p ,
  program_assn pa
WHERE p.PGM_ID      = pa.PGM_ID(+)
 ;


When I use the outer join to the begin_dt to pick the most recent from the detail table, it fails with an error message

SELECT *
FROM program_sa p ,
  program_assn pa
WHERE p.PGM_ID      = pa.PGM_ID(+)
AND pa.begin_dt(+) IN
  (SELECT MAX(begin_dt) FROM PROGRAM_ASSN pa1 GROUP BY pa1.pgm_id
  )
  ;


Error:
ORA-01799: a column may not be outer-joined to a subquery
01799. 00000 - "a column may not be outer-joined to a subquery"
*Cause: <expression>(+) <relop> (<subquery>) is not allowed.
*Action: Either remove the (+) or make a view out of the subquery.
In V6 and before, the (+) was just ignored in this case.
Error at Line: 32 Column: 2

CAn anyone help me to pick the most recent status from the detail table?
Re: Outer Join and MAX [message #612095 is a reply to message #612094] Fri, 11 April 2014 19:08 Go to previous messageGo to next message
BlackSwan
Messages: 23031
Registered: January 2009
Senior Member
>if the detail table has multiple records for a program ID, then the most recent status from the detail table should be picked.
which column in PROGRAM_ASSM table is used to determine "most recent status"?

what should be done when only header record exists & no detail record?

OUTER JOIN is not correct for this solution, IMO.


for the sample data provided, what are the desired results?
Re: Outer Join and MAX [message #612097 is a reply to message #612095] Fri, 11 April 2014 19:52 Go to previous messageGo to next message
OraFaq13
Messages: 14
Registered: May 2013
Junior Member
The most recent status would depend on the begin_dt. The most recent beg_date would have the recent status.

IF there are no matching records in the detail table, display the program Id and program_name from master table with null values for detail table columns.

The desired output would like

Program_ID  Program_Name   Begin_Dt    End_Dt      Status
-----------------------------------------------------------
1           Health         21-Jan-14   31-Jan-14   DS
2           Education      NULL        NULL        NULL
3           Sports         NULL        NULL        NULL

Re: Outer Join and MAX [message #612101 is a reply to message #612094] Sat, 12 April 2014 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59750
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

BlackSwan and I are waiting for your feedback on their solution about your previous problems:

http://www.orafaq.com/forum/m/608865/#msg_608865
http://www.orafaq.com/forum/m/611900/#msg_611900

If I think I didn't help you I then think it is not worth to waste my time to try to help you now. Sad

I will nevertheless add a helpful (imo) comment on my previous helpful one about dates: your test case is not correct: Smile
SQL> INSERT INTO program_assn VALUES
  2    (1,'01-Jan-2014', '10-Jan-14', 'AC'
  3    );
  (1,'01-Jan-2014', '10-Jan-14', 'AC'
     *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected


Re: Outer Join and MAX [message #612103 is a reply to message #612101] Sat, 12 April 2014 01:44 Go to previous messageGo to next message
OraFaq13
Messages: 14
Registered: May 2013
Junior Member
Apologies for the delay in my responses for the earlier queries. I just updated one of them now.

I was able to execute the insert without any issues - tried it again and didnt have any issues loading the data.
  • Attachment: insert.jpg
    (Size: 21.56KB, Downloaded 30 times)
Re: Outer Join and MAX [message #612104 is a reply to message #612103] Sat, 12 April 2014 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59750
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The fact it does not work for me PROVES it is wrong.
The fact it works for you just shows you are lucky.
Read about TO_DATE function and DATE literals in Database SQL Reference.

[Updated on: Sat, 12 April 2014 01:57]

Report message to a moderator

Re: Outer Join and MAX [message #612106 is a reply to message #612104] Sat, 12 April 2014 02:37 Go to previous messageGo to next message
OraFaq13
Messages: 14
Registered: May 2013
Junior Member
I have changed my insert statement not to use the strings for date column. Again, I am not sure why it worked for me earlier and not you. May be my DB version(11g) is different from yours. However , thanks for the link.

INSERT
INTO program_assn VALUES
  (
    1,
    TRUNC(SYSDATE,'RRRR'),
    TRUNC(SYSDATE,'RRRR') + 9 ,
    'AC'
  );
INSERT
INTO program_assn VALUES
  (
    2,
    TRUNC(SYSDATE,'RRRR') +10,
    TRUNC(SYSDATE,'RRRR') +19 ,
    'PE'
  );
INSERT
INTO program_assn VALUES
  (
    3,
    TRUNC(SYSDATE,'RRRR')+20,
    LAST_DAY(TRUNC(SYSDATE,'RRRR')) ,
    'DS'
  );
Re: Outer Join and MAX [message #612115 is a reply to message #612106] Sat, 12 April 2014 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59750
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Again, I am not sure why it worked for me earlier and not you.


Because you didn't read what I told you to read.
Read also about NLS_DATE_FORMAT in Database Reference.

Re: Outer Join and MAX [message #612116 is a reply to message #612115] Sat, 12 April 2014 04:05 Go to previous messageGo to next message
OraFaq13
Messages: 14
Registered: May 2013
Junior Member
I understand it now. I could have used to_date() for the date columns.

With respect to the original question, if outer join is not appropraite - how could we do this?
Re: Outer Join and MAX [message #612117 is a reply to message #612116] Sat, 12 April 2014 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59750
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With respect to us, feedback and thank people that gave you solutions in your previous topics.

Re: Outer Join and MAX [message #612118 is a reply to message #612117] Sat, 12 April 2014 05:11 Go to previous message
OraFaq13
Messages: 14
Registered: May 2013
Junior Member
I did updated my feedback/Thanks to the earlier posts. I did made mistake by not responding to those replies quickly but will correct that going forward. Thanks to you too for all the help.
Previous Topic: # character is used in column
Next Topic: Multiple counts as one
Goto Forum:
  


Current Time: Sun Nov 23 10:09:40 CST 2014

Total time taken to generate the page: 0.10674 seconds