Joins

[Former Member]

Hi Experts,

I'm trying to retreive the data from the tables with the fields TADIR(name,srcsystem,author),TRDIR(name,subc),TRDIRT(name,text) . For this i used join condition but here in trdir name is not a primary key so i'm getting error.

So how should i retrieve the data from these tables using joins.

Thanks in Advance,

Aadi.

Title edited by: Alvaro Tejada Galindo on May 23, 2008 6:39 PM

[Former Member]

Hi,

Do inner join using the following fields :

TADIR - OBJ_NAME

TRDIR - NAME

TRDIRT - NAME

Regards,

Navneeth K.

[Former Member]

Hi aditya,

once try with this code

tables : tadir, trdirt .

data: begin of itab occurs 0,

obj_name like tadir-OBJ_NAME,

SUBC like trdir-subc,

SPRSL like trdirt-sprsl,

end of itab.

select tadir_{obj_name trdir}subc trdirt~sprsl

from tadir

inner join trdir on trdir_{NAME = tadir}obj_name

inner join trdirt on trdirt_{name = trdir}NAME

into table itab

up to 10 rows.

loop at itab.

write 😕 itab-obj_name,

itab-subc,

itab-sprsl.

endloop.

Thanks & Regards,

S.Gangi reddy

[Former Member]

Hi,

The code i was trying

SELECT name subc author srcsystem devclass FROM

trdir INNER JOIN tadir ON tadir_{obj_name = trdir}name

inner join trdirt on trdirt_{name = trdir}NAME

INTO TABLE int_cust_program WHERE name IN s_prog AND

object = 'PROG' AND

author IN s_author AND

srcsystem IN s_srcsys AND

devclass IN s_packag AND

( subc = '1' OR subc = 'M' OR subc = 'F' OR subc = 'S' OR subc = 'J'

or subc = 'K' OR subc = 'T' OR subc = 'X' or subc = 'I') "Changed by Aditya

AND

(

name LIKE 'Y%' OR

name LIKE 'Z%' OR

name LIKE 'LY%' OR

name LIKE 'LZ%'

).

[Former Member]

Hi,

Sorry this is was the code.

SELECT name text subc author srcsystem devclass FROM

trdir INNER JOIN tadir ON tadir_{obj_name = trdir}name

inner join trdirt on trdirt_{name = trdir}NAME

INTO TABLE int_cust_program WHERE name IN s_prog AND

object = 'PROG' AND

author IN s_author AND

srcsystem IN s_srcsys AND

devclass IN s_packag AND

( subc = '1' OR subc = 'M' OR subc = 'F' OR subc = 'S' OR subc = 'J'

or subc = 'K' OR subc = 'T' OR subc = 'X' or subc = 'I') "Changed by Aditya

AND

(

name LIKE 'Y%' OR

name LIKE 'Z%' OR

name LIKE 'LY%' OR

name LIKE 'LZ%'

).

[Former Member]

Hi,

By using this i'm getting error as "The Name has two meanings.

Please can any one solve my problem.

[Former Member]

Hi Aditya,

select table1_{column1 table1}column2

from table1

inner join table2 on table2_{column1 = table1}column1

where table1~column1 = 'abcd'.

You are getting errors because the 2 tables the same column name.

Hope this helps.

Benedict

[Former Member]

Hi,

Thanks for help but how should i join all the 3 tables.

You are showing with 2 tables but is that possible to join these 3 tables.

Please let me know as soon as possible.

Thanks in Advance,

Aadi.

[Former Member]

Hi,

You can join

TADIR-OBJ_NAME with

TRDIR-NAME with

TRDIRT-NAME

all of them has language fields too...

For the table TADIR, i think the PGMID is always "R3TR"

OBJECT is "PROG"

Hope this helps.

Benedict

[Former Member]

Hi,

Please refer this join statement.It was giving an error as their are 2 name statement used. Please help me as soon as possible.

SELECT name text subc author srcsystem devclass FROM

trdir INNER JOIN tadir ON tadir_{obj_name = trdir}name

inner join trdirt on trdirt_{name = trdir}NAME

INTO TABLE int_cust_program WHERE name IN s_prog AND

