Bug in Query Builder with PIVOT clause

blama

Hello Data Modeler Team,

I just found a bug in the Query Builder (Data Modeler 3.3.0.747)

Please enter following SELECT in a new view and hit "Update Diagram".

WITH basis AS(

    SELECT 'setting1' AS settingname, 'a' AS myval FROM DUAL UNION ALL

    SELECT 'setting2' AS settingname, 'b' AS myval FROM DUAL UNION ALL

    SELECT 'setting3' AS settingname, 'c' AS myval FROM DUAL UNION ALL

    SELECT 'unusedsetting3' AS settingname, '11' AS myval FROM DUAL

),

myfaketable AS(

SELECT * FROM basis WHERE settingname LIKE 'setting%'

)

SELECT setting1, setting2, setting3 FROM myfaketable

PIVOT  (MAX(myval) FOR (settingname) IN ('setting1' AS setting1, 'setting2' AS setting2, 'setting3' AS setting3));

I'm using PIVOT to get my settings flat as one row, even though they are saved as name-value pairs.

The alternative would be sth. like this, which accesses the base table "myfaketable" way to often:

WITH basis AS(

    SELECT 'setting1' AS settingname, 'a' AS myval FROM DUAL UNION ALL

    SELECT 'setting2' AS settingname, 'b' AS myval FROM DUAL UNION ALL

    SELECT 'setting3' AS settingname, 'c' AS myval FROM DUAL UNION ALL

    SELECT 'unusedsetting3' AS settingname, '11' AS myval FROM DUAL

),

myfaketable AS(

    SELECT * FROM basis WHERE settingname LIKE 'setting%'

)

SELECT * FROM

    (SELECT myval AS setting1 FROM myfaketable WHERE settingname='setting1')

    CROSS JOIN (SELECT myval AS setting2 FROM myfaketable WHERE settingname='setting2')

    CROSS JOIN (SELECT myval AS setting3 FROM myfaketable WHERE settingname='setting3');

So basically the error report is that the parser does not know about the PIVOT clause.

Thank you & Best regards,

Blama

Philip Stoyanov-Oracle

Hi Blama,

thanks for reporting the problem. I logged a bug for that.

Philip

blama

Hi,

I just wanted to let you know that the problem persists in 4.0.0.833.

Best regards,

Blama