Select statement not returning all records for specific cases

[Former Member]

Hi,

   We have a select statement in our program, which picks up a bunch of pricing line items from DBERCHZ3.

          Data : c_1001 type e_preis value '0000001001'.
          SELECT belnr nettobtr preisbtr INTO TABLE i_price
                                                       FROM dberchz3
                                                       FOR ALL ENTRIES in i_erch
                                                       WHERE
                                                            belnr = i_Erch-belnr and
                                                             preis = c_1001.

     But in some cases, it is not returning all entries.

     For example,

          We have two billing documents, with the same kind of line items. (two records that meet the conditions)

           For the first document it is working fine and fetching both the records.

           For another document, it is picking up just one record.

     When we check the SQL trace, it shows '2 Hits' in both cases.

     But the internal table i_price has just one entry filled into it for the second case.

     Any idea what could be causing this problem.

Cheers,

Pavan.

[Former Member]

Hi,

   We have a select statement in our program, which picks up a bunch of pricing line items from DBERCHZ3.

          Data : c_1001 type e_preis value '0000001001'.
          SELECT belnr nettobtr preisbtr INTO TABLE i_price
                                                       FROM dberchz3
                                                       FOR ALL ENTRIES in i_erch
                                                       WHERE
                                                            belnr = i_Erch-belnr and
                                                             preis = c_1001.

     But in some cases, it is not returning all entries.

     For example,

          We have two billing documents, with the same kind of line items. (two records that meet the conditions)

           For the first document it is working fine and fetching both the records.

           For another document, it is picking up just one record.

     When we check the SQL trace, it shows '2 Hits' in both cases.

     But the internal table i_price has just one entry filled into it for the second case.

     Any idea what could be causing this problem.

Cheers,

Pavan.

[Former Member]

When you are using FOR ALL ENTRIES,  SELECT all the key fields of the table to avoid this problem. Check whether you have used all key fields in SELECT for the table dberchz3

[RaymondGiuseppi]

Check statement documentation SELECT FOR ALL ENTRIES remove duplicate of the rfesult set, so if 2 records carry same values only one is kept. Best practices : always SELECT whole primary keys.

Regards,

Raymond

[Former Member]

Please check PREIS field value for the second record of the second document in table dberchz3.

I think this value is slightly different from the c_1001

[Former Member]

Hi Pavan,

In some cases you need to fetch all the KEY fields (In this case BELZEILE needs to be fetched). If you Rephrase your select query to

          Data : c_1001 type e_preis value '0000001001'.

          SELECT belnr BELZEILE nettobtr preisbtr INTO TABLE i_price

                                                       FROM dberchz3

                                                       FOR ALL ENTRIES in i_erch

                                                       WHERE

                                                            belnr = i_Erch-belnr and

                                                             preis = c_1001.

If you check the F1 help for FOR ALL ENTRIES, it clearly says that

The entire logical expression sql_cond is evaluated for each individual row of the internal table itab. The result set of the SELECT statement is the union set of the result sets produced by the individual evaluations. Rows that appear in duplicate are removed from the result set automatically(In our case since BELZEILE is not fetched, we have multiple belnr's which would be removed because of duplication) . If the internal table itab is empty, the entire WHERE condition is ignored and all rows from the database are placed in the result set.

[former_member209120]

Hi Praveen Kumar,

FOR ALL ENTRIES removes duplicate entries, with out  item BELZEILE (Billing line item for billing documents) field FOR ALL ENTRIES consider values as duplicate entries

Just add BELZEILE in your internal table and select statement

Types : begin of ty_price,

              belnr type dberchz3-belnr,

              BELZEILE type  dberchz3-BELZEILE,

              nettobtr type dberchz3-nettobtr,

              preisbtr type dberchz3-preisbtr,

              end of ty_price.

Data : i_price type table of ty_price.

Data : c_1001 type e_preis value '0000001001'.

          SELECT belnr BELZEILE   nettobtr preisbtr INTO TABLE i_price

                                                       FROM dberchz3

                                                       FOR ALL ENTRIES in i_erch

                                                       WHERE

                                                            belnr = i_Erch-belnr and

                                                             preis = c_1001.

For example:

You select statement

SELECT belnr  nettobtr preisbtr INTO TABLE i_price

                                                       FROM dberchz3

                                                       FOR ALL ENTRIES in i_erch

                                                       WHERE

                                                            belnr = i_Erch-belnr and

                                                             preis = c_1001.

BELNR       NETTOBTR        PREISBTR       

100010          1000                    1520

100010          1000                     1520         "for all entries consider this one as duplicate entry then deletes this line

Modified statement

SELECT belnr BELZEILE   nettobtr preisbtr INTO TABLE i_price

                                                       FROM dberchz3

                                                       FOR ALL ENTRIES in i_erch

                                                       WHERE

                                                            belnr = i_Erch-belnr and

                                                             preis = c_1001.

BELNR         BELZEILE             NETTOBTR        PREISBTR       

100010                01                           1000                    1520

100010                 02                            1000                    1520       

Not only for this in feature if your fetching any item you should consider item field.

Regards,

Ramesh.T

[Former Member]

For all entires query will not bring the duplicates that corresponds to the key. So please try to use inner join query.

[Former Member]

Hi pavan,

For All Entries will delete all the duplicate entries from the result. So you need to specify all the key of the table from where you are fetching in the select clause or specify all the key fields in the where clause if you dont want to select all the key fields.

Regards,

Ashish Kumar