Hi,
We have a process that runs hourly and processes a certain amount of records across a set of different business objects. I am trying to write a small view to see the total records and number of objects processed daily.
SELECT
TO_CHAR(PL1.process_start_date, 'YYYY-MM-DD') log_date,
PL1.process_id,
SUM(PL1.ods_count) rec_count,
(
SELECT COUNT(plmwh_type_reln_master_seq_id) obj_count
FROM process_log PL2
WHERE TO_CHAR(PL2.process_start_date,'YYYY-MM-DD') = TO_CHAR(PL1.process_start_date,'YYYY-MM-DD')
) sub
FROM process_log PL1
GROUP BY TO_CHAR(PL1.process_start_date,'YYYY-MM-DD'), process_id
;
Is there a way to add the subquery to the group by? Because if I don't, I get a 'not a GROUP BY expression' error. If I do add sub to the group by I get 'sub: invalid identifier'
Here's a more generic example:
SELECT
tbls.owner,
tbls.table_name,
(select COUNT(column_name) as total_columns
from all_tab_columns cols
where cols.owner = tbls.owner
AND cols.table_name = tbls.table_name) subquery2
FROM all_tables tbls
group by tbls.owner, tbls.table_name, subquery2;
Remove the group by and it will run fine.