#5 cursor conversion in kopi syntax

[naim]

in the original inp file, a cursor was like that:

DEFINE BLOCK
NAME = all_auft
TABLE = tmp_mwba130
ROWS_DISPLAYED = 5
ROWS_BUFFERED = 12
BASE_LINE = 4
LINES_PER_ROW = 3
ARRAY_SIZE = 0
ORDERING = <<<
where 1 = 2
union
select bas.auftragnr, bas.posnr,
max(farben.bezeichnung),
max(pos.farbe_dsgn),
max(substr(pos.form_art,1,20)),
max(bas.liefartnr),
max(bas.saison),
max(bas.datum_we),
sum(nvl(bew.stk_umsatz_akt_s,0)),
sum(nvl(bew.stueck_bestand,0)),
round(to_number(
decode(sum(nvl(bew.stk_umsatz_akt_s,0))+
sum(nvl(bew.stueck_bestand,0)),0,0,
(sum(nvl(bew.stk_umsatz_akt_s,0)) /
(sum(nvl(bew.stk_umsatz_akt_s,0)) +
sum(nvl(bew.stueck_bestand,0))) * 100 ))),1),
chartorowid('0')
from bestand_bew bew,
bestand_bas bas,
farben,
position pos
where bas.untnr = bew.untnr
and bas.identnr = bew.identnr
and bas.auftragnr = pos.auftragnr (+)
and bas.posnr = pos.posnr (+)
and bas.farbencode = farben.farbencode (+)
and bas.liefartnr like :eingabe.liefartnr
and bas.liefnr = :eingabe.liefnr
and bas.artikelnr = :eingabe.artnr
and bas.untgrnr = :kopf.untgrnr
group by bas.auftragnr, bas.posnr
>>>

=======================================================

=======================================================

The conversion made y tanit gave the folowing:

#sql {
SELECT anonymous
FROM (SELECT anonymous
FROM tmp_mwba130 t0
WHERE 1 = 2
UNION SELECT bas.auftragnr AS auftragnr,
bas.posnr AS posnr,
MAX(farben.bezeichnung) AS elem3,
MAX(pos.farbe_dsgn) AS elem4,
MAX({fn SUBSTR(pos.form_art, 1, 20)}) AS elem5,
MAX(bas.liefartnr) AS elem6,
MAX(bas.saison) AS elem7,
MAX(bas.datum_we) AS elem8,
SUM({fn NVL(bew.stk_umsatz_akt_s, 0)}) AS elem9,
SUM({fn NVL(bew.stueck_bestand, 0)}) AS elem10,
{fn ROUND({fn TO_NUMBER(DECODE(SUM({fn NVL(bew.stk_umsatz_akt_s, 0)}) + SUM({fn NVL(bew.stueck_bestand, 0)}), 0, 0, (SUM({fn NVL(bew.stk_umsatz_akt_s, 0)}) / (SUM({fn NVL(bew.stk_umsatz_akt_s, 0)}) + SUM({fn NVL(bew.stueck_bestand, 0)})) * 100)))}, 1)} AS elem11,
{fn CHARTOROWID('0')} AS elem12
FROM tmp_mwba130 t0
WHERE bas.untnr = bew.untnr
AND bas.identnr = bew.identnr
AND bas.auftragnr = pos.auftragnr(+)
AND bas.posnr = pos.posnr(+)
AND bas.farbencode = farben.farbencode(+)
AND bas.liefartnr LIKE :(@EINGABE.LIEFARTNR)
AND bas.liefnr = :(@EINGABE.LIEFNR)
AND bas.artikelnr = :(@EINGABE.ARTNR)
AND bas.untgrnr = :(@KOPF.UNTGRNR)
GROUP BY bas.auftragnr, bas.posnr
)
};

The tables from which data is selected were wrongly mapped
* tmp_mwba130 t0
instead of
* bestand_bew bew,bestand_bas bas, farben, position pos