Select statement for range table

[siongchao_ng]

Hi all,

I have a select statement here which is selecting data wrongly using the range table. I have a pernr here 73535 which the perid n pa0002 value is 999973535. And the select statement below is selecting this pernr into table y. By right, only pernr with perid value 999999999 will be selected. Anyone have any idea?

Select distint perid into table y from pa0002 where perid in t_value_check.

t_value_check is a table consist of:

sign: I

opt: EQ

low: 999999999

high:

[GauthamV]

 Select distint perid into table y from pa0002 where perid in t_value_check.
 
 t_value_check is a table consist of:
 
 sign: I
 opt:  EQ
 low: 
 high:999999999 ---------> use it here and check.

[siongchao_ng]

Hi Gautam,

Select distint perid into table y from pa0002 where perid in t_value_check.

t_value_check is a table consist of:

sign: I

opt: EQ

low:

high:999999999 -

---

> use it here and check.

I changed the value to high instead of low and pernr 73535 still get selected.

[jrg_wulf]

Hi Siong,

make sure, that your range value really is in table t_value_check, not only in working area (header line).

When table t_value_check is empty, all values will be selected.

regards

Jörg

Edited by: Jörg Wulf on Jun 29, 2010 10:40 AM

[siongchao_ng]

Hi Jorg,

Hi Siong,

make sure, that your range value really is in table t_value_check, not only in working area (header line).

When table t_value_check is empty, all values will be selected.

regards

Jörg

Edited by: Jörg Wulf on Jun 29, 2010 10:40 AM

t_value_check is a table consist of:

sign: I

opt: EQ

low: 999999999

high:

[Former Member]

Hi,

Check the definition part of the Range table t_value_check. Before the select statement, check the contents of the range table in Debug mode.

Regards

Vinod

[siongchao_ng]

Hi Vinod,

The defnition of the range table t_value_check as followed:

TYPES: BEGIN OF ty_value_check,

sign TYPE char1,

opt TYPE char2,

low TYPE char255,

high TYPE char255,

END OF ty_value_check.

t_value_check is a table consist of:

sign: I

opt: EQ

low: 999999999

high:

[Former Member]

Hi,

Try the following code for populating value into the range table.

TYPES: BEGIN OF ty_value_check,
sign TYPE char1,
opt TYPE char2,
low TYPE char9
high TYPE char9,
END OF ty_value_check.

data : t_value_check type standard table of ty_value_check,
          w_value_check type ty_value_check.

move 'I' to w_value_check-sign.
move 'EQ' to w_value_check-option.
move '999999999' to w_value_check-low.
append w_value_check to t_value_check.

Regards

Vinod

[SuhaSaha]

What is the difference from Gautham's suggestion ?

[siongchao_ng]

Hi Vinod,

The problem is not with the value range table. It contains value as I mentioned.

t_value_check is a table consist of:

sign: I

opt: EQ

low: 999999999

high:

Pernr_tab consist 2 pernr 73535 and 73536.

Only Pernr 73536 perid in pa0002 is 999999999.

Pernr 73535 perid in pa0002 is 999973535.

The problem is with the select statement. Now both pernr are selected. How?

LOOP at pernr_tab

SELECT DISTINCT perid

INTO table y

FROM pa0002

WHERE perid in t_value_check.

EndLoop.

[jrg_wulf]

Hi Siong, your problem can't be anything but range or table PA0002.

Either you've got additional entries for same PERNR but different PERID or most likely, your t_value_check is not populated.

If you declared it like this:

RANGES: t_value_check for PA0002-PERID.

t_value_check-sign = 'I'.
t_value_check-option = 'EQ'.
t_value_check-low = '999999999'.

only assigning the values won't do the trick since they stay in the header line and won't populate the table.

instead try it the way Vinod has suggested or simply write:

append 'IEQ999999999' to t_value_check.

ranges, like select-options only do their work, when values are properly stored in table-body.

regards

Jörg

[Former Member]

@suhas,

He mentioned to pass the value in T_value_check-high. I feel passing value in t_value_check-low instead of high will make a difference.

Regards

Vinod

[jrg_wulf]

Hi Siong,

I just realized your recent posting with your code-snippet

LOOP at pernr_tab

SELECT DISTINCT perid
INTO table y
FROM pa0002
WHERE perid in t_value_check.

EndLoop.

Why do you put your select inside the loop without specifying any reference to pernr_tab values?

If you have 2 values in pernr_tab as you wrote, all you get is running your select statement twice with identical result.

Or do i miss something here?

regards Jörg

[siongchao_ng]

Hi Jorg,

You got into the core problem area here. That is what I mean here. I need to loop through a set of pernrs and check each pernr perid field in pa0002 against the preset value in t_value_check. So how do I go around it?

[Former Member]

Hi Siong ,

As jork told declare a range type pa0002-perid.

RANGES: t_value_check FOR pa0002-perid.

*  Append the Value 999999999 to the range
t_value_check-sign = 'I'.
t_value_check-option = 'EQ'.
t_value_check-low = '999999999'.
APPEND t_value_check.
CLEAR t_value_check.

*  Instead of loop the pernr Tab ---> Make use of FOR ALL ENTRIES as below
if pernr_tab[] is not INITIAL.
SELECT DISTINCT perid FROM pa0002 INTO TABLE Y
  FOR ALL ENTRIES IN pernr_tab
   WHERE pernr eq pernr_tab-pernr and perid IN t_value_check.
Endif.

Hope it helps.

[Former Member]

Hi,

Just give the BEGDA and ENDA.

With Regards,

Sumodh.P

Edited by: Sumodh P on Jun 29, 2010 2:36 PM

[vattekrishna]

Hi Siong,

Can you paste the entire code (both declaration & extract parts)to avoid confusion.

[siongchao_ng]

Hi Krishna,

The problem part is here. As said in my post earlier. t_value_check already consist of the values mentioned.

LOOP at pernr_tab

SELECT DISTINCT perid

INTO table y

FROM pa0002

WHERE perid in t_value_check.

EndLoop.

[vattekrishna]

Hi Siong,

I think you have not appended the range table.

The values you said are only in the header line not in the table.

I think the problem is not in the select statement but in your where condition data.

Please check one more time both header line and table.

i.e

t_value_check and t_value_check[ ].

[jrg_wulf]

Hi Siong,

just to get your request clear - i understood, that you want to identify multiple employees representing a single person?

Or does '999999999' imply, that perid is not provided yet?

Anyway, since you only select field PERID, all you get is the the number of occurances of your given value, if any.

Nevertheless, try:

APPEND 'IEQ999999999000000000' to t_value_check.
LOOP at pernr_tab

SELECT DISTINCT perid
APPENDING table y
FROM pa0002
WHERE PERNR eq pernr_tab-pernr
AND perid in t_value_check.

EndLoop.

Instead, you could use prasath's approach to select all matching data in "burst mode".

regards

Jörg