Home » SQL & PL/SQL » SQL & PL/SQL » Get last record from another table (11g)
icon1.gif  Get last record from another table [message #670609] Mon, 16 July 2018 10:50 Go to next message
ritaman
Messages: 10
Registered: September 2011
Location: Ireland
Junior Member
Hi there,
I would really appreciate your help on interrogating my tables.
I want to a list of Material Codes, the last Cost Code used and the Nominal Code associated with that Cost Code.

My 3 tables which I would like to interrogate as follows;
Material_Codes (A)
Material_Code
Category_Code
Material_Description


Unique Key is Material_Code

Material_Cost_Codes (B)
Material_Code
Supplier_Code
Cost_Code
Category_Code
Updated_Date

Unique key is Cost_Code + Supplier_Code + Material_code


Cost_Code (C)
Cost_Code
Cost_Description
Nominal_Code


Unique Key is Cost_Code


I wish to write a query which will list;
A.Material_Code, A.Category_code, A.Material_Description, B.Cost_Code *, C.Cost_Description, C.Nominal_Code

• This is the last Cost_Code used i.e. Latest B.Updated_date for this Material_Code

Hope I have explained this sufficiently.
Thanking you in advance for your help.

Best regards
Rita
Re: Get last record from another table [message #670619 is a reply to message #670609] Mon, 16 July 2018 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 26620
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Get last record from another table [message #670620 is a reply to message #670609] Mon, 16 July 2018 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Get last record from another table [message #670627 is a reply to message #670620] Tue, 17 July 2018 02:41 Go to previous messageGo to next message
ritaman
Messages: 10
Registered: September 2011
Location: Ireland
Junior Member
  CREATE TABLE "CONSTRUCTION"."AC_MATERIAL_CODES" 
   (	"CATEGORY_CODE" NUMBER NOT NULL ENABLE, 
	"MATERIAL_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
	"MATERIAL_DESCRIPTION" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"DEFAULT_COST_CODE" VARCHAR2(4 BYTE), 
	"DEFAULT_UNIT" VARCHAR2(4 BYTE), 
	"CREATED_BY" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_BY" VARCHAR2(50 BYTE), 
	"UPDATED_DATE" DATE, 
	"ACTIVE" VARCHAR2(1 BYTE), 
	 PRIMARY KEY ("MATERIAL_CODE")

	  CONSTRAINT "ACCODE_NOM_FK" FOREIGN KEY ("DEFAULT_COST_CODE")
	  REFERENCES "CONSTRUCTION"."AC_COST_CODES" ("COST_CODE") ENABLE
   ) ;



CREATE TABLE "CONSTRUCTION"."AC_CODE_MATERIAL_SUPPLIERS" 
   (	"COST_CODE" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"CATEGORY_CODE" NUMBER NOT NULL ENABLE, 
	"SUPPLIER_CODE" VARCHAR2(8 BYTE) NOT NULL ENABLE, 
	"MATERIAL_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
	"CREATED_BY" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_BY" VARCHAR2(50 BYTE), 
	"UPDATED_DATE" DATE, 
	 PRIMARY KEY ("COST_CODE", "CATEGORY_CODE", "SUPPLIER_CODE", "MATERIAL_CODE")
  
	  CONSTRAINT "AC_COSTMATSUP_MAT_FK" FOREIGN KEY ("MATERIAL_CODE")
	  REFERENCES "CONSTRUCTION"."AC_MATERIAL_CODES" ("MATERIAL_CODE") ENABLE, 
	 CONSTRAINT "AC_COSTMATSUP_COST_FK" FOREIGN KEY ("COST_CODE")
	  REFERENCES "CONSTRUCTION"."AC_COST_CODES" ("COST_CODE") ENABLE
   ) ;




CREATE TABLE "CONSTRUCTION"."AC_COST_CODES" 
   (	"COST_CODE" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"CODE_DESCRIPTION" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"CODE_TYPE" VARCHAR2(1 BYTE), 
	"NOMINAL_CODE" VARCHAR2(8 BYTE), 
	"CREATED_BY" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_BY" VARCHAR2(50 BYTE), 
	"UPDATED_DATE" DATE, 
	"ACTIVE" VARCHAR2(1 BYTE), 
	 CONSTRAINT "AC_COST_CODES_PK" PRIMARY KEY ("COST_CODE")

  ;

Re: Get last record from another table [message #670628 is a reply to message #670627] Tue, 17 July 2018 03:04 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've ignored half of Michel's post.

[Updated on: Tue, 17 July 2018 03:04]

Report message to a moderator

Re: Get last record from another table [message #670629 is a reply to message #670628] Tue, 17 July 2018 03:11 Go to previous messageGo to next message
ritaman
Messages: 10
Registered: September 2011
Location: Ireland
Junior Member
Sorry - I was working on the sql to populate the tables but I think it might be better just to send a spreadsheet.....

I have uploaded an Excel Spreadsheets which show the tables and a little data which I hope explains what I am looking for.
I've simplified the tables.
Sorry if it's not too technical.
Thanks in advance

[Updated on: Tue, 17 July 2018 04:24]

Report message to a moderator

Re: Get last record from another table [message #670633 is a reply to message #670629] Tue, 17 July 2018 04:36 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
You haven't uploaded a spreadsheet.
A lot of people won't download a spreadsheet.
Inserts are far better, we can run them into our own DBs and try queries out.

There's an special version of the aggregate max function that lets you get the value of one column that is the on the same row as the max of something else. Which is probably what you need.
You can see an example here:
https://livesql.oracle.com/apex/livesql/file/content_FP499O87G9NJ3CA3JKIJA5XM8.html
It's the last couple of examples.

If that doesn't work for you post the inserts here, along with what you tried and we'll see what we can do.
Re: Get last record from another table [message #670637 is a reply to message #670633] Tue, 17 July 2018 07:04 Go to previous messageGo to next message
hiddenTR
Messages: 1
Registered: July 2018
Junior Member
Hi there,
I would really appreciate your help on interrogating my tables.
I want to a list of Material Codes, the last Cost Code used and the Nominal Code associated with that Cost Code.

My 3 tables which I would like to interrogate as follows;
Material_Codes (A)
Material_Code
Category_Code
Material_Description

Unique Key is Material_Code

Material_Cost_Codes (B)
Material_Code
Supplier_Code
Cost_Code
Category_Code
Updated_Date

Unique key is Cost_Code + Supplier_Code + Material_code


Cost_Code (C)
Cost_Code
Cost_Description
Nominal_Code

Unique Key is Cost_Code


I wish to write a query which will list;
A.Material_Code, A.Category_code, A.Material_Description, B.Cost_Code *, C.Cost_Description, C.Nominal_Code

• This is the last Cost_Code used i.e. Latest B.Updated_date for this Material_Code

Hope I have explained this sufficiently.
Thanking you in advance for your help.

Best regards
Rita
Report message to a moderator
Send a private message to this user
Re: Get last record from another table [message #670619 is a reply to message #670609] Mon, 16 July 2018 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 26057
Registered: January 2009
Location: SoCal
Senior Member
add to buddy list
ignore all messages by this user
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator
Send a private message to this user
Re: Get last record from another table [message #670620 is a reply to message #670609] Mon, 16 July 2018 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65561
Registered: March 2007
Location: Nanterre, France
Senior Member
Account Moderator
add to buddy list
ignore all messages by this user

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator
Send a private message to this user
Re: Get last record from another table [message #670627 is a reply to message #670620] Tue, 17 July 2018 02:41 Go to previous messageGo to next message
ritaman
Messages: 6
Registered: September 2011
Location: Ireland
Junior Member
add to buddy list
ignore all messages by this user
CREATE TABLE "CONSTRUCTION"."AC_MATERIAL_CODES"
( "CATEGORY_CODE" NUMBER NOT NULL ENABLE,
"MATERIAL_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"MATERIAL_DESCRIPTION" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"DEFAULT_COST_CODE" VARCHAR2(4 BYTE),
"DEFAULT_UNIT" VARCHAR2(4 BYTE),
"CREATED_BY" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"UPDATED_BY" VARCHAR2(50 BYTE),
"UPDATED_DATE" DATE,
"ACTIVE" VARCHAR2(1 BYTE),
PRIMARY KEY ("MATERIAL_CODE")

CONSTRAINT "ACCODE_NOM_FK" FOREIGN KEY ("DEFAULT_COST_CODE")
REFERENCES "CONSTRUCTION"."AC_COST_CODES" ("COST_CODE") ENABLE
) ;



CREATE TABLE "CONSTRUCTION"."AC_CODE_MATERIAL_SUPPLIERS"
( "COST_CODE" VARCHAR2(4 BYTE) NOT NULL ENABLE,
"CATEGORY_CODE" NUMBER NOT NULL ENABLE,
"SUPPLIER_CODE" VARCHAR2(8 BYTE) NOT NULL ENABLE,
"MATERIAL_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"CREATED_BY" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"UPDATED_BY" VARCHAR2(50 BYTE),
"UPDATED_DATE" DATE,
PRIMARY KEY ("COST_CODE", "CATEGORY_CODE", "SUPPLIER_CODE", "MATERIAL_CODE")

CONSTRAINT "AC_COSTMATSUP_MAT_FK" FOREIGN KEY ("MATERIAL_CODE")
REFERENCES "CONSTRUCTION"."AC_MATERIAL_CODES" ("MATERIAL_CODE") ENABLE,
CONSTRAINT "AC_COSTMATSUP_COST_FK" FOREIGN KEY ("COST_CODE")
REFERENCES "CONSTRUCTION"."AC_COST_CODES" ("COST_CODE") ENABLE
) ;




CREATE TABLE "CONSTRUCTION"."AC_COST_CODES"
( "COST_CODE" VARCHAR2(4 BYTE) NOT NULL ENABLE,
"CODE_DESCRIPTION" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"CODE_TYPE" VARCHAR2(1 BYTE),
"NOMINAL_CODE" VARCHAR2(8 BYTE),
"CREATED_BY" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"UPDATED_BY" VARCHAR2(50 BYTE),
"UPDATED_DATE" DATE,
"ACTIVE" VARCHAR2(1 BYTE),
CONSTRAINT "AC_COST_CODES_PK" PRIMARY KEY ("COST_CODE")

;

Report message to a moderator
Send a private message to this user
Re: Get last record from another table [message #670628 is a reply to message #670627] Tue, 17 July 2018 03:04 Go to previous messageGo to next message
cookiemonster
Messages: 13167
Registered: September 2008
Location: Rainy Manchester
Senior Member
add to buddy list
ignore all messages by this user
You've ignored half of Michel's post.
[Updated on: Tue, 17 July 2018 03:04]Report message to a moderator
Send a private message to this user
Re: Get last record from another table [message #670629 is a reply to message #670628] Tue, 17 July 2018 03:11 Go to previous messageGo to next message
ritaman
Messages: 6
Registered: September 2011
Location: Ireland
Junior Member
add to buddy list
ignore all messages by this user
Sorry - I was working on the sql to populate the tables but I think it might be better just to send a spreadsheet.....

I have uploaded an Excel Spreadsheets which show the tables and a little data which I hope explains what I am looking for.
I've simplified the tables.
Sorry if it's not too technical.
Thanks in advance
[Updated on: Tue, 17 July 2018 04:24]Report message to a moderator
Send a private message to this user
Re: Get last record from another table [message #670633 is a reply to message #670629] Tue, 17 July 2018 04:36 Go to previous message
cookiemonster
Messages: 13167
Registered: September 2008
Location: Rainy Manchester
Senior Member
add to buddy list
ignore all messages by this user
You haven't uploaded a spreadsheet.
A lot of people won't download a spreadsheet.
Inserts are far better, we can run them into our own DBs and try queries out.

There's an special version of the aggregate max function that lets you get the value of one column that is the on the same row as the max of something else. Which is probably what you need.
You can see an example here:
It's the last couple of examples.

If that doesn't work for you post the inserts here, along with what you tried and we'll see what we can do.
Re: Get last record from another table [message #670669 is a reply to message #670637] Wed, 18 July 2018 02:54 Go to previous messageGo to next message
ritaman
Messages: 10
Registered: September 2011
Location: Ireland
Junior Member
Hi again,
Sorry for making things so long winded in my earlier posts!

I've simplified this as much as I can and hopefully this will explain a little better


I have 2 tables;
AC_INVOICE_DETAILS
AC_COST_CODES

The SQL below correctly populates only one AC_INVOICE_DETAILS (the last Date) record for each MATERIAL_CODE (which is what I want)

select INV.MATERIAL_CODE, INV.CREATED_DATE, INV.Cost_Code
from
(
  select MATERIAL_CODE, CREATED_DATE, Cost_Code,
    rank() over(partition by Material_Code
                        order by CREATED_DATE desc) seq
  from AC_INVOICE_DETAILS
) INV
where seq = 1;


I now want to populate a field (Nominal_Code) from the other table AC_COST_CODE where
AC_INVOICE_DETAILS.COST_CODE = AC_COST_CODE.COST_CODE
Any ideas' how I can incorporate this into my query?


I've tried this but it won't work;
select INV.MATERIAL_CODE, INV.CREATED_DATE, INV.Cost_Code, AC_COST_CODES.Nominal_Code
from
(
(
  select MATERIAL_CODE, CREATED_DATE, Cost_Code,
    rank() over(partition by Material_Code
                        order by CREATED_DATE desc) seq
  from AC_INVOICE_DETAILS
) INV
where seq = 1)
INNER JOIN AC_COST_CODES ON inv.COST_CODE = AC_COST_CODES.COST_CODE;


Your invaluable help is much appreciated.
Re: Get last record from another table [message #670670 is a reply to message #670669] Wed, 18 July 2018 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 16 July 2018 19:28

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Re: Get last record from another table [message #670671 is a reply to message #670669] Wed, 18 July 2018 03:37 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
ritaman wrote on Wed, 18 July 2018 08:54


I've tried this but it won't work;
Won't work can mean all sorts of things. We have your tables now, but we don't have the data - we can't run your code and see what it is doing.

As Michel said - we need inserts for sample data and we also need the expected output.
Re: Get last record from another table [message #670672 is a reply to message #670671] Wed, 18 July 2018 04:20 Go to previous messageGo to next message
ritaman
Messages: 10
Registered: September 2011
Location: Ireland
Junior Member
Thank you for your patience with me!!

Here are my tables and insert statements.
CREATE TABLE "CONSTRUCTION"."AC_COST_CODES" 
   (	"COST_CODE" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"NOMINAL_CODE" VARCHAR2(8 BYTE), 
	 CONSTRAINT "AC_COST_CODES_PK" PRIMARY KEY ("COST_CODE")

  ;


CREATE TABLE "CONSTRUCTION"."AC_INVOICE_DETAILS" 
   (	"INVOICE_DETAIL_NO" NUMBER NOT NULL ENABLE, 
	"COST_CODE" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"MATERIAL_CODE" VARCHAR2(10 BYTE), 
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	CONSTRAINT "AC_INVOICE_DETAILS" PRIMARY KEY ("INVOICE_DETAIL_NO")
	 CONSTRAINT "AINVD_CC_FK" FOREIGN KEY ("COST_CODE")
		 
   ) ;




INSERT INTO AC_COST_CODES (COST_CODE, NOMINAL_CODE)
VALUES ('MB01', '1250'),
VALUES ('MC01', '1850'),
VALUES ('Md01', '1950'),
;

INSERT INTO AC_INVOICE_DETAILS (INVOICE_DETAIL_NO, COST_CODE, MATERIAL_CODE, CREATED_DATE)
VALUES ('1', 'MB01', 'MATERIAL-1','21-JAN-2018' ),
VALUES ('2', 'MC01', 'MATERIAL-1','22-JAN-2018' ),
VALUES ('3', 'MB01', 'MATERIAL-1','23-JAN-2018' ),
VALUES ('4', 'MC01', 'MATERIAL-1','24-JAN-2018' ),
VALUES ('5', 'MB01', 'MATERIAL-2','25-JAN-2018' ),
VALUES ('6', 'MC01', 'MATERIAL-2','26-JAN-2018' ),
VALUES ('7', 'MB01', 'MATERIAL-3','27-JAN-2018' ),
VALUES ('8', 'MC01', 'MATERIAL-3','28-JAN-2018' ),
VALUES ('9', 'MD01', 'MATERIAL-3','29-JAN-2018' ),
;





Results Required:
Material_Code Cost_Code Nominal_Code Updated_Date
MATERIAL 1 MC01 1250 24-Jan-18 As Cost Code MC01 was the latest date for this Material
MATERIAL 2 MC01 1250 26-Jan-18 As Cost Code MC01 was the latest date for this Material
MATERIAL 3 MD01 1260 29-Jan-18 As Cost Code MD01 was the latest date for this Material


Re: Get last record from another table [message #670673 is a reply to message #670672] Wed, 18 July 2018 04:31 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
It helps if you test these things before you post them.
1) 1st create is missing a )
2) 2nd create is missing comma
3) 2nd create has an incomplete foreign key declaration
4) We don't have your schemas so please leave the schema name off
5) When inserting dates you should always wrap them in to_date calls with the appropriate format mask - our systems aren't all set to the same date format as you.
6) Also - use numeric months - some us don't have our DBs set to English.
7) you've mixed the case on Md01 in ac_cost_codes
Re: Get last record from another table [message #670674 is a reply to message #670673] Wed, 18 July 2018 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
8) you can't write insert statments like that - a given insert can only have one values clause.

[Updated on: Wed, 18 July 2018 04:33]

Report message to a moderator

Re: Get last record from another table [message #670675 is a reply to message #670674] Wed, 18 July 2018 04:53 Go to previous messageGo to next message
ritaman
Messages: 10
Registered: September 2011
Location: Ireland
Junior Member
Apologies again, I don't have the means of testing my code as I only have the production DB to work with;

Hope this is better;

CREATE TABLE "AC_COST_CODES" 
   (	"COST_CODE" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"NOMINAL_CODE" VARCHAR2(8 BYTE), 
	 CONSTRAINT "AC_COST_CODES_PK" PRIMARY KEY ("COST_CODE")
)
  ;


CREATE TABLE "AC_INVOICE_DETAILS" 
   (	"INVOICE_DETAIL_NO" NUMBER NOT NULL ENABLE, 
	"COST_CODE" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"MATERIAL_CODE" VARCHAR2(10 BYTE), 
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	CONSTRAINT "AC_INVOICE_DETAILS" PRIMARY KEY ("INVOICE_DETAIL_NO")
	 		 
   ) ;




INSERT INTO AC_COST_CODES VALUES ('MB01', '1250');
INSERT INTO AC_COST_CODES VALUES ('MC01', '1850');
INSERT INTO AC_COST_CODES VALUES ('MD01', '1950');



INSERT INTO AC_INVOICE_DETAILS VALUES ('1', 'MB01', 'MATERIAL-1', (TO_DATE('2018/01/22 ', 'yyyy/mm/dd '));
INSERT INTO AC_INVOICE_DETAILS VALUES ('2', 'MC01', 'MATERIAL-1', (TO_DATE('2018/01/22 ', 'yyyy/mm/dd '));
INSERT INTO AC_INVOICE_DETAILS VALUES ('3', 'MB01', 'MATERIAL-1', (TO_DATE('2018/01/23 ', 'yyyy/mm/dd '));
INSERT INTO AC_INVOICE_DETAILS VALUES ('4', 'MC01', 'MATERIAL-1', (TO_DATE('2018/01/24 ', 'yyyy/mm/dd '));
INSERT INTO AC_INVOICE_DETAILS VALUES ('5', 'MB01', 'MATERIAL-2', (TO_DATE('2018/01/25 ', 'yyyy/mm/dd '));
INSERT INTO AC_INVOICE_DETAILS VALUES ('6', 'MC01', 'MATERIAL-2', (TO_DATE('2018/01/26 ', 'yyyy/mm/dd '));
INSERT INTO AC_INVOICE_DETAILS VALUES ('7', 'MB01', 'MATERIAL-3', (TO_DATE('2018/01/27 ', 'yyyy/mm/dd '));
INSERT INTO AC_INVOICE_DETAILS VALUES ('8', 'MC01', 'MATERIAL-3', (TO_DATE('2018/01/28 ', 'yyyy/mm/dd '));
INSERT INTO AC_INVOICE_DETAILS VALUES ('9', 'MD01', 'MATERIAL-3', (TO_DATE('2018/01/29 ', 'yyyy/mm/dd '));


[Updated on: Wed, 18 July 2018 05:12]

Report message to a moderator

Re: Get last record from another table [message #670676 is a reply to message #670675] Wed, 18 July 2018 05:11 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you don't have a test DB you should get one before you do anything else. You can download oracle XE for free and install it on your PC. You can download an oracle virtual box and install that.
You should never just go and try things on prod.

Your inserts for ac_invoice_details had mismatched brackets. I fixed that and gave your query a try.
It doesn't work because you've got a nested select that isn't a select. Formatted:
select INV.MATERIAL_CODE, INV.CREATED_DATE, INV.Cost_Code, AC_COST_CODES.Nominal_Code
FROM ((select MATERIAL_CODE, CREATED_DATE, Cost_Code,
              rank() over(partition by Material_Code
                          order by CREATED_DATE desc) seq
       from AC_INVOICE_DETAILS
      ) INV
      where seq = 1
     )
INNER JOIN AC_COST_CODES ON inv.COST_CODE = AC_COST_CODES.COST_CODE;
That where clause doesn't belong to either of the two selects and so you get an error.
If you change it to this it works:
select INV.MATERIAL_CODE, INV.CREATED_DATE, INV.Cost_Code, AC_COST_CODES.Nominal_Code
FROM (select MATERIAL_CODE, CREATED_DATE, Cost_Code,
             rank() over(partition by Material_Code
             order by CREATED_DATE desc) seq
      from AC_INVOICE_DETAILS
     ) INV
INNER JOIN AC_COST_CODES ON inv.COST_CODE = AC_COST_CODES.COST_CODE
where seq = 1
It gives this:
MATERIAL_CODE CREATED_DATE COST_CODE NOMINAL_CODE
------------- ------------ --------- ------------
MATERIAL-1    24/01/2018   MC01      1850
MATERIAL-2    26/01/2018   MC01      1850
MATERIAL-3    29/01/2018   MD01      1950
Which differs from your stated expected result since the nominal_code for MC01 is 1850 not 1250. I assume this result is actually correct.

You can also write the query like this:
SELECT material_code, created_date, cost_code, nominal_code
FROM (SELECT acd.material_code, acd.created_date, acd.cost_code, acc.nominal_code,
             rank() OVER (PARTITION BY material_code
                          ORDER BY created_date DESC) seq
      FROM ac_invoice_details acd
      INNER JOIN ac_cost_codes acc ON acd.cost_code = acc.cost_code
     ) INV
WHERE seq = 1;
Re: Get last record from another table [message #670677 is a reply to message #670676] Wed, 18 July 2018 05:21 Go to previous messageGo to next message
ritaman
Messages: 10
Registered: September 2011
Location: Ireland
Junior Member
THANK YOU so much. That is Exactly what I want.
Again thank you for your patience.
As you have probably guessed, I do not code - I am just working on extracting data from an oracle database to SAP.
THANK You so much. You have been so helpful.
Re: Get last record from another table [message #670678 is a reply to message #670677] Wed, 18 July 2018 05:26 Go to previous message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should still have test instances to work with - oracle and SAP both.
Otherwise you're liable to end up with mangled data in SAP and non-performant queries being run on oracle chewing up all the resources and annoying the heck out the regular users.
Previous Topic: Need Help on Pl/Sql
Next Topic: ORA-04091: table Table_Name is mutating, trigger/function may not see it
Goto Forum:
  


Current Time: Fri Nov 15 14:30:13 CST 2019