Problem in Select using for all entries

[former_member188251]

Hi All,

My second select statement from dpr_task is not working(sy-subrc 4) though there are entries, I am perplexed !

TYPES:

BEGIN OF st_pro_id,

   guid        LIKE cgpl_project-guid,

   external_id LIKE cgpl_entity-external_id,

END OF st_pro_id.

data : gt_proj_guid TYPE standard table of  st_pro_id,

          gt_dpr_task TYPE STANDARD TABLE OF dpr_task.

  SELECT guid external_id

      FROM cgpl_entity

        INTO TABLE gt_proj_guid

          WHERE version = space AND

                external_id IN project AND

                object_type = 'DPO'.

if  gt_proj_guid is not initial.

    SELECT * FROM dpr_task

          INTO TABLE gt_dpr_task

          FOR ALL ENTRIES IN gt_proj_guid

    WHERE guid = gt_proj_guid-guid.

endif.

Best Regards,

Shankar.

[arindam_m]

Hi,

I think sort your entries in table gt_proj_guid based on the field in where condition (guid) and delete duplicates in the internal table gt_proj_guid before using FOR ALL ENTRIES. Also try out JOINS..

Cheers,

Arindam

[matt]

Definitely use an INNER JOIN. In most cases it is more efficient than FOR ALL ENTRIES; it also makes for more readable code which is cheaper and easier to maintain - and better programming.

[Arun_Prabhu_K]

Hi Shankar,

     One reason for your problem may be because the statement is not getting executed.

     Check if entries are available in internal table gt_proj_guid.

          if  gt_proj_guid[] is not initial. "This checks if whole internal table is empty

               SELECT * FROM dpr_task INTO TABLE gt_dpr_task

               FOR ALL ENTRIES IN gt_proj_guid

                    WHERE guid = gt_proj_guid-guid.

           endif.

Regards.

[Former Member]

Hi Shankar,

Assuming your first Select is successful, just check whether any Conversion Exit is there on field guid in table dpr_task. If its there then use it before using FOR ALL ENTRIES.

I am away from SAP system so cannot execute and advice the exact cause.

BR.

[Former Member]

Hi Shankar,

Please check the first query . If you are getting the entries there check whether those entries are there in second table. Am not seeing anything wrong in your second query.

Regards,

Vinay

[former_member209120]

Hi Shankaranarayan,

Please check first there is no direct link between this two tables cgpl_entity and  DPR_TASK.

check it SQVI

due to that sy-subrc = 4.

[Former Member]

Hi Shankar,

I checked both the tables and they do have conversion exits for GUID but thats not the problem. There seems to be no corresponding data amongst the two tables. I checked in SE16 the data for both the tables and found no corresponding entry for GUID in DPR_TASK from GUID in CGPL_ENTITY. I advice you to check whether you have any corresponding entry first in SE16 of both tables.

BR.

[Former Member]

Hi Shankarnarayan,

May be the query is failing due to the field GUID in DPR_TASK does not match to cgpl_project-guid in terms of data dictionary attribiutes like data element etc. May be sometimes the fields do not have internal conversion exits due to which sometimes zeros are not adjusted to the field value and due to which, it can be the case in this scenario one GUID in DPR_TASK can have zeros and the one in CGPL_PROJECT can't or vice versa as well.

Please check.

BR

Pranav agrawal

[Former Member]

I would request you to go thru the link:

cgpl_entity->cgpl_hierarchy-> dpr_task

Hopefully this solves your issue.

BR

Pranav Agrawal

[Former Member]

Hi Shankar,

I think there is either a type mismatch gt_dpr_task-guid & gt_proj_guid-guid fields or as Ankit said Conversion exit FM can be called and append zeros for gt_proj_guid-guid field.

Hope it helps.

Regards,

Adithi

[Former Member]

Hi shankarnarayan,

1) sort the first table.

2) data in the first table might not contain data in the second table. so sy-subrc eq 4.

3) here in the second select statement you are comparing the both RAW type fields in both tables.

Actually during runtime, RAW type fields converted to binary values.

Here, In the first query values are converted as binary values. and this binary values are compared with the actual values in table dpr_task. so it will always gives sy-subrc = 4.

[RaymondGiuseppi]

As  Matthew Billingham wrote you could use a JOIN and not a FOR ALL ENTRIES, but you could also use a subquery, the code would look something like the following statement.

SELECT * FROM dpr_task
   INTO TABLE gt_dpr_task
   WHERE EXISTS ( SELECT * FROM cgpl_entity
     WHERE external_id = gt_dpr_task~guid
       AND version EQ space
       AND external_id IN project
       AND object_type = 'DPO' ).

or

SELECT * FROM dpr_task
   INTO TABLE gt_dpr_task
   WHERE guid IN ( SELECT external_id FROM cgpl_entity
     WHERE version EQ space
       AND external_id IN project
       AND object_type = 'DPO' ).

Regards,

Raymond

[former_member188251]

My bad, the entries were indeed not there in dpr_task . I should have checked it . Apologize for the same.