SMARTFORMS,REPORTS

[Former Member]

Hi all i have a couple of questions:

1) what is the difference in using JOINS and FOR ALL ENTRIES.Where to use them specifically?

2)How and where can i specify the no of copies to be output (suppose invoices ) in my print program.Is it in the FM SSF_FUNCTION_MODULE _NAME.

3)Wat all condituions r necessary for using For All Entries.I know two of them

a)the internal table should not be empty.

b)The matcinhg fields in the where clause should be of same type.

[Former Member]

Hi Abhi,

Firstly avoid using join statement.

It may seem simple while you are joining two tables but when it comes to joining multiple tables it becomes an uphill task.

Use the following alternaties.

They are simple.

To select single entries use this.

Select single field1 field2 fieldn

from table

into x_tab

where tab_field = table_field.

To select multiple entries use this.

Select field1 field2 fieldn

from table

into it_tab

for all entries in table

where tab_field = table_field.

In for all entries clause it takes into account all the lines of code that exists in the internal table and checks for all the entries available. The where condition is checked for the matching fields and to fetch the data as per the two tables common fields.

This should solve all your joining problems with select queries.

Reward Points if useful.

Thanks,

Tej..

[Former Member]

Hi Abhi,

1) what is the difference in using JOINS and FOR ALL ENTRIES.Where to use them specifically?

For all entries

The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus

Large amount of data

Mixing processing and reading of data

Fast internal reprocessing of data

Fast

The Minus

Difficult to program/understand

Memory could be critical (use FREE or PACKAGE size)

Some steps that might make FOR ALL ENTRIES more efficient:

Removing duplicates from the the driver table

Sorting the driver table

If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:

FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.

Select using JOINS

The plus

Very large amount of data

Similar to Nested selects - when the accesses are planned by the programmer

In some cases the fastest

Not so memory critical

The minus

Very difficult to program/understand

Mixing processing and reading of data not possible.

Thanks,

Reward If Helpful.

[Former Member]

Hi Abhi,

If you want to fetch data by combining more than 2 tables we use joins.

If you want to fetch data from a table based on a selection made we go for for all entries .

FOR ALL ENTRIES is an effective way of doing away with using JOIN on two tables.

You can check the below code -

SELECT BUKRS BELNR GJAHR AUGDT

FROM BSEG

INTO TABLE I_BSEG

WHERE BUKRS = ....

SELECT BUKRS BELNR BLART BLDAT

FROM BKPF

INTO TABLE I_BKPF

FOR ALL ENTRIES IN I_BSEG

WHERE BUKRS = I_BSEG-BUKRS

AND BELNR = I_BSEG-BELNR

AND BLDAT IN SO_BLDAT.

*******************************8

look another example

what is the use of FOR ALL ENTRIES

1. INNER JOIN

DBTAB1 <----

---

> DBTAB2

It is used to JOIN two DATABASE tables

having some COMMON fields.

2. Whereas

For All Entries,

DBTAB1 <----

---

> ITAB1

is not at all related to two DATABASE tables.

It is related to INTERNAL table.

3. If we want to fetch data

from some DBTABLE1

but we want to fetch

for only some records

which are contained in some internal table,

then we use for alll entries.

*----

---

1. simple example of for all entries.

2. NOTE THAT

In for all entries,

it is NOT necessary to use TWO DBTABLES.

(as against JOIN)

3. use this program (just copy paste)

it will fetch data

from T001

FOR ONLY TWO COMPANIES (as mentioned in itab)

4

REPORT abc.

DATA : BEGIN OF itab OCCURS 0,

bukrs LIKE t001-bukrs,

END OF itab.

DATA : t001 LIKE TABLE OF t001 WITH HEADER LINE.

*----

---

itab-bukrs = '1000'.

APPEND itab.

itab-bukrs = '1100'.

APPEND itab.

*----

---

SELECT * FROM t001

INTO TABLE t001

FOR ALL ENTRIES IN itab

WHERE bukrs = itab-bukrs.

*----

---

LOOP AT t001.

WRITE 😕 t001-bukrs.

ENDLOOP.

*****************inner join**********

table emp

empno name

a sasi

b xxx

c yyy

table sal

empno salary

a 1000

b 2000

Inner join

****************

select e_{empno e}name

s~sal

into table int_table

from emp as e

inner join sal

on

e_{empno = s}empno.

if you made inner join between table a and b by emp no

the selection retrives only if the condition satisfy the output will be

a sasi 1000

b xxx 2000

Outer join

*************************

select e_{empno e}name

s~sal into table int_table

from emp as e

LEFT OUTER JOIN sal

on

e_{empno = s}empno.

if you made outer join (left /right ) the left table kept as it is the

if the condition satisfy the right table entries will fetch else leave it blank

the output will be

a sasi a 1000

b xxx b 2000

c yyy

Hope this helps!

[Former Member]

Inner Join :- to retrive the data from 2 or more database table into ur 1 internal table.

ex:

SELECT SINGLE A_{LFBNR B}BLDAT INTO (IT_DATA-GRNO,IT_DATA-GRDATE) FROM

RSEG AS A INNER JOIN MKPF AS B ON A_{LFBNR = B}MBLNR " AND A_{GJAHR = B}MJAHR

WHERE A~BELNR = IT_DATA-BELNR

AND A~GJAHR = IT_DATA-GJAHR.

For all entries :- Fetching the data from database as per the entries already exist in another internal table.

ex:

select matnr maktx

into corresponding fields of table it_makt

from makt

for all entries in it_item

where matnr = it_item-matnr

and spras = 'EN'.

2. That depends on the transcation for ex me23n for Purchase order print out

In ME22n it will set by functional consultant.

3. that depends on ur requirement.

regards

prabhu