topic Re: Select statement for range table in Application Development and Automation Discussions

Select statement for range table

siongchao_ng — Tue, 29 Jun 2010 08:31:29 GMT

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:

Re: Select statement for range table

GauthamV — Tue, 29 Jun 2010 08:36:07 GMT


 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.

Re: Select statement for range table

jrg_wulf — Tue, 29 Jun 2010 08:40:13 GMT

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

Re: Select statement for range table

Former Member — Tue, 29 Jun 2010 08:59:43 GMT

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

Re: Select statement for range table

Former Member — Tue, 29 Jun 2010 09:04:33 GMT

Hi,

Just give the BEGDA and ENDA.

With Regards,

Sumodh.P

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

Re: Select statement for range table

siongchao_ng — Tue, 29 Jun 2010 09:27:17 GMT

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.

Re: Select statement for range table

siongchao_ng — Tue, 29 Jun 2010 09:29:33 GMT

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:

Re: Select statement for range table

siongchao_ng — Tue, 29 Jun 2010 09:32:10 GMT

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:

Re: Select statement for range table

Former Member — Tue, 29 Jun 2010 09:40:05 GMT

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

Re: Select statement for range table

SuhaSaha — Tue, 29 Jun 2010 10:02:34 GMT

What is the difference from Gautham's suggestion ?

Re: Select statement for range table

siongchao_ng — Tue, 29 Jun 2010 10:07:53 GMT

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.

Re: Select statement for range table

jrg_wulf — Tue, 29 Jun 2010 10:22:17 GMT

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

Re: Select statement for range table

Former Member — Tue, 29 Jun 2010 10:30:59 GMT

@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

Re: Select statement for range table

jrg_wulf — Tue, 29 Jun 2010 10:39:32 GMT

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

Re: Select statement for range table

siongchao_ng — Wed, 30 Jun 2010 01:46:29 GMT

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?

Re: Select statement for range table

vattekrishna — Wed, 30 Jun 2010 02:48:41 GMT

Hi Siong,

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

Re: Select statement for range table

siongchao_ng — Wed, 30 Jun 2010 04:09:15 GMT

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.

Re: Select statement for range table

Former Member — Wed, 30 Jun 2010 05:54:25 GMT

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.

Re: Select statement for range table

vattekrishna — Wed, 30 Jun 2010 06:31:32 GMT

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[ ].

Re: Select statement for range table

jrg_wulf — Wed, 30 Jun 2010 07:13:15 GMT

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