How can I write this given native sql into open sql in abap

[former_member789989]

Hi,

can any one tell me how can i write the below mentioned native sql query in open sql query in abap?

select * from  table1_name z

where STATUS='C'

and (menge <>0 or res_menge<>0)

and z.LGNUM='101'

and z.LGTYP in ('Y02','Y03', 'Y04', 'Y05', 'Y26', 'Y27')

and not exists

(select 1 from table2_name q

where z.LGNUM=q.LGNUM

and z.MATNR=q.MATNR

and z.LGTYP=q.LGTYP

and z.LGPLA=q.LGPLA

and z.CHARG=q.CHARG

and (substr(q.SONUM,13,4))=(substr(z.ZZPALLET_CODE,15,4))

);

Regards

[mtremblay-savard]

Hi Muhammad,

First, you cannot use substrings in open SQL statements, refer to this link for more explanations

Second, you need a range for your LGTYP. It should look like this :

RANGES: r_field for table1_name-lgtyp.

r_field-sign = 'I'.

r_field-option = 'EQ'.

r_field-option-low = 'Y02'.

append r_field.

r_field-sign = 'I'.

r_field-option = 'EQ'.

r_field-option-low = 'Y03'.

append r_field.

r_field-sign = 'I'.

r_field-option = 'EQ'.

r_field-option-low = 'Y04'.

append r_field.

r_field-sign = 'I'.

r_field-option = 'EQ'.

r_field-option-low = 'Y05'.

append r_field.

r_field-sign = 'I'.

r_field-option = 'EQ'.

r_field-option-low = 'Y26'.

append r_field.

r_field-sign = 'I'.

r_field-option = 'EQ'.

r_field-option-low = 'Y27'.

append r_field.

Then, the open sql statement would look like :

select *

     into lt_table1

     from  table1_name as z

     where STATUS='C'

          and (menge <>0 or res_menge<>0)

          and z.LGNUM='101'

          and z.LGTYP in r_field.

select *

into lt_table2

from table2_name q

for all entries in lt_table1 as z

where z.LGNUM=q.LGNUM

     and z.MATNR=q.MATNR

     and z.LGTYP=q.LGTYP

     and z.LGPLA=q.LGPLA

     and z.CHARG=q.CHARG ).

Then, you need to verify for this part (substr(q.SONUM,13,4))=(substr(z.ZZPALLET_CODE,15,4)

data:

     l_tabix type sy-tabix,

     l_substring1 type string,

     l_substring2 type string.

field-symbols:

     <table1> type table1_name,

     <table2> type table2_name.

loop at lt_table1 assigning <table1>.

     l_tabix = sy-tabix.

     read table lt_table2 assigning <table2> with key

                  LGNUM=<table1>.LGNUM

          and MATNR=<table1>.MATNR

          and LGTYP=<table1>.LGTYP

          and LGPLA=<table1>.LGPLA

          and CHARG=<table1>.CHARG.

     check sy-subrc = 0. "If not found, continue

     l_substring1 = <table1>-zzpallet_code+4(15).

     l_substring2 = <table2>-sonum+4(13).

     if l_substring1 = l_substring2.

          delete lt_table1 index l_tabix.

     endif.

endloop.

The reason for doing two selects and then looping is your substring part, if it wasn't for you substring, you could simply do :

select *

     into lt_table1

     from  table1_name as z

     where STATUS='C'

          and (menge <>0 or res_menge<>0)

          and z.LGNUM='101'

          and z.LGTYP in r_field

          and not exist (

               select *

                    from table2_name q

                    where z.LGNUM=q.LGNUM

                         and z.MATNR=q.MATNR

                         and z.LGTYP=q.LGTYP

                         and z.LGPLA=q.LGPLA

                         and z.CHARG=q.CHARG ).

I hope this will help!

Max

[rosenberg_eitan]

Hi,

Why not use native SQL .

See http://scn.sap.com/community/abap/blog/2013/12/26/sdbadbc--the-program

Regards.

[Former Member]

Hi Muhammad.

I haven't been able to test my code. But attached is a script containing some code that you could use or help you get started.

Remember to declare the internal tables & field symbols.

Regards.