Solved: Translate Informix Select-Statement

StefanReitz · ‎10-15-2008

Dear Members,

i need to translate an Exec-SQL Statement into ABAP.

The following SQL Statement selects data from an Informix Database.

EXEC SQL PERFORMING append_einzelteilbew.
select k.vkorg, k.kunnr, k.adr1 as name1, b.bewnr, trim(b.bewname) || ', ' || trim(b.bewvorname) as bwlas,
a.matnr, a.bezeichnung as maktx, e.barcode
from einzelteil e, einzelteilbewegung eb, artikel a, bewohner b, kunde k
into :wa_out where a.vkorg = :p_vkorg and a.mandt = '301'
and (eb.enter_ts between :z_timevo and :z_timebi)
and a.vkorg = b.vkorg and a.mandt = b.mandt and a.vkorg = e.vkorg
and a.mandt = e.mandt and a.mandt = eb.mandt and a.vkorg = eb.vkorg
and a.mandt = k.mandt and a.vkorg = k.vkorg and k.kunnr = e.kunnr
and k.kunnr = b.kunnr and e.matnr = a.matnr and b.bewnr = e.bewnr
and e.barcode = eb.barcode
group by k.vkorg, k.kunnr, k.adr1, b.bewnr, 5, a.matnr, a.bezeichnung, e.barcode
order by k.kunnr, b.bewnr, a.matnr
ENDEXEC.

While i am at it... this is another Statement i have Problems with.

I already searched the SDN for UNION but i didnt find a fitting answer.

exec sql performing append_stl.

*

select settyp, version, matnr, 'N'
into :wa_stl-settyp,
:wa_stl-version,
:wa_stl-matnr,
:wa_stl-typ
from set_stl
where mandt = :sy-mandt
union
select settyp, version, matnr, 'E'
from ersatzmat
where mandt = :sy-mandt

*

endexec.

Thanks in advance for your help!

SR.

Former Member · ‎10-15-2008

Hi,

In native sql UNION operator is used to combine result of two or more select statement.

exec sql performing append_stl. 
select settyp, version, matnr, 'N' 
into :wa_stl-settyp, 
:wa_stl-version, 
:wa_stl-matnr, 
:wa_stl-typ 
from set_stl 
where mandt = :sy-mandt 
union 
select settyp, version, matnr, 'E' 
from ersatzmat 
where mandt = :sy-mandt 
endexec.

in your case equivalent of above statement is executing below two select statement separately as shown below.....

for the every record retrieved, form append_stl is executed

exec sql performing append_stl. 
select settyp, version, matnr, 'N' 
into :wa_stl-settyp, 
:wa_stl-version, 
:wa_stl-matnr, 
:wa_stl-typ 
from set_stl 
where mandt = :sy-mandt 
endexec.

exec sql performing append_stl. 
select settyp, version, matnr, 'E' 
into :wa_stl-settyp, 
:wa_stl-version, 
:wa_stl-matnr, 
:wa_stl-typ 
from ersatzmat 
where mandt = :sy-mandt 
endexec.

Regards,

Vishal

Edited by: Vishal Jadhav on Oct 15, 2008 4:11 PM

Former Member · ‎10-15-2008

Hi,