Forum Stats

  • 3,854,962 Users
  • 2,264,441 Discussions
  • 7,905,847 Comments

Discussions

Bug in Query Builder with PIVOT clause

blama
blama Member Posts: 123

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

Comments

This discussion has been closed.