Create View from three tables

[Former Member]

I have three tables :

KONDD

KOTD001

KOTD002

I want to create a view that : gets all the records from the KONDD for both the tables :

All the records for KOTD001-knumh = kondd-knumh

All the records for kotd002-knumh = kondd-knumh .

Is this possible ? Is there any other way out ?

because when i created view and gave the join condition :

KOTD001-knumh = kondd-knumh it worked good and got all the records from kondd for kotd001 . Then I added one more row in join cond for : kotd002-knumh = kondd-knumh ..it dint work !!

I want to know if there is any possibility of specifying an 'OR' condition while fetching the data ?

[Former Member]

I have three tables :

KONDD

KOTD001

KOTD002

I want to create a view that : gets all the records from the KONDD for both the tables :

All the records for KOTD001-knumh = kondd-knumh

All the records for kotd002-knumh = kondd-knumh .

Is this possible ? Is there any other way out ?

because when i created view and gave the join condition :

KOTD001-knumh = kondd-knumh it worked good and got all the records from kondd for kotd001 . Then I added one more row in join cond for : kotd002-knumh = kondd-knumh ..it dint work !!

I want to know if there is any possibility of specifying an 'OR' condition while fetching the data ?

[Former Member]

Give it as

kondd-knumh  = KOTD001-knumh
kondd-knumh  = kotd002-knumh

It should work.

Cheers,

Thomas.

Please mark points if helpful.

[Former Member]

When i do this :

It says no table entry found .

Only with one condition , it gets the record ..but for this it looks like it does an AND...?

any way out ?

[sridhar_k1]

Database view implement Inner join, so if you create a view like you've mentioned you don't get any records because If KNUMH exist in one table it doesn't exist in other condition table.

You have to write a outer join select statement in the abap program.

Regards

Sridhar

[Former Member]

I created a veiw with sap tables successfully. Can you check whether you have given

View field
Table name
Table field

under view field.

These are the fields which you are going to display.

Rgds,

Thomas.

Message was edited by: Thomas Mann

[Former Member]

Yes , i have given the same ....it worked well for one join condition and got the records for kotd001-knumh = kondd-knumh...!!

could you create a view that is getting all the records from kondd for all in KOTD001 and KOTD002???

[Former Member]

Yes dude

it will work fine for these 3 tables.

1) Give 3 table names

2) conditions as

kondd-knumh = KOTD001-knumh

kondd-knumh = kotd002-knumh

3) Fields which you want to display. (select all the key fields in all the tables)

View field

Table name

Table field

under view fields.

4) Manually go and check there is a common knumh in all the 3 tables. If so it will display.

Cheers,

Thomas.

[Former Member]

Hi Thomas ,

Thanks much ...:) but , the problem is that :

the knumh is not common (value wise ) in the three...

KONDD has all the records ( base table )

and kotd001 and kotd002 have diff set of knumhs in them .

I wanted to put a condition which would fetch all the records frrom KONDD if the knodd-knumh matched with any of the knumh ( knotd001 or kotd002)?

[Former Member]

Sridhar,

thanks I am looking for the outer join thing....i cant find how to write the select statement for outer join on multiple(three) tables ?

Message was edited by: SAP BEE

[Former Member]

for outer joins you can write it as

SELECT a.x a.y b.z

from abcd as a

outer join xyz as b on a.x = b.z;

[Former Member]

You might need to use outer joins since if one of the table does not have the corresponding entry it would result in no records. I feel it is precisely the reason for your issue.

Regards

Anurag

[Former Member]

SELECT c~SMATN

INTO CORRESPONDING FIELDS OF TABLE itab

FROM KONDD AS c

left outer JOIN KOTD001 AS P ON p_{KNUMH = C}KNUMH

left outer join kotd002 as z on z_{knumh = c}knumh.

Now, whether i give the second join expression or not ...It already fetches all the records from table kondd ...its almost like doing a select w/o any condition on kondd..??

[sridhar_k1]

Right, outer join fetches all records from KONDD, and you need to delete duplicate records later from itab, best way is to write two seperate inner join select statements and append the resulting internal tables.

Regards

Sridhar

[Former Member]

Outer join will select all the records from all the condition with KNUMH value.

Rgds,

TM

[Former Member]

Sorry just a typo error in the previous reply..

Attached is an example for left outer join.

select A~VBELN

B~POSNR

A~VSTEL

A~VKORG

B~VTWEG

A~KUNNR

A~LFART

A~VBTYP

A~LIFEX

B~MATNR

B~LFIMG

B~ARKTX

A~ROUTE

from LIKP as A left outer join LIPS as B

on A_{VBELN = B}VBELN

into LIKP

where A~LIFEX in SO_LIFEX

and A~KUNNR in SO_KUNNR

and A~VBELN in SO_VBELN

and A~VBTYP eq 'J'.

[Former Member]

Hi everyone ...thankx for all the replies. I m assigning the points and closing the thread :

Thanks Sridhar ...I finally created a report program ur way ...!!

Thanks ,