Getting different output when selecting dates and check in null values

[darktiger7]

Hello All,

I am trying to create a report using Power BI Builder

i want to run the report with selecting dates and without dates.

issue with the Submission Tracker, reported by one of the end users Rais Quak. Please find the attached reports, the report from September has more than 23,000 rows, but the report that was taken yesterday has 12,000 rows only.

Any help to get common output from both way would be appreciated.

Oracle SQL query attached for your reference.

select

'Submission Tracker' as "REPORT_TITLE",
trunc(sysdate)as "SYSDATE",
p.sponsor_name,
p.protocol_no,
p.pra_study_id,
initcap(r.region)region,
vm.recipient_type,
(select to_nchar(a.account_name) from pradw.dw_ctms_account a, pradw.dw_ctms_submission su where su.account_key = a.account_key
and su.ctms_subm_id = vm.record_id)recipient,
replace(replace(vm.description,chr(10),' '),chr(13),' ') sub_desc,
vm.sub_type as sub_type,
vm.status,
trunc(vm.pln_dt) pln_dt,
trunc(vm.act_dt)act_dt,
trunc(vm.trg_dt)trg_dt,
vm.record_id,
case when vm.site_key <> 0 then 'SITE LEVEL'
when vm.site_key = 0 and vm.region_key <> 0 then 'REGION LEVEL'
when vm.region_key = 0 and vm.protocol_key <> 0 then 'PROTOCOL LEVEL' end sub_level,
dbms_lob.substr((select
listagg(initcap(re.region), ', ') within group(order by re.region) as region_list
from pradw.dw_ctms_subm_reg_site rs, pradw.dw_ctms_region re where rs.rec_scope ='REGION' and rs.ctms_subm_id = vm.record_id
and rs.region_key = re.region_key)) assoc_reg,
dbms_lob.substr((select
listagg((se.site_no), ', ') within group(order by se.site_no) as site_list
from pradw.dw_ctms_subm_reg_site rs, pradw.dw_ctms_site se where rs.rec_scope ='SITE' and rs.ctms_subm_id = vm.record_id
and rs.site_key = se.site_key)) assoc_sites,
round(((vm.pln_dt - vm.act_dt) / 7), 2) moh_rvw_wks,
s.site_no,
s.pi_full_name,
(select trunc(s.renew_prog_rpt_due_dt) from pradw.dw_ctms_submission s where s.ctms_subm_id = vm.record_id) renew_prog_rpt_due_dt,
(select trunc(s.renew_prog_rpt_cmpltd_dt) from pradw.dw_ctms_submission s where s.ctms_subm_id = vm.record_id) renew_prog_rpt_cmpltd_dt
from
pra_prn_ide.ide_ctms_protocol_filter p, --KM updated from pradw.dw_ctms_protocol p,
pradw.dw_ctms_region r,
pradw.dw_ctms_site s,
--pra_prn_ide.ide_ctms_site_filter p,
vm
where vm.protocol_key = p.src_protocol_key --KM updated from p.protocol_key
and vm.region_key = r.region_key
and (p.protocol_no in
(select column_value from table(cast(pradw.vsplit((:i_protocol_no), ',') as pradw.split_tbl)))
or lower(:i_protocol_no) = 'all')
and p.cns_src_nm = 'CTMS' --KM added
and vm.site_key = s.site_key
and (vm.status in
(select column_value from table(cast(pradw.vsplit((:i_sub_status), ',') as pradw.split_tbl)))
or lower(:i_sub_status) = 'all')
and (sub_type in
(select column_value from table(cast(pradw.vsplit((:i_subtype), ',') as pradw.split_tbl)))
or lower(:i_subtype) = 'all')
and (p.sponsor_name in
(select column_value from table(cast(pradw.vsplit((:i_sponsor), ',') as pradw.split_tbl)))
or lower(:i_sponsor) = 'all')
and (trunc(vm.act_dt) >= to_date(:i_actual_dt_from) OR to_date(:i_actual_dt_from) is NULL)
and (trunc(vm.act_dt) <= to_date(:i_actual_dt_to) OR to_date(:i_actual_dt_to) is NULL)

and p.protocol_status in ('In Progress')
and p.protocol_type!='Consulting Project Only'
order by
p.protocol_no,
r.region,
nvl(vm.pln_dt,vm.act_dt)

[Anonymous · Accepted solution]

Hi @darktiger7 ,

Thanks for reaching out to us with your problem. According to your description, it seems like you’re experiencing discrepancies in the number of rows returned by your Oracle SQL query when you select dates and when you don’t. Please check if the parameter :i_actual_dt_from and :i_actual_dt_to has been set the suitable dates... If both of them are NULL, then the following conditions will always true. It will return the rows with unlimited dates. 

and (trunc(vm.act_dt) >= to_date(:i_actual_dt_from) OR to_date(:i_actual_dt_from) is NULL)
and (trunc(vm.act_dt) <= to_date(:i_actual_dt_to) OR to_date(:i_actual_dt_to) is NULL)

---

Best Regards