Hello,
The below query does not return any value when the value of the counter is equal or greater than 110000000.
NOTE: It works well for 6100 or 19 000 000 as example.
select
to_char(to_number(counter))
from
(select level counter from dual connect by level <= 120000000)
where
counter =(SELECT SUM(peevf.screen_entry_value)
FROM pay_element_entries_f peef,
pay_element_entry_values_f peevf,
pay_input_values_f pivf,
pay_sub_classn_rules_f pscrf,
pay_ele_classifications_tl pect
WHERE peef.element_entry_id = peevf.element_entry_id
AND peef.effective_start_date BETWEEN TO_DATE('01/01/2021','DD/MM/YYYY') AND TO_DATE('31/12/2021','DD/MM/YYYY')
AND peef.person_id = <REPLACE with PersonID>
AND peevf.input_value_id = pivf.input_value_id
AND pivf.base_name = 'Amount'
AND peef.element_type_id = pscrf.element_type_id
AND pscrf.classification_id = pect.classification_id
AND pect.LANGUAGE = 'US'
AND pect.classification_name = 'TOTAL_VARIABLE_COMPENSATION')Is this a limitation from the "select level counter from dual connect by level" ?
NOTE: to replace with personID in the query: <REPLACE with PersonID>
It's unclear exactly what the problem is here - providing any error messages or output you get helps a lot!
That said, I'm guessing you're hitting this problem:
select count(*) from dual connect by level <= 100000000;
ORA-30009: Not enough memory for CONNECT BY operation
i.e. you've hit a memory limit.
You can overcome this by:
Cross joining a smaller data set with itself and limiting the rows:
with rws as (
select level n from dual connect by level <= 1000000
)
select count(*) from rws cross join rws
where rownum <= 100000000;
COUNT(*)
----------
100000000
You could also use recursive WITH instead (this can be much slower than connect by):
with rws (n) as (
select 1 n from dual
union all
select n+1 from rws
where n <= 100000000
)
select count(*) from rws;