Blitz Report LOV SQL Validation

Description

Categories: Enginatics
Repository: Github

Validates Blitz Report LOV SQLs for valid syntax.
This can be useful after mass migrating reports from other tools such as Discoverer, Excl4apps, splashBI or Polaris Reporting Workbench into Blitz Report.

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

select
z.*
from
(
select
y.*,
nvl2(y.error_message,'Error','Valid') validation_result
from
(
select
x.lov_name,
x.report_name,
x.parameter_name,
xxen_report.validate_sql(replace(x.lov_query,':$flex$.',':'),'parse') error_message,
x.lov_query
from
(
select
xrpl.lov_name,
null report_name,
null parameter_name,
xrpl.lov_query
from
xxen_report_parameter_lovs xrpl
where
1=1
union all
select
null lov_name,
xrpv.report_name,
xrpv.parameter_name,
xrpv.lov_query
from
xxen_report_parameters_v xrpv
where
2=2 and
xrpv.parameter_type='LOV' and
xrpv.lov_id is null
) x
) y
) z
where
3=3
order by
z.lov_name

Parameter Name

SQL text

Validation

LOV Name like

lower(xrpl.lov_name) like lower(:lov_name_like)

LOV

Category

xrpl.lov_id in (
select
xrp.lov_id
from
xxen_report_categories_v xrcv,
xxen_report_category_assigns xrca,
xxen_report_parameters xrp
where
xrcv.category=:category and
xrcv.category_id=xrca.category_id and
xrca.report_id=xrp.report_id and
xrp.parameter_type='LOV'
)

LOV

Report Name like

xrpl.lov_id in (select xrpv.lov_id from xxen_report_parameters_v xrpv where lower(xrpv.report_name) like lower(:report_name_like) and xrpv.parameter_type='LOV')