topic Appending in select statement in Application Development and Automation Discussions

Appending in select statement

Former Member — Tue, 08 Jul 2008 06:30:29 GMT

Hi all,

could anyone tell me the following select how it will work?

I have never seen this type of select.

I could not understand the purpose of appending and where exists?

select vbeln

posnr

srcloc

appending table it_tab

from ztab

where exists ( select *

from vbepwhere vbeln eq ztab~vbeln

and posnr eq zstab~posnr.

Kindly let me know if anyone knows the answer.

Thanks in advance.

Regards

Abhilash.

Re: Appending in select statement

JozsefSzikszai — Tue, 08 Jul 2008 06:35:03 GMT

hi,

this is a nested select (or whatever it called). it means that only those records will be selected from table ztab, where vbeln and posnr exist in table vbepwhere as well. The code you provided has nothing to do with APPEND, your subject is misleading.

hope this helps

Re: Appending in select statement

Former Member — Tue, 08 Jul 2008 06:36:05 GMT

Appending is used to To Add the data to existing Internal table.

Suppose in your processing you selected the data from another table. and in this step you want to get the data from another table and populate to same internal table instead of taking new one then we will use the APPENDING

select vbeln

posnr

srcloc

appending table it_tab

from ztab

where exists ( select *

from vbepwhere vbeln eq ztab~vbeln

and posnr eq zstab~posnr.

Where exists

is another type of handling where condition.

first it check the in the table vbep if there is entry then only that record will be added or else it will not.

Re: Appending in select statement

Sidh_M — Tue, 08 Jul 2008 06:36:06 GMT

Hi,

it Works like ... INTO TABLE itab, except that the read lines are appended to the old contents of the internal table itab.

Regards,

SUDHIR MANJAREKAR

Re: Appending in select statement

bpawanchand — Tue, 08 Jul 2008 06:37:43 GMT

SELECT *

FROM vbep WHERE vbeln EQ ztab~vbeln

AND posnr EQ zstab~posnr.

First the inner query is executed and it retrives all the fields from VBEp table based on the conddition.

Secondly

SELECT

vbeln

posnr

srcloc

APPENDING TABLE it_tab

FROM ztab

WHERE EXISTS

now this query is executed and those values are read from which the first has read.

Regards

pavan

Re: Appending in select statement

Former Member — Tue, 08 Jul 2008 06:38:27 GMT

Hi Eric Cartman,

Thanks for the update.

Here I have a question for u, is it same as the for all entries statement.

Regards

Abhilash.

Re: Appending in select statement

Former Member — Tue, 08 Jul 2008 06:38:35 GMT

Hello

Purpose of

Appending

Means already internal table has records and you are now appending records with matching conditions

Where exists

this nested subquery syntax which actually matches each record from the bracket query with Parent Query.

Thanks and Regards

Pushkar Joshi

Re: Appending in select statement

Former Member — Tue, 08 Jul 2008 06:43:49 GMT

Hi Abhilash,

When you use the INTO clause in your select statement the contents retrieved replaces the previous contents in the internal table specified in the into clause....

using appending ... instead of replacing all contents of the internal table it adds the selected data to the internal table.

You already have data in your internal table and you want to add some more information to the existing table.... then you go with appending.....

regards

padma

Re: Appending in select statement

JozsefSzikszai — Tue, 08 Jul 2008 06:45:23 GMT


> Hi Eric Cartman,
>   Thanks for the update.
> 
> Here I have a question for u, is it same as the for all entries statement.
> 
> 
> Regards
> Abhilash.

yes, it is the same

Re: Appending in select statement

Former Member — Tue, 08 Jul 2008 06:53:42 GMT

Hi Eric Cartman,

Thanks for the update.

I have need one more small clarification,

If the for all entries and where exists statements are same while selecting the data from the database,which one will be better performance wise?

I think for all entries is better.

Please correct me if I am wrong?

And one more thing I have observed this select in 4.6c .

I did not find this type of statement in higher versions,

is it obselete statement in higher versions, or performance wise bad ?

if not can we use the same statemnet in higher versions also.

Kindly reply me back.

Thanks in advance.

Regards

Abhilash.

Re: Appending in select statement

Former Member — Tue, 08 Jul 2008 07:02:31 GMT

Hi,

The below is the example for select with "Where exits"... and also look into the below link. Hope it helps you

http://help.sap.com/saphelp_47x200/helpdata/en/dc/dc7614099b11d295320000e8353423/frameset.htm

Correlated, non-scalar subquery:

REPORT demo_select_subquery_1.

DATA: name_tab TYPE TABLE OF scarr-carrname,

name LIKE LINE OF name_tab.

SELECT carrname

INTO TABLE name_tab

FROM scarr

WHERE EXISTS ( select *

FROM spfli

WHERE carrid = scarr~carrid AND

cityfrom = 'NEW YORK' ).

LOOP AT name_tab INTO name.

WRITE: / name.

ENDLOOP.

This example selects all lines from database table SCARR for airlines that fly from New York.

Thanks,

Ramya.R

Re: Appending in select statement

JozsefSzikszai — Tue, 08 Jul 2008 07:04:36 GMT


> Hi Eric Cartman,
>  Thanks for the update.
> I have need one more small clarification,
> If the for all entries and where exists statements are same while selecting the data from the database,which one will be better performance wise?
> 
> I think for all entries is better.
> 
> Please correct me if I am wrong?
> 
> And one more thing I have observed this select in 4.6c .
> I did not find this type of statement in higher versions,
> is it obselete statement in higher versions, or performance wise bad ?
> if not can we use the same statemnet in higher versions also.
> 
> Kindly reply me back.
> 
> Thanks in advance.
> 
> Regards
> Abhilash.

I cannot tell with certanity, but FOR ALL ENTRIES is probably faster in this case - I think you are right on this. You have to try both and see! And this also might be the reason why can't you find this statement in higher releases - because of the performance issues it is not used anymore.

Re: Appending in select statement

Former Member — Tue, 08 Jul 2008 07:10:17 GMT

Hi Abhilash ,

If you are selecting row from Database table according to som condition more than one time . Then the existing records will be replaced with new records.

So, if you don't want to replace the existing records , and yuo want all records then use this APPENDING in your select statement.

Then records will be appended to th existing records of internal table.

DATA:
  BEGIN OF fs_spfli,
    carrid LIKE spfli-carrid,
    connid LIKE spfli-connid,
    cityfrom LIKE spfli-cityfrom,
    cityto   LIKE spfli-cityto,
  END OF fs_spfli.

DATA:
 t_spfli LIKE STANDARD TABLE OF fs_spfli.


select carrid
       connid
       cityfrom
       cityto
  from spfli
  into table t_spfli
 where carrid eq 'AA'.

 select carrid
       connid
       cityfrom
       cityto
  from spfli
  appending table t_spfli
 where carrid eq 'UA'.

 loop at t_spfli into fs_spfli.
   write : / fs_spfli-carrid,
             fs_spfli-connid,
             fs_spfli-cityfrom,
             fs_spfli-cityto.
 endloop.

In the above example if APPENDING is not used then 'AA' records will be raplaced with 'UA' records. Internal table contains only 'UA' records.

So, If we want all records then use APPENDING . Using this records will be appended to the internal table without replacing existing records.

Regards,

Rajitha.

Edited by: Rajitha Muthineni on Jul 8, 2008 12:44 PM