topic Create View from three tables in Application Development and Automation Discussions

Create View from three tables

Former Member — Fri, 21 Jul 2006 15:07:15 GMT

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 ?

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 15:13:45 GMT

Give it as

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

It should work.

Cheers,

Thomas.

Please mark points if helpful.

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 15:18:22 GMT

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 ?

Re: Create View from three tables

sridhar_k1 — Fri, 21 Jul 2006 15:24:05 GMT

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

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 15:44:28 GMT

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

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 15:50:49 GMT

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???

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 16:10:10 GMT

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.

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 16:14:35 GMT

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)?

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 16:15:51 GMT

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

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 16:21:57 GMT

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

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 16:26:11 GMT

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;

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 16:27:37 GMT

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..??

Re: Create View from three tables

sridhar_k1 — Fri, 21 Jul 2006 16:33:33 GMT

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

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 16:33:52 GMT

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

Rgds,

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 16:36:15 GMT

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'.

Re: Create View from three tables

Former Member — Fri, 21 Jul 2006 18:43:24 GMT

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 ,