topic Re: Join condition in Application Development and Automation Discussions

Join condition

Former Member — Tue, 07 Jun 2005 21:23:00 GMT

Hi Gurus,

I have a database table dbstatora with fields

TNAME --> Table Name

ANDAT --> Analyzing Date

AMETH --> Analysis method for collecting Statistics

NROWS --> No of rows in a table

OCCBL --> Used blk(size) of table in KB

EMPBL --> Empty blk(size) of table in KB

AFREE --> Avg Freespace in a used db block

INDBS --> Used Blk(size) of an index kb

And another table RSTSODS

The fields of this table is

ODSNAME

VERSION

ODSNAME_TECH

etc

I want to join these two tables and display the result with these fields,

ODSNAME, TNAME(/BIC/A%, /BI0/A% ,/BIC/B%, /BI0/B%) and all the fields of DBSTATTORA,

The common field for these two TABLES is

TNAME in DBSTATTORA = /BIC/B% OR /BI0/B%

ODSNAME_TECH in RSTSODS = /BIC/B% /BI0/B%

I tried inner join it doesn't work, which join should i use to display my result as specified above.

Any help will be of great help.

Thanks,

Prab

Re: Join condition

ssimsekler — Wed, 08 Jun 2005 00:11:37 GMT

Hi Prabhakaran

It depends on your requirement whihjoin type you should use. However, I guess you require LEFT OUTER JOIN where DBSTATORA is the left side table.

For more information on JOIN, you can visit <a href="http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm">here</a>.

Regards

*--Serdar <a href="https://www.sdn.sap.com:443http://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.sdn.businesscard.sdnbusinesscard?u=qbk%2bsag%2bjiw%3d">[ BC ]</a>

Re: Join condition

Former Member — Wed, 08 Jun 2005 00:35:42 GMT

Hi Serder,

Thanks it really helped me, but still i am not able to display the results as how i wanted.

Regards,

Prabhu.

Re: Join condition

Former Member — Wed, 08 Jun 2005 06:05:47 GMT

Hi,

Try selecting the records using 'For all entries' of first table with the common fields in the where condition, this might work.

Rgds,

Re: Join condition

ssimsekler — Wed, 08 Jun 2005 08:22:04 GMT

Hi Prabhakaran

I did not try at the system but you can try:

SELECT a~tname a~andat a~ameth
       a~nrows a~occbl a~empbl
       a~afree a~indbs b~odsname
       FROM dbstatora AS a
       LEFT OUTER JOIN rstsods
         ON a~tname = b~odsname_tech
       INTO TABLE gt_itab .

If this does not give the output you require, are you sure about the result you expect can be gathered via the join condition?

Regards

*--Serdar <a href="https://www.sdn.sap.com:443http://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.sdn.businesscard.sdnbusinesscard?u=qbk%2bsag%2bjiw%3d">[ BC ]</a>

Re: Join condition

Former Member — Wed, 08 Jun 2005 18:37:58 GMT

Hi,

I tried the way u sent but it displays all the tables names of ods i.e only the change log tables but i also want to display the tables names of active data and new data tables of ods. So what i did is i created a view based on DBSTATTORA and RSTSODS. This view displays all the change log table names of ods for that particular ods. Now i am using this view and the table DBSTATTORA joining it so that i can display all the tables of ods object. In the join condition if i do left outer join of

SELECT D_{TNAME D}ANDAT D_{NROWS D}OCCBL D_{EMPBL D}AFREE D~INDBS

V~ODSNAME UP TO 30 ROWS

INTO (WD-TNAME, WD-ANDAT, WD-NROWS, WD-OCCBL, WD-EMPBL, WD-AFREE,

WD-INDBS, WV-ODSNAME)

FROM DBSTATTORA AS D LEFT JOIN ZOBTODS_VW AS V

ON D_{TNAME = V}TNAME

WHERE D~NROWS > 50000

AND D_{TNAME LIKE '/BIC/A%' OR D}TNAME LIKE '/BI0/A%' OR

D_{TNAME LIKE '/BIC/B%' OR D}TNAME LIKE '/BI0/B%'.

This displays only the /bic/A* and /bio/a* tables,

I think by seeing this code u would have got it. Any suggestions or help will be of great help.

Thanks,

Prabhu