View on two tables

[Former Member]

Hello experts,

I would like to create a view based on a join between two tables.

I would like to take for a Date field, only the most recent date found in the data.

How can I perform it.

Thanks.

Amine

[Former Member]

Hello experts,

I would like to create a view based on a join between two tables.

I would like to take for a Date field, only the most recent date found in the data.

How can I perform it.

Thanks.

Amine

[Amarpreet]

The ans to this thread might help you :

http://scn.sap.com/thread/2057528

Or check this document out :

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/10a89c00-7dd7-2d10-6f83-cd24ee6d5...

hope this helps

Good luck

[former_member212705]

Hi amine,

You can create database view that use the inner join. You can create database view from SE11->View->Create->database view and then provide tables in table tab and there you can give join condition between table. IN view field tab you can provide fields that you want.

I hope this will help in case of any doubt reply back.

Thanks,

Ashish Trivedi

[Former Member]

Hi Ashish and Amarpreet,

Thanks for both of you for your answers.

But what I need to know is how to select the most recent date in my records while creating my view. I give you an example:

Let's say that my view contains 3 records with the same keys:

Reference	Client	material	date	quantity
1	ABC	MaterialA	01.01.2012	3
1	ABC	MaterialA	02.01.2012	4
1	ABC	MaterialA	03.01.2012	1

So as you can see. The most recent date is 03.01.2012 (03 Jan 2012)

Is only this record that i need to take in account in my view.

Thanks for your help.

Amine

[former_member212705]

Hi Amine,

As for as i think this would not be possible at the time of defining view as in the selection criteria tab we cant put such condition. So it would be better to apply a condition while selectiing data from view in your query, for example:

Select material quantity from view where (condition to select latest date).

However you can give some filtering criteria on selection tab of DB view:

table	field	operator	quantity
Tab A	DATE	>= or <= or = or any operator	here you can enter constant for compare, means any date; here if we provide text literal it gives error.

Regards,

Ashish