Re querying a table using ONE Select

[ricky_shaw]

Hello Experts,

In one hit i want to query a Table2 2 times using oNE select query

Lets say, i have 2 database tables : Table1 & Table2.

& internal tables say itab1 & itab2.

Lets say they have fields: fld1 fld 2 fld3 fld4.

1) I used select * from Table1 into table itab1.

2) Now i want to get entries from table2 based on itab-fld1 and itab2-fld4

select * from Table2 into itab2
 FOR ALL ENTRIES in itab1
where fld1 = itab1-fld1
  and fld2 = *itab2-fld4*  (entries based on 
itab2)

Please suggest.,

Thanks

Ricky

[Former Member]

Hello,

I have not done before, but can sense it can be solved through sub query....

Please read documentation of SELECT...WHERE IN EXIST....

Thanks.

[Former Member]

Hi

I am not sure we can directly assign like this, But you can have one SELECT and get the solution.

I have never tried this, but have a look into this.

http://help.sap.com/saphelp_nw04/helpdata/EN/dc/dc7614099b11d295320000e8353423/content.htm

Shiva

[ricky_shaw]

Hi,

How can i use JOIN here? The main filter is in Table2 itself on fld4 .

Thanks

[Clemenss]

Hi Ricky,

this is a possible case for ALIAS, but you must get rid of *. SELECT * is translated by DB interface in to all available fields.

You can

SELECT table1~fld1 table2~fld2 table1~fld3 table2~fld4 table1~fld5 ...
  INTO CORRESPONDING FIELDS OF itab_x
  FROM table1
  JOIN table1 as table2
    ON table1~fld1 = table2~fld3 AND
       table1~fld2 = table2~fld4
   WHERE ...

Regards

Clemens

[ricky_shaw]

Hi Celemens,

i DONT see that you are making use of any field from itab_x.

Ok. just IGNORE table1.

Now please tell me how can i write aliasing?

SELECT table2_{fld2 table2}fld4

INTO CORRESPONDING FIELDS OF itab_x

FROM table2

WHERE

fld2 = itab_x-fld2..

Thanks

Ricky

[Clemenss]

Hi Ricky,

it is almost impossible to explain anything if you do not have an example of what you are going to do. When I say example, I don not mean Lets say, i have 2 database tables or Lets say they have fields but a case that everyone understands.

If you are talking about SAP tables, give the names and fields. If you are talking about user-defined tables, decribe the process, give table and field names and characteristics (data element, domain, check table, search help...) of each field.

For the question Now please tell me how can i write aliasing the answer is a clear F1.

Shortly: If you write <tabname> AS <alias> or <fieldname> AS <alias> this means that <tabname> and <fieldname> identifie table and field in the database and <alias> is used for it in the query clause and for the data transfer from database to INTO data object.

Note: It is always better to ask a question for a specific task. Getting the answer you can use it in all tasks that are comparable to the one you asked for. Asking a bla-bla question ( Lets say... ) will rarely get a useful answer.

Regards,

Clermens

[ricky_shaw]

Hi Clemens,

Ok..I thought of making my question in a more generalized way so i avoided specifying the tables.

Here you go.

1) Select vertrag vkonto into table it_ever

from EVER

where ainzdat in s_moveout.

if not it_ever[] is initial.

2) select vkont gpart into table it_fkkvkp

from FKKVKP for all entries in it_vkont

where vkont = it_ever-vkonto.

endif.

3) 1 vkont will have multiple vertrags in EVER : From above vkonts in it_fkkvkp, I need to hit EVER again as below which i DONT like.

if it_fkkvkp[] is initial.

select vertrag vkonto into table it_ever2

where vkonto = it_fkkvkp-vkont.

endif.

so i am thinking of some table aliaisng on it_fkkvkp.

[SureshRa]

Hi Ricky,

Please try co-related subquery as below. I've not checked the exact syntax, but I'm sure this approach would fulfill your requirement in one SQL statement:

SELECT vertrag vkonto 
INTO TABLE it_ever
FROM ever
WHERE ainzdat IN s_moveout
AND vkonto IN ( SELECT vkont
                FROM fkkvkp 
                WHERE vkont = ever~vkonto )

Here the sub-query need not have a work-area or target internal table but co-relates to the result of the parent SQL query - which exactly meets your required resultset in table IT_EVER.

Regards

Suresh