object = 'PROG' AND

author IN s_author AND

srcsystem IN s_srcsys AND

devclass IN s_packag AND

( subc = '1' OR subc = 'M' OR subc = 'F' OR subc = 'S' OR subc = 'J'

or subc = 'K' OR subc = 'T' OR subc = 'X' or subc = 'I') "Changed by Aditya

AND

(

name LIKE 'Y%' OR

name LIKE 'Z%' OR

name LIKE 'LY%' OR

name LIKE 'LZ%'

).

[Former Member]

Hi,

Refer to my first post

Select table1~name table2~object

from....

That should solve your problem...

Benedict

[Former Member]

This is an example code of joining 3 table.

Follow the same for your tables.

DATA: BEGIN OF wa,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

fldate TYPE sflight-fldate,

bookid TYPE sbook-bookid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY carrid connid fldate bookid.

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

LOOP AT itab INTO wa.

AT NEW fldate.

WRITE: / wa-carrid, wa-connid, wa-fldate.

ENDAT.

WRITE / wa-bookid.

ENDLOOP.

NOTE: All the key fields should be used to fetch right data.

[Former Member]

Hi,

Thanks for your kind help but i my case trdir table with name field is not a key field so its giving me an error. In the table tadir, trdirt name is the key field.

I understand what you are trying to say but is it possible in my case with 3 tables having the same field (NAME) but in only 2 tables the field is declared as key field and in another tables it is not a key field

So how should i declare the join statement .

Please let me know the example using my code which i have posted.

Thanks in Advance,

Aadi.

[Former Member]

TRDIR is a "Database view", its not an Database table.

Where as TRDIRT is a Table.

Join both tables on NAME field i.e, on a_{NAME = b}NAME, its enough.

Follow the above inner join post which i have sent.

[Former Member]

Hi Adithya,

Fine but i require the fields from 3 tables like

TADIR--Name, srcsystem, Author.

TRDIR---Name, Subc,

TRDIRT---Name, Text(Short description)

Actually i developed using tadir and trdir using the above fields.

But now i want to add the Text( short description) of every object at output.

Actually the code was like this. I just want to add one more field i.e., TEXT which is present in TRDIRT.

So how should i write the code.

SELECT name subc author srcsystem devclass FROM

trdir INNER JOIN tadir ON tadir_{obj_name = trdir}name

INTO TABLE int_cust_program WHERE obj_name IN s_prog AND

object = 'PROG' AND

author IN s_author AND

srcsystem IN s_srcsys AND

devclass IN s_packag AND

( subc = '1' OR subc = 'M' OR subc = 'F' OR subc = 'S' OR subc = 'J'

or subc = 'K' OR subc = 'T' OR subc = 'X' or subc = 'I') "Changed by Aditya

AND

(

name LIKE 'Y%' OR

name LIKE 'Z%' OR

name LIKE 'LY%' OR

name LIKE 'LZ%'

).

[Former Member]

select a_{name a}text b_{srcsystem b}author c~subc

INTO CORRESPONDING FIELDS OF TABLE int_cust_program

FROM ( ( trdirt AS a

INNER JOIN tadir AS b ON a_{name = b}obj_name AND

a_{sprsl = b}masterlang )

INNER JOIN trdir AS c ON a_{name = c}name )

WHERE b~obj_name IN s_prog

AND b~object = 'PROG'

AND b~author IN s_author

AND b~srcsystem IN s_srcsys

AND b~devclass IN s_packag

AND ( c_{subc = '1' OR c}subc = 'M' OR c_{subc = 'F' OR c}subc = 'S' OR c_{subc = 'J' or c}subc = 'K' OR c_{subc = 'T' OR c}subc = 'X' or c~subc = 'I')

AND (

a~name LIKE 'Y%' OR

a~name LIKE 'Z%' OR

a~name LIKE 'LY%' OR

a~name LIKE 'LZ%'

).

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on May 23, 2008 6:39 PM

[Former Member]

Hi,

Thanks for your help but it is not fetching all the fields data, only few fields are getting fetched.

please can you look at the code once and let me know as soon as possible.

Thanks in Advance,

Aadi.