Pulling Random Rows.

[Former Member]

Hi All,

I have a situations where in I need to pull rows from a particular dbtable randomly. Is there any function module or SQL syntax whereby I can do it? This means that everytime the SQL statement is fired I want that the output should not be the same. I am using UP To 5 Rows.

Please help.

Regards,

Aris

[Former Member]

Hi,

Please try this function module

RANDOM_TABLE_ENTRY

and give ur table name as import parameter.

The value of TABLEPOS keeps on changing , which u can store its value and in ur query u can use this variable as index.

DATA : t_sdtest type STANDARD TABLE OF ZSD_TEST,

x_sdtest TYPE zsd_test,

lv_index TYPE INTEGER4.

Do 4 times.

clear lv_index.

CALL FUNCTION 'RANDOM_TABLE_ENTRY'

EXPORTING

table = 'ZSD_TEST'

• ITEM1 =

• ITEM2 =

• ITEM3 =

• ITEM4 =

IMPORTING

• VALUE1 =

• VALUE2 =

• VALUE3 =

• VALUE4 =

• TABLESIZE =

TABLEPOS = lv_index

• EXCEPTIONS

• GENERATION_FAILED = 1

• OTHERS = 2

.

IF sy-subrc <> 0.

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

select * FROM ZSD_TEST into TABLE t_sdtest.

read TABLE t_sdtest into x_sdtest INDEX lv_index.

WRITE : x_sdtest-MATNO.

ENDDO.

Edited by: Rahul Babukuttan on Sep 13, 2010 1:21 PM

[sitakant_tripathy2]

Hi Johannes,

I must say that this is a very strange requirement....I dont think thr is any direct method of doing it..but having said that you would always find a logic to do the same...

This can be done by using the Open Cursor and Fetch Cursor methodology...but the limitation is that tha data cannot be as random as you thought of but will follow the index mechanisms..what that means is the first time you select you will have the records 1-5 and the nect time you select you will have records 6-10 and so on...

My assumption is that you would be doing this select on some user-command/event..Please follow the steps below:

1) Create a global variable for DB_CUR type crsor....

2) Create a function module with the GV_CUR as the changing parameter and the database table IT_TAB as the tables parameter.

The function module should look somewhat like this...

IF gv_cur IS INITIAL.
      OPEN CURSOR gv_cur FOR SELECT (it_fields)
      FROM (i_tablename) BYPASSING BUFFER.
ENDIF.

WHILE NOT l_cursor IS INITIAL.
*simply overwrite the table
  FETCH NEXT CURSOR gv_cur
  INTO CORRESPONDING FIELDS OF TABLE it_tab PACKAGE SIZE 5.
ENDWHILE.

3) Call this function module till the time you want to select the random data...the logic is the cursor would get incremented by 5

each time you call this and hence you would get different data from the database...

4) Finally before leaving the program close the cursor GV_CUR...

Frankly speaking even I dont know whether this would work perfectly for you but its worth giving a shot....

You may also want to refer to the following link for more details[http://wiki.sdn.sap.com/wiki/pages/viewpage.action?pageId=87818744]

Hope this helps ....

Regards,

Sitakant...

Please follow this link for more information..

Edited by: Sitakant Tripathy on Sep 13, 2010 1:30 PM