Hi,

I've got a complex Oracle-SQL-statement and I want to convert it to DB/
2:

INSERT INTO TBZL2000CSFLQ_AGG(
Solve_Name,
Scenario,
Korrektur_KZ,
EventDate,
EventCurrency,
CLI,
FXB,
RefNodeID_KAB,
RefNodeID_LIQ,
RefNodeID_ZAB,
TBS1_Name,
TBS1_Start,
TBS1_End,
TBS2_Name,
TBS2_Start,
TBS2_End,
Cashflow_Sign
) SELECT
MC.type,
NVL(MC.scenario, 0),
'M',
CASE WHEN MC.fixed_date IS NULL
THEN DateAdd([Reportdatum], "d", MC.rel_shift)
ELSE MC.fixed_date
END,
MC.ccy,
MC.amount,
FX.quoted_price_fx,
CASE WHEN MC.type = 'KAB' THEN ET.nodeid ELSE NULL END,
CASE WHEN MC.type = 'LIQ' THEN ET.nodeid ELSE NULL END,
CASE WHEN MC.type = 'ZAB' THEN ET.nodeid ELSE NULL END,
CASE WHEN MC.type = 'ZAB' THEN 'BII' ELSE 'STD' END AS tbs1_name,
CASE WHEN MC.type = 'ZAB' THEN NVL(tb1.tbs_start, (SELECT
MAX(tbs_start) FROM TBZL0318TBS_AGG WHERE tbs_name = 'BII')) ELSE
NVL(tb1.tbs_start, (SELECT MAX(tbs_start) FROM TBZL0318TBS_AGG WHERE
tbs_name = 'STD')) END AS tbs1_start,
CASE WHEN MC.type = 'ZAB' THEN NVL(tb1.tbs_start, (SELECT
MAX(tbs_start) FROM TBZL0318TBS_AGG WHERE tbs_name = 'BII')) ELSE
NVL(tb1.tbs_end, (SELECT MAX(tbs_end) FROM TBZL0318TBS_AGG WHERE
tbs_name = 'STD')) END AS tbs1_end,
CASE WHEN MC.type = 'ZAB' THEN NULL ELSE '180D' END AS tbs2_name,
CASE WHEN MC.type = 'ZAB' THEN NULL ELSE NVL(tb2.tbs_start, (SELECT
MAX(tbs_start) FROM TBZL0318TBS_AGG WHERE tbs_name = '180D')) END AS
tbs2_start,
CASE WHEN MC.type = 'ZAB' THEN NULL ELSE NVL(tb2.tbs_end, (SELECT
MAX(tbs_end) FROM TBZL0318TBS_AGG WHERE tbs_name = '180D')) END AS
tbs2_end
CASE WHEN MC.amount < 0 THEN '-' ELSE '+' END AS cashflow_sign
FROM
TBZL2011PAR_MANCOR AS MC,
(SELECT * FROM TBZL0318TBS_AGG
WHERE tbs_name = CASE WHEN MC.type = 'ZAB' THEN 'BII' ELSE 'STD' END)
AS TB1,
(SELECT * FROM TBZL0318TBS_AGG
WHERE tbs_name = CASE WHEN MC.type = 'ZAB' THEN 'BII' ELSE '180D' END)
AS TB2,
HY_ENTITY_TREE AS ET,
FX_RATE_OBSERVATION AS FX
WHERE
CASE WHEN MC.fixed_date IS NULL
THEN DateAdd([Reportdatum], "d", MC.rel_shift)
ELSE MC.fixed_date
END = TB1.tbs_date (+) AND
CASE WHEN MC.fixed_date IS NULL
THEN DateAdd([Reportdatum], "d", MC.rel_shift)
ELSE MC.fixed_date
END = TB2.tbs_date (+) AND
MC.type = ET.entityname AND
MC.mnemonic = ET.fullmnemonic AND
MC.delete_time IS NULL AND
MC.valid_from >= Laufdatum (Globalvariable aus Jobsteuerung) AND
MC.ccy = substr(FX.market_object_code,5,3)

Any hints?

Thank you in advance,
Sascha

Sascha,

I see only two issues:
1. Replace all occurances of NVL with COALESCE
That should be trivial.
2. Strip the (+) boolean factors and replace the FROM clause with (also
fix DATEADD):
FROM TBZL2011PAR_MANCOR AS MC
LEFT OUTER JOIN LATERAL
(SELECT * FROM TBZL0318TBS_AGG
WHERE tbs_name = CASE WHEN MC.type = 'ZAB' THEN 'BII' ELSE 'STD' END)
AS TB1
ON CASE WHEN MC.fixed_date IS NULL
THEN [Reportdatum] + MC.rel_shift DAYS
ELSE MC.fixed_date
END = TB1.tbs_date
LEFT OUTER JOIN LATERAL
(SELECT * FROM TBZL0318TBS_AGG
WHERE tbs_name = CASE WHEN MC.type = 'ZAB' THEN 'BII' ELSE '180D' END)
AS TB2
ON CASE WHEN MC.fixed_date IS NULL
THEN [Reportdatum] + MC.rel_shift DAYS
ELSE MC.fixed_date
END = TB2.tbs_date

I don't know what [ReportDatum] means in brackets...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Great, thank you, I forgot LATERAL ...

Regards,
Sascha

On Jun 15, 2:51 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> Sascha,
>
> I see only two issues:
> 1. Replace all occurances of NVL with COALESCE
> That should be trivial.
> 2. Strip the (+) boolean factors and replace the FROM clause with (also
> fix DATEADD):
> FROM TBZL2011PAR_MANCOR AS MC
> LEFT OUTER JOIN LATERAL
> (SELECT * FROM TBZL0318TBS_AGG
> WHERE tbs_name = CASE WHEN MC.type = 'ZAB' THEN 'BII' ELSE 'STD' END)
> AS TB1
> ON CASE WHEN MC.fixed_date IS NULL
> THEN [Reportdatum] + MC.rel_shift DAYS
> ELSE MC.fixed_date
> END = TB1.tbs_date
> LEFT OUTER JOIN LATERAL
> (SELECT * FROM TBZL0318TBS_AGG
> WHERE tbs_name = CASE WHEN MC.type = 'ZAB' THEN 'BII' ELSE '180D' END)
> AS TB2
> ON CASE WHEN MC.fixed_date IS NULL
> THEN [Reportdatum] + MC.rel_shift DAYS
> ELSE MC.fixed_date
> END = TB2.tbs_date
>
> I don't know what [ReportDatum] means in brackets...
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Then again, you can just go here:
http://www-306.ibm.com/software/data.../openbeta.html
And all you need to do is fix the date arithmetic :-)

I'll post shortly on some stuff you may want to know.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab