topic fetching data from mseg select * and select field_list problem in Application Development and Automation Discussions

fetching data from mseg select * and select field_list problem

Former Member — Sat, 18 Apr 2009 13:22:08 GMT

when i executing the below select statement the sy-dbcnd = 502.

SELECT *  FROM mseg INTO CORRESPONDING FIELDS OF TABLE imseg
         FOR ALL ENTRIES IN itab
           WHERE bwart IN ('309', '311', '315', '413', '411', '412')
             AND xauto EQ 'X'
             AND matnr EQ itab-matnr
             AND mat_kdauf EQ itab-vbeln
             AND mat_kdpos EQ itab-posnr.

and when i change field list in place of * the sy-dbcnd = 369.

SELECT mat_kdauf mat_kdpos matnr kdauf kdpos menge  
          FROM mseg INTO CORRESPONDING FIELDS OF TABLE imseg
         FOR ALL ENTRIES IN itab
           WHERE bwart IN ('309', '311', '315', '413', '411', '412')
             AND xauto EQ 'X'
             AND matnr EQ itab-matnr
             AND mat_kdauf EQ itab-vbeln
             AND mat_kdpos EQ itab-posnr.

is anyone know what is the reason of fatched record difference.

Re: fetching data from mseg select * and select field_list problem

MarcinPciak — Sat, 18 Apr 2009 13:37:30 GMT

In first case only records with xauto = X are transffered, in second one xauto is no longer consider as you are not projecting it


SELECT mat_kdauf mat_kdpos matnr kdauf kdpos menge xauto  "insert xauto here, db should return same result now

Regards

Marcin

Re: fetching data from mseg select * and select field_list problem

shahid_malayil1 — Sat, 18 Apr 2009 13:38:11 GMT

Hi NSTomar,

When you give * there are 502 rows returned. when you give the fields in specific it returned only 369 because there will be no entries for the 133 ( 502 - 369 ) for the these fields but there will be entries in other fields which you are not selecting.

Best regards,

Shahid Malayil

Re: fetching data from mseg select * and select field_list problem

Former Member — Sat, 18 Apr 2009 13:49:40 GMT

Hi,

The difference is that in select * u have primary key ..

where as when u are selecting the fields individually the repetition of these fields is not captured.

one way to check it is simple

sort the data fetched from select * case based on the fields selectes

then delete adjacent on the same 5 fields

it should give u the same number of entries ..

hope i have made it clear

Re: fetching data from mseg select * and select field_list problem

Former Member — Sat, 18 Apr 2009 14:11:28 GMT

What happens in first case is when you do select * the unique entries by table key corresponding to the condition on where fields are fetched.

Whereas in the second case the unique entries by table key present in your field selection corresponding to the condition on where fields are fetched.

So it is always advisable to fetch all the table primary keys in your select statements.

Regards,

Lalit Mohan Gupta.

Re: fetching data from mseg select * and select field_list problem

MarcinPciak — Sat, 18 Apr 2009 14:13:22 GMT


The difference is that in select * u have primary key ..
where as when u are selecting the fields individually the repetition of these fields is not captured

What you are trying to say is the number of passed record depend on passed field?

What would then happen if you just write it like this:


SELECT *  FROM mseg INTO CORRESPONDING FIELDS OF TABLE imseg

Would it be different when you have such statemnt


SELECT mat_kdauf mat_kdpos matnr kdauf kdpos menge  
          FROM mseg INTO CORRESPONDING FIELDS OF TABLE imseg

No! because the projected fields simply inform DB interface which fields should he transfer back to ABAP program (to avoid unnecessary network load).

The clause which affects how many records are passed back to the program is this one:


FOR ALL ENTRIES IN itab
WHERE bwart IN ('309', '311', '315', '413', '411', '412')
             AND xauto EQ 'X'
             AND matnr EQ itab-matnr
             AND mat_kdauf EQ itab-vbeln
             AND mat_kdpos EQ itab-posnr.

DB interface checks which of these records provided in itab matches the records in DB including the condition in WHERE clause. Then it restricts that hits with another WHERE bwart IN .... and AUTO = 'X'.

At the end only those records are passed back to formulate the result set.

Furthermore, there will never be reduntant combination of these fields (as long as full key is to be passed back), as DB can't store non-unique records.

Regards

Marcin

Re: fetching data from mseg select * and select field_list problem

Former Member — Sat, 30 May 2009 06:14:51 GMT

self solved create index