Regarding database selection

[Former Member]

hi all,

I have a db table which is consisting of the following data:

SNO VALUE

-

---

123 A1

123 A2

123 A3

124 A2

125 A1

125 A3

128 A1

I want to fetch the records into an internal table and Exclude retrieved sno with value A1 only, without A2 or A3.

Include retrieved sno with value A1 with A2 or A1 with A3.

...please let me know how to resolve this issue.

[Former Member]

i don know abt ut exact requirement ,code cna be like this -

data begin of itab occurs 0.

include structure ztable.

data end of itab.

select * into table ztable from itab where sno = 'A1'.

LIKE THIS U CAN PROCESS.

U CAN CHANGE THE WHERE CONDITION ACCORDINGLY

[Former Member]

Hi Subhakar,

Can u plz explain in detail so that we can understand ur problem....

BALU.

[Manohar2u]

if you need the following records in your final internal table

SNO VALUE

-

---

123 A2

123 A3

124 A2

125 A3

then delete itab where value = 'A1'.

i can think of this for a moment.

[Former Member]

SNO VALUE

-

---

123 A1

123 A2

123 A3

124 A2

125 A1

125 A3

128 A1

I want to fetch the records into an internal table and Exclude retrieved sno with value A1 only, without A2 or A3.

Include retrieved sno with value A1 with A2 or A1 with A3.

that means the output must be:

sno

-

---

123 (because it is holding A1 and <b>A2, A3</b>)

125 (because it is holding A1 and <b>A3</b>)

the snumbers 128(<b>holding A1 but not holding either A2 or A3</b>) and 124(<b>not holding A1</b>) should not be retrieved into the final internal table

[Former Member]

Hi ,

What i would suggest is select all the records into an internal table.

No loop on this internal table and read table where the value is not equal to A1 , if there is a record not no processing else delete the record.

if you want a similar code i shall send it

Regards

Arun

Message was edited by:

Arun R

[Former Member]

plase help me. I will award points definetely.....:-)

[Former Member]

Hi

You cannot achieve this using a select query. This can be done only in a loop.

A sample code for the data you have given,

My table zkb_test has the following data,

123 A1

123 A2

123 A3

124 A2

125 A1

125 A3

128 A1

REPORT  zkb_test5.

DATA: i_zkb_test TYPE TABLE OF zkb_test,
      i_zkb_test1 TYPE TABLE OF zkb_test,
      w_zkb_test1 TYPE zkb_test,
      w_zkb_test2 TYPE zkb_test,
      w_zkb_test3 TYPE zkb_test,
      v_index TYPE sy-tabix VALUE 1.

SELECT * FROM zkb_test INTO TABLE i_zkb_test.

SORT i_zkb_test BY sno value ASCENDING.

LOOP AT i_zkb_test INTO w_zkb_test1.
w_zkb_test3 = w_zkb_test1.
  AT NEW sno.
    v_index = sy-tabix + 1.
    READ TABLE i_zkb_test INTO w_zkb_test2 INDEX v_index.
    IF w_zkb_test3-sno = w_zkb_test2-sno AND w_zkb_test3-value = 'A1'
       AND ( w_zkb_test2-value = 'A2' OR w_zkb_test2-value = 'A3' ).
      WRITE / w_zkb_test1-sno.
    ENDIF.
  ENDAT.
ENDLOOP.

The result is 123 and 125 as you asked. Hope this solves your problem.

Regards

Kathirvel

[Former Member]

SORT it_sno by sno.

LOOP AT it_sno.

IF NOT sno_temp = it_sno-sno.

sno_temp = it_sno-sno.

count1 = 0.

ENDIF.

IF it_sno-VALUE = A1.

count = 1.

ELSEIF it_sno-VALUE = A2 AND count = 1.

count = 2.

count1 = 1.

ELSEIF it_sno-VALUE = A3 AND ( count = 2 OR count = 1 ) .

count = 3

count1 = 1.

ENDIF.

IF count != 1 and count1 = 1.

copy the sno another itable.( This is the included table after completing loop delete duplicate entries will give the result)

ENDIF.

AT END OF sno.

