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

former_member789989 — Wed, 09 Apr 2014 19:20:14 GMT

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

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

mtremblay-savard — Thu, 10 Apr 2014 15:29:54 GMT

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

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

rosenberg_eitan — Thu, 10 Apr 2014 16:23:24 GMT

Hi,

Why not use native SQL .

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

Regards.

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

Former Member — Fri, 11 Apr 2014 13:13:15 GMT

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.

topic Re: How can I write this given native sql into open sql in abap in Application Development and Automation Discussions

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

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

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

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