Hi Friends,
Our database version is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Below is the query I am trying to execute and I have also attached excel containing the query output.
My doubt is on the value returned in the columns max_dt_corr_sub_query, max_dt_rneq1 and max_dt_hard_code.
For column max_dt_rneq1, I took the same query as that of max_dt_corr_sub_query and had rownum <= 1.
For column max_dt_hard_code, I took the same query as that of max_dt_corr_sub_query and hardcoded the values (as it is the same values for every record) making it a subquery.
I am expecting the values of these 3 columns to be the same but Oracle is returning a different value for the column max_dt_corr_sub_query.
Can someone explain why Oracle is returning a different value in this case? Please help me in understanding what I am missing here.
Note: In the output that I attached, the value of p1_st_dt is the same for the entire result set and hence I have hardcoded values in the query for the max_dt_hard_code column.
Query:
/* Formatted on 4/30/2019 11:18:54 (QP5 v5.115.810.9015) */
SELECT p1.period_type,
p1.start_date p1_st_dt,
p1.end_date p1_ed_dt,
p2.start_date p2_st_dt,
p2.end_date p2_ed_dt,
p1.period_set_name p1_psn,
p2.period_year p2_yr,
p2.period_num p2_pn,
r.TYPE,
r.frequency,
r.occurrences,
(SELECT MAX (p9.end_date)
FROM gl.gl_periods p9
WHERE p9.period_set_name = p1.period_set_name
AND p9.period_type = p1.period_type
AND (p9.start_date >= p1.start_date)
AND p9.adjustment_period_flag = 'N'
AND ROWNUM <=
(DECODE (r.TYPE,
'ACC_DUR',
NVL (:accounting_rule_duration, 1),
'PP_DR_ALL',
NVL (:accounting_rule_duration, 1),
'PP_DR_PP',
NVL (:accounting_rule_duration, 1),
r.occurrences)))
max_dt_corr_sub_query,
(SELECT p9.end_date
FROM gl.gl_periods p9
WHERE p9.period_set_name = p1.period_set_name
AND p9.period_type = p1.period_type
AND (p9.start_date >= p1.start_date)
AND p9.adjustment_period_flag = 'N'
AND ROWNUM <= 1
) max_dt_rneq1,
(SELECT MAX (p9.end_date)
FROM gl.gl_periods p9
WHERE p9.period_set_name = 'MSI CALENDAR'
AND p9.period_type = 21
AND p9.start_date >= '27-APR-2019'
AND p9.adjustment_period_flag = 'N'
AND ROWNUM <= (DECODE (r.TYPE,
'ACC_DUR',
NVL (:accounting_rule_duration, 1),
'PP_DR_ALL',
NVL (:accounting_rule_duration, 1),
'PP_DR_PP',
NVL (:accounting_rule_duration, 1),
r.occurrences))
)max_dt_hard_code
FROM gl_periods p1, /* to get the first period */
gl_periods p2, /* to get the last period */
gl_sets_of_books b,
gl_period_types t,
ra_rules r
WHERE r.rule_id = :accounting_rule_id
AND b.set_of_books_id = :sob_id
AND:rule_start_date BETWEEN p1.start_date AND p1.end_date
AND r.frequency <> 'SPECIFIC'
AND p1.period_set_name = b.period_set_name
AND p1.adjustment_period_flag = 'N'
AND p1.period_type = r.frequency
AND t.period_type = p1.period_type
/*p2 joins */
AND p2.adjustment_period_flag = 'N'
AND p2.period_set_name = b.period_set_name
AND p2.period_type = p1.period_type;
Query Output:
Please find the attached excel sheet.