CODEWITHSUNDEEP
selectabapopensql
Nagendra Babu
Nagendra Babu

Reputation: 1

Need an efficient select query

I would like to know an efficient to way to fetch the data in the following case.

There are two tables say Table1 and Table2 having two common field say contry and pincode and other table "Table3" having key fields of first two tables (DNO, MPNO).

Here is the little glitch, In table3 data, if it is having DNO it wont have MPNO

So when in the selection screen(Pic no2) if the use enter any thing, result should be as follows

**MFID  |  DNO        |  MPNO     | COUNTRY | PINCODE**
----------
00001   |   10011     |  novalue  | IN      | 4444
00002   |   Novalue   |  1200     | IN      | 5555
00003   |   300       |  novalue  | US      | 9999

( as you can observe if DNO present no MPNO , vice versa )

Please have a look at the pictures for a clear picture :-)

Table Relation: Table Relation

Selection screen with select options:

Selection screen with select options

The code shouldn't be long.

Upvotes: 0

Views: 131

Answers (2)

Suncatcher
Suncatcher

Reputation: 10621

Building of efficient select will require information about obligatory fields in your selection screen, as well as about alleged production size of all 3 tables. However, without this information let's assume that table1 and table2 are reference tables and table3 is a transaction table, as onr can assume from their structure. It would be sensible to build selection in a following way:

  1. Selecting data from reference tables. As you said fields DNO/MPNO are mutually exclusive then there will be no hits of country/pincode pair in both reference tables, so JOIN is useless here. However we can merge 2 result sets in single itab without any constraints' violations.

    TYPES: BEGIN OF tt_result,
         dno     TYPE table1-dno,
         mpno    TYPE table2-mpno,
         country TYPE table1-country,
         pincode TYPE table1-pincode,
        ...other field from table3
       END OF tt_result.

DATA: itab_result TYPE tt_result.

SELECT dno
  FROM table1
  INTO CORRESPONDING FIELDS OF TABLE itab_result
 WHERE pincode IN so_pincode
   AND country IN so_country.

SELECT mpno
  FROM table2
  APPENDING CORRESPONDING FIELDS OF TABLE itab_result
 WHERE pincode IN so_pincode
   AND country IN so_country.

  2. FOR ALL ENTRIES addition allows specifying the same table in FOR ALL ENTRIES clause and in INTO clause, so we can fill our result table with absent table3 data by DNO/MPNO key.

    SELECT *
  FROM table3
  INTO CORRESPONDING FIELDS OF TABLE itab_result
   FOR ALL ENTRIES IN itab_result
    ON itab_result~dno = itab3~dno
   AND itab_result_mpno = itab3~mpno.

Upvotes: 0

Arjun
Arjun

Reputation: 11

PSEUDO CODE:

Select queries:

  1. Select * from table3 into it_table3.

  2. Select * from table1 FOR ALL ENTRIES IN it_table3 INTO it_table1 WHERE dno = table3-dno.

  3. Select * from table2 FOR ALL ENTRIES IN it_table3 INTO it_table2 WHERE mpno = table3-mpno.

  4. Loop at internal table 3 and build final table. LOOP at it_table3 into wa_table3.

    IF wa_table3-dno IS NOT INITIAL.
READ it_table1 where dno = wa_table3-dno.
ELSE.
READ it_table2 where mpno = wa_table3-mpno.
ENDIF.

ENDLOOP.

Hope this was the answer you were hoping to find!

Upvotes: 1

Related Questions