clear count.

clear count1.

ENDAT.

ENDLOOP.

use this logic.

Yuvaram.

[Former Member]

&----

---

*& Report Z_TESTYUVA *

*& *

&----

---

*& *

*& *

&----

---

REPORT Z_TESTYUVA .

DATA : Begin of it_sno occurs 0,

sno TYPE i,

value(2) TYPE c,

END of it_sno.

DATA : Begin of it_sno1 occurs 0,

sno TYPE i,

value(2) TYPE c,

END of it_sno1.

DATA : count Type i Value 0,

count1 Type i Value 0,

sno_temp Type i.

it_sno-sno = 123.

it_sno-value = 'A1'.

Append it_sno.

clear it_sno.

it_sno-sno = 123.

it_sno-value = 'A2'.

Append it_sno.

clear it_sno.

it_sno-sno = 123.

it_sno-value = 'A3'.

Append it_sno.

clear it_sno.

it_sno-sno = 124.

it_sno-value = 'A2'.

Append it_sno.

clear it_sno.

it_sno-sno = 125.

it_sno-value = 'A1'.

Append it_sno.

clear it_sno.

it_sno-sno = 125.

it_sno-value = 'A3'.

Append it_sno.

clear it_sno.

it_sno-sno = 128.

it_sno-value = 'A1'.

Append it_sno.

clear it_sno.

SORT it_sno by sno.

LOOP AT it_sno.

IF NOT sno_temp = it_sno-sno.

sno_temp = it_sno-sno.

count1 = 0.

ENDIF.

IF it_sno-VALUE = 'A1'.

count = 1.

ELSEIF it_sno-VALUE = 'A2' AND count = 1.

count = 2.

count1 = 1.

ELSEIF it_sno-VALUE = 'A3' AND ( count = 2 OR count = 1 ) .

count = 3.

count1 = 1.

ENDIF.

IF count > 1 and count1 = 1.

it_sno1-sno = it_sno-sno.

Append it_sno1.

clear it_sno1.

ENDIF.

AT END OF sno.

clear count.

clear count1.

ENDAT.

ENDLOOP.

DELETE ADJACENT DUPLICATES FROM it_sno1.

LOOP AT it_sno1.

write / it_sno1-sno.

ENDLOOP.

This is the program for above logic.

you can load internal table with your values.

By

Yuvaram.

Dont forget to reward

[Former Member]

REPORT ychatest LINE-SIZE 350.

DATA : BEGIN OF itab OCCURS 0,
         value(2),
         sno(3),
       END OF itab.

DATA : BEGIN OF it_final OCCURS 0,
         sno(3),
       END OF it_final.

DATA : flag(1).

itab-sno = '123'.
itab-value = 'A1'.
APPEND itab.
CLEAR itab.

itab-sno = '123'.
itab-value = 'A2'.
APPEND itab.
CLEAR itab.

itab-sno = '123'.
itab-value = 'A3'.
APPEND itab.
CLEAR itab.

itab-sno = '124'.
itab-value = 'A2'.
APPEND itab.
CLEAR itab.

itab-sno = '125'.
itab-value = 'A1'.
APPEND itab.
CLEAR itab.

itab-sno = '125'.
itab-value = 'A3'.
APPEND itab.
CLEAR itab.

itab-sno = '128'.
itab-value = 'A1'.
APPEND itab.
CLEAR itab.

SORT itab BY sno value.


LOOP AT itab.
  AT NEW sno.
    READ TABLE itab INDEX sy-tabix.
    IF itab-value EQ 'A1'.
      flag = 'X'.
    ENDIF.
    IF itab-value EQ 'A2' OR
       itab-value EQ 'A3'.
      IF flag EQ 'X'.
        it_final-sno = itab-sno.
        APPEND it_final.
        CLEAR : it_final , flag.
      ENDIF.
    ENDIF.

  ENDAT.
ENDLOOP.

DELETE ADJACENT DUPLICATES FROM it_final COMPARING sno.

LOOP AT it_final.
  WRITE : / it_final-sno .
ENDLOOP.

[Former Member]

thank you all...

I have awarded points for all..