Look Up Logic in Select

[former_member206475]

Hi Guys,

I am trying to do a look up from a master data table.

My situation is that i have to compare a coloum for multivalues in where clause. But this does not work.

May be i am using a wrong way of implementing the logic. I tried using IN , Like in where caluse but in was giving a short dump. Then i tried below. It executes but not showing result.

When i try with only one value condition it works. Data exists in system.

select /BIC/R0CAGID CMS_GRRT CMS_CASVAL

     into table it_cag

     from /BIC/MR0CAGID

     where CMS_CAGTYP = '030600' AND

                 OBJVERS = 'A'.

select /BIC/R0CAGID CMS_GRRT CMS_CASVAL

     into table it_cag

     from /BIC/MR0CAGID

     where CMS_CAGTYP = '030200' AND

                 OBJVERS = 'A'.

select /BIC/R0CAGID CMS_GRRT CMS_CASVAL

     into table it_cag

     from /BIC/MR0CAGID

     where CMS_CAGTYP = '030601' AND

                 OBJVERS = 'A'.

   LOOP AT it_source INTO ls_source.

     IF ls_source-CMS_CAGTYP = '030600' OR

        ls_source-CMS_CAGTYP = '030200'  OR

        ls_source-CMS_CAGTYP = '030601'.

       READ TABLE it_cag into lv_cag

       WITH KEY lv_cagid = ls_source-/BIC/R0CAGID.

Please assist.

Regards

Zabi

[FredericGirod]

Hi,

INTO TABLE will erase the content of the internal table

used APPENDING CORRESPONDING FIELDS OF TABLE ..  instead, that will add the result to the already existing entries

regards

Fred

[former_member201275]

First:

change "into table it_cag"

to "appending table it_cag"

for all of your select statements.

[FredericGirod]

not all, INTO TABLE for the first, and APPENDING TABLE for the others.

[former_member206475]

Hi,

I tried teh following but gives a short dump.

select /BIC/R0CAGID CMS_GRRT CMS_CASVAL

      into table it_cag

      from /BIC/MR0CAGID

      where CMS_CAGTYP = '030200' AND

                  OBJVERS = 'A'.

select /BIC/R0CAGID CMS_GRRT CMS_CASVAL

      appending table it_cag

      from /BIC/MR0CAGID

      where CMS_CAGTYP = '030600' AND

                  OBJVERS = 'A'.

select /BIC/R0CAGID CMS_GRRT CMS_CASVAL

      appending table it_cag

      from /BIC/MR0CAGID

      where CMS_CAGTYP = '030601' AND

                  OBJVERS = 'A'.

[former_member206475]

Hi,

I tried but gives a short dump..

[former_member206475]

For two select statements it works but when i add the third it gives a short dump

[raymond_giuseppi]

Or use APPENDING in every SELECT, but REFRESH table before.

[Former Member]

You don't need to write 3 select statements.

After WHERE pass the CMS_CAGTYP using OR operator.

Like: WHERE ( CMS_CAGTYP = '030600' OR CMS_CAGTYP = '030200' )

               AND OBJVERS = 'A'.

[former_member183607]

Hi,

    Instead of Three Hits to database table , I would like to use In Operator , For Example

select /BIC/R0CAGID CMS_GRRT CMS_CASVAL

     into table it_cag

     from /BIC/MR0CAGID

     where CMS_CAGTYP  in ( '030600'  ,  '030200' ,  '030601' )

               AND

                 OBJVERS = 'A'.

[former_member206475]

Hi Shukla,

I tried this statement you mentioned.

But it doesn not work.

[former_member183607]

Hi,

     Can you share the Dump Message getting with the use of in operator.

[former_member206475]

What happened?

    Error in the ABAP Application Program

    The current ABAP program "%_T0011Z" had to be terminated because it has

    come across a statement that unfortunately cannot be executed.

Error analysis

    An entry was to be entered into the table

     "\PROGRAM=%_T0011Z\FORM=ROUTINE_FORM00001\DATA=IT_CAG" (which should have

    had a unique table key (UNIQUE KEY)).

    However, there already existed a line with an identical key.

    The insert-operation could have ocurred as a result of an INSERT- or

    MOVE command, or in conjunction with a SELECT ... INTO.

    The statement "INSERT INITIAL LINE ..." cannot be used to insert several

     initial lines into a table with a unique key.

[former_member183607]

Hi,

     Please Check your internal table declaration it_cag,

     It Seems you have declared with Unique Key Clause.

[former_member206475]

This is the table declaration :

wa_cag TYPE ty_cag,

     tt_cag TYPE SORTED TABLE OF ty_cag

     WITH UNIQUE KEY lv_cagid.

What declaration can i give for the above one?

[former_member206475]

If i remove the Unique key line then i get below error:

Syntax error: "TT_CAG" has a generic type. Use of this type is only possible for typing field symbols and formal parameters -

[former_member183607]

Hi,

     It depends on your requirement , If you want all records(Duplicate records)

     then u may use

  tt_cag TYPE SORTED TABLE OF ty_cag

     WITH NON-UNIQUE KEY lv_cagid.

[former_member184958]

Hi,

What type of internal table you are using ? If you wants to store the unique key, try to pick the item level field. then sort and delete the duplicate entry.

Regards.

John.

[former_member183607]

Hi,

    Also Check Your Internal table name  TT_CAG or IT_CAG???

[raymond_giuseppi]

Use a DISTINCT

SELECT DISTINCT /bic/r0cagid cms_grrt cms_casval
      INTO TABLE it_cag
      FROM /bic/mr0cagid
      WHERE cms_cagtyp IN ('030600', '030200', '030601')
        AND objvers = 'A'.

Regards,

Raymond

[former_member206475]

Hi,

I removed teh table declaration with unique key because the master data table was loaded only with unique values so it was not necessary to sort and declare unique.

Then the code worked.

Thank you.

assigning points.