Internal table in SQ02

[Former Member]

Hi all,

I'm using 'select single' code in SQ02 to retrieve user names from table V_USR_NAME (not possible via table join in this case). This is slowing my report down considerably.

Rather than retrieve the user names one by one I hear it's more efficient to create an internal table and sort the names first.

Please can someone explain how this is done?

Many thanks

Dave

[Former Member]

Hi all,

I'm using 'select single' code in SQ02 to retrieve user names from table V_USR_NAME (not possible via table join in this case). This is slowing my report down considerably.

Rather than retrieve the user names one by one I hear it's more efficient to create an internal table and sort the names first.

Please can someone explain how this is done?

Many thanks

Dave

[Former Member]

Hi Dave,

Should be something like this:

data: it_users type standard table of v_usr_name.

select * "choose fields to select

  from v_usr_name

  into table it_users

  where ...          "include your conditions here

            order by "sort criteria

for reading,

read table it_users into wa_users with key field = field value.

I hope I was helpful.

Take care,

[Former Member]

Hi Ricardo,

Many thanks for your reply.

Can i put the code into an additional field or should it be in one of the code sections on the code tab?

I've put this code in an additional field as follows: (agent is the field that contains user id)

   data: it_users type standard table of v_usr_name.

select bname name_text from v_usr_name
  into table it_users.

  read table it_users into name
  with key = agent.

Kind regards

Dave

[Former Member]

Hi Dave,

I think you can put it an additional field, put please validate first it's meeting your requirements regarding execution time.

Take care,

[Former Member]

Hi Ricardo,

The code passes the checks but the field is coming out blank in the report. I need nametext to populate my field name. If you could point me in the right direction it would be much appreciated!

Should this be an efficient way of retrieving the user names? The report pulls through open items from VIM Analytics, currently around 4000 records. There are some users with dozens of docs allocated to them, so i was hoping that using an internal table would mean that each name only needs to be retrieved once rather than each time the user id occurs in the report.

Kind regards

Dave

[Former Member]

Hi Dave,

Was your initial select successful ? Check table ADRP, that has a language dependant field, in case you are having problems (v_usr_name) is just a view.

If everything else is ok, try to check the field's definitions on the infogroup.

Regarding efficiency, you can always try to narrow the number of records fetched from table, depending on the report logic. Afterwards, you can perform a read table with binary search addition. This will save you some accesses to database.

I hope I was of any help.

Take care,

[Former Member]

Hi Dave,

I will give you examle with field mara-aenam (Name of Person Who Changed Object).

1. Code Section - data.

2. Code Section - initialization.

3. Additional field NAME_TEXT - definition.

4. Additional field NAME_TEXT - code.

Regards

Boris

[Former Member]

Hi Boris,

That's a huge help and runs quickly.Thank you very much!

Thank you to Ricardo also.

Regards

Dave