Home » SQL & PL/SQL » SQL & PL/SQL » Help reverse engineering pl sql query (Oracle sql)
| Help reverse engineering pl sql query [message #654781] |
Fri, 12 August 2016 10:15  |
 |
jprisk
Messages: 1
Registered: August 2016
|
Junior Member |
|
|
Hello Everyone,
I'm wondering if someone could help me understand the steps that the following query is taking. I'm trying to rebuild the output of this query using Vrealize Suite. A gentlemen from VmWare wrote this query before my time at my company. This query was built to rollup ledger data into a YTD format. It utilizes 3 tables and im just unsure of the steps/formulas it is using to bring these tables together. I'm new to understanding pl sql and any help on this would be greatly appreciated.
SELECT FUNDING,
BOARD,
GL.PROJECT PROJECT_ID,
gl.Sf_Dim10110_Mapping,
GL.ORG,
TO_SF_DATE(TO_DATE(GL.YEAR, 'yyyy')) AS YEAR,
GL.START_TIME,
case when CP.YEAR is not null then GL.AMOUNT else 0 end CURR_YEAR_AMOUNT,
GL.AMOUNT,
SUM(GL.AMOUNT) OVER(PARTITION BY gl.PROJECT,dept,GL.YEAR,SF_DIM10064_MAPPING,SF_DIM10_MAPPING,gl.Sf_Dim10110_Mapping ORDER BY GL.START_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) YTD_SPENT,
GL.DEPT,
GL.SF_DIM10_MAPPING,
GL.SF_DIM10064_MAPPING,
CP.YEAR IS_CLOSED_YEAR
FROM (QBE_GL_ALL_MONTH) GL
LEFT JOIN complete_project_list PT ON GL.PROJECT = PT.PROJECT_ID
LEFT JOIN (select * from wf_gl_period_closed where CLOSED=1) CP ON TN(ADD_MONTHS(nt(CP.YEAR),CP.PERIOD-1)) = GL.START_TIME
|
|
|
|
| Re: Help reverse engineering pl sql query [message #654782 is a reply to message #654781] |
Fri, 12 August 2016 10:27  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Welcome to this forum
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
SELECT funding,
board,
gl.project project_id,
gl.sf_dim10110_mapping,
gl.org,
To_sf_date(To_date(gl.year, 'yyyy')) AS year,
gl.start_time,
CASE
WHEN cp.year IS NOT NULL THEN gl.amount
ELSE 0
END curr_year_amount,
gl.amount,
sum(gl.amount) OVER(partition BY gl.project,dept,gl.year,sf_dim10064_mapping,sf_dim10_mapping,gl.sf_dim10110_mapping ORDER BY gl.start_time rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) ytd_spent,
gl.dept,
gl.sf_dim10_mapping,
gl.sf_dim10064_mapping,
cp.year is_closed_year
FROM (qbe_gl_all_month) gl
LEFT JOIN complete_project_list pt
ON gl.project = pt.project_id
LEFT JOIN
(
SELECT *
FROM wf_gl_period_closed
WHERE closed=1) cp
ON tn(add_months(nt(cp.year),cp.period-1)) = gl.start_time
|
|
|
|
|
|
|
|
| Re: Help reverse engineering pl sql query [message #654790 is a reply to message #654782] |
Fri, 12 August 2016 15:03 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following is an attempt to explain things from top to bottom:
It SELECTs a bunch of columns from three tables.
Some of the columns are listed by themselves, like funding and board. Some of the columns are prefaced with table aliases, like g1.project and g1.gl.sf_dim10110_mapping, where g1 is the table alias for the qbe_gl_all_month table. Table aliases are necessary where different tables have the same column name, in order to resolve ambiguity. They are otherwise optional.
There are also some column aliases. For example, project_id is a column alias for g1.project. The column alias is the heading for that column that will appear in the output.
Some of the columns have functions applied to them. For example, the Oracle supplied TO_DATE function accepts a value to be converted and a format that matches that value and converts it to a date. So, if the value of g1.year (the year column in the qbe_gl_all_month table with g1 as the table alias) is 2016, then TO_DATE(g1.year,'yyyy') would return the first day of the current month for the year 2016, as shown below.
SCOTT@orcl_12.1.0.2.0> select to_date('2016','yyyy') from dual;
TO_DATE('2016',
---------------
Mon 01-Aug-2016
1 row selected.
To_sf_date must be a function, but is not an Oracle supplied function. It apparently accepts a date and converts it to something. Only you can determine what the user-defined sf_date function returns. You should be able to tell that by viewing the output for that column that has a column alias of year.
The CASE statement:
CASE WHEN cp.year IS NOT NULL THEN gl.amount ELSE 0 END curr_year_amount
says when the year column of the inline view with the alias of cp that selects from the wf_gl_period_closed table is not null then select the amount column from the qbe_gl_all_month table aliased by g1 otherwise select 0 and label that column using the column alias of curr_year_amount.
The following is an Oracle analytic function, followed by the column alias ytd_spent:
sum(gl.amount) OVER(partition BY gl.project,dept,gl.year,sf_dim10064_mapping,sf_dim10_mapping,gl.sf_dim10110_mapping ORDER BY gl.start_time rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) ytd_spent
The above analytic function produces a rolling total for each group of project, dept, year, sf_dim10046_mapping, sf_dim10_mapping, and sf_dim10110_mapping. The rows within each group are displayed in the order of start_time with the value of amount in each row being added to the previous rolling total, until the next group, where it starts again.
All of these columns are selected from the three tables that are joined on the columns that they have in common. The following join:
FROM (qbe_gl_all_month) gl
LEFT JOIN complete_project_list pt
ON gl.project = pt.project_id
means that the qbe_gl_all_month table with a table alias of g1 is joined to the complete_project_list table with a table alias of pt in such a manner (LEFT JOIN) that there may be values for g1.project where there aren't any corresponding values from pt.project_id.
The previous is then similarly joined to an inline view (a subquery in the from clause that is used the same as a table) that selects from the wf_gl_period_closed restricting the rows from that select to those where the value of the closed column is 1. That inline view has an alias of cp, just as the other tables have been assigned aliases for the purpose of using shorter references to table names and column names and eliminating ambiguity.
LEFT JOIN
(
SELECT *
FROM wf_gl_period_closed
WHERE closed=1) cp
ON tn(add_months(nt(cp.year),cp.period-1)) = gl.start_time
The final join condition above indicates that the tables are joined by comparing the start_time of the qbe_gl_all_month table aliased by g1 to some functions applied to the year and period columns of the inline view aliased as cp that select from the wf_gl_period_closed table. The add_months function is an Oracle supplied function that adds the number of months represented by the numerical value in the second parameter to the date value in the first parameter. The tn function and nt function are apparently user-defined functions on your system, so will you have to figure out what they do, based on viewing the output.
To view the source code of your user-defined functions, you can use:
select name, text
from user_source
where name in ('TO_SF_DATE', 'TN', 'NT')
order by name, line;
If needed, you can post the output of the above query for explanation of what those functions do.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 10:16:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
