When using a SQL statement written over a table to define the underlying data source, all the columns of the underlying table are available on the widget filter tab.
When using a SQL statement written over a view to define the underlying data source, only the result columns presented by the view are available on the widget filter tab even though other columns are available on the view.
Is this expected behavior?
Code:
SELECT year(x.dtaccount) AS fiscalyear, decimal( sum( CASE WHEN bondclass = 'C' THEN x.ampremtrn ELSE 0 END ), 13, 2 ) AS contract, decimal( sum( CASE WHEN bondclass = 'M' THEN x.ampremtrn ELSE 0 END ), 13, 2 ) AS commercial, decimal( sum( CASE WHEN bondclass = 'S' THEN x.ampremtrn ELSE 0 END ), 13, 2 ) AS subdivision FROM iftovr.fmrm000 AS x JOIN iftovr.bondlobclassification2 AS c ON ( x.cdbranch, x.cdcarrier, x.cdprogram, x.nubond, x.nubondre, x.nubondenpr ) = ( c.cdbranch, c.cdcarrier, c.cdprogram, c.nubond, c.nubondre, c.nubonden ) WHERE x.cdcarrier in ('IF') and x.cdtrans in ('PR', 'SC') and year(x.dtaccount) in ('2014', '2015', '2016', '2017', '2018') GROUP BY year(x.dtaccount) ORDER BY year(x.dtaccount)
Code:
SELECT fiscalyear, decimal( sum( CASE WHEN bondclass = 'C' THEN ampremtrn ELSE 0 END ), 13, 2 ) AS contract, decimal( sum( CASE WHEN bondclass = 'M' THEN ampremtrn ELSE 0 END ), 13, 2 ) AS commercial, decimal( sum( CASE WHEN bondclass = 'S' THEN ampremtrn ELSE 0 END ), 13, 2 ) AS subdivision, decimal( sum( CASE WHEN bondclass = 'X' THEN ampremtrn ELSE 0 END ), 13, 2 ) AS specialty FROM jjcwork.bondtransactionhistoryclassified_combined GROUP BY fiscalyear