I'm attempting to use Discoverer to create a rolling 12-month attrition report. It works fine, up to the point of trying to create a average headcount for each month in the current 12-month period over the last 12 months' each. The problem I'm encountering involves the use of the MIN() function in selecting active employees in each month, mostly due to a data-cleansing issue, which I'd hoped to bypass.
Because some invididuals have two "data conversion" records - i.e., they were converted to the new database and an additional, subsequent record re-used what should have been a unique action reason - I need to test their MIN(position start date) so as to then use their actual start against the first record, whereas I can go on to use their position start to capture their FTE for any subsequent active records.
So I created a calculation, Min Start, to hold the earliest start date for each employee:
MIN(Position.Start.Date) OVER (PARTITION BY Employee.Number ORDER BY Position.Start.Date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
Then I test the status of each position for each month in the report and return the FTE where it tests as active through month-end.
CASE WHEN Position.Action.Reason = 'CNV' THEN
CASE WHEN Position.Start.Date = Min Start THEN
CASE WHEN Hire.Date <= Month.End THEN
CASE WHEN Termination.Date is NULL THEN FTE
[code]...
However, I can't SUM this because nesting isn't permitted and I can't average the sum because Aggregation of Analytic functions is not allowed either. I need a different approach. Data is always going to be dirty, so coding to account for such problems means I can perform my reporting requirements without interruption for clean-ups. (I know that keeping the data clean is best and highlighting such problems brings the attention of managers and staff to rectifying and avoiding such problems, but I still need to get the results out.)
The following sequence of simple statements shows me how Analytics and Predicate Pushing appear to work. But when I go from constant tests to a join with the same data as a row instead of a constant value, the Predicate Pushing stops.
I have a table with 9 million rows in it. It takes about 90 seconds to scan. 13:25:11 SQL> 13:25:11 SQL> select count(*) thecount from lv_pln_usge_fact ; THECOUNT ---------- 8681388
1 row selected.
Elapsed: 00:00:32.28
If I run an analytic that counts all rows in the table I can see that easy enough. This exampe as I understand it, scans the table (I know this because if for no other reason, it take 90 seconds to get an answer), then after scanning all rows, counts them and adds the count to each row. There are in fact 9500 or so rows with the values of lv_rqst shown. Instead of seeing all 9500 rows, I distinct it to get only one. See how the count shows all rows in the table.
From this we see that the predicate is not pushed into the inner query to filter rows. If it had, the analytic would have produced a number like 9500 not 9 million. I have no issue with this as this is how Analytics are documented work.
13:25:43 SQL> select distinct * 13:25:43 2 from ( 13:25:43 3 select LV_RQST 13:25:43 4 ,count(*) over () thecount
[code]..
We can see how filter does happen with an analytic. We push the predicate into the inner query and all of a sudden we get a count of only those rows for the specific request. Thus we see the basics of how analytics work, particularly with respect to predicate pushing. There is not real rocket science here. The filter removed rows before the analytic counted them. This too is how Analytics are documented to work.
13:27:02 SQL> select distinct * 13:27:02 2 from ( 13:27:02 3 select lv_rqst,count(*) over () thecount 13:27:02 4 from lv_pln_usge_fact 13:27:02 5 where lv_rqst = '746780192'
[code]...
So far we have been doing "all rows" analytics. Now we use a PARTITION clause to group the data. Notice the rowcount. It is the count for just the lv_rqst. Do not be fooled. This is because the PARTITION column says to synchronize the analytic count to the data for its associated row. Thus the counts will be grouped by LV_RQST. Whether we had the predicate on the outside of the query or not, for this specific lv_rqst shown we would still get this count. This is not proof that we did any filtering with the predicate lv_rqst = '746780192'.
However, notice the speed of the query. It gets done so fast, that there is no way it is scanning the table. I know there is an index that starts with LV_RQST so I conclude that the index was used which I believe means the predicate was in fact PUSHED into the inner query. Thus I think we did in fact filter the rows to just this single LV_RQST value and we also accessed the table using the predicate as well which became an index range scan instead of full table scan. My biggest point is we were able to use the index to get the data we want, not scan the table and this was made so because we added the LV_RQST column to the OVER clause as part of the PARTITION BY expression.
13:27:03 SQL> select distinct * 13:27:03 2 from ( 13:27:03 3 select LV_RQST 13:27:03 4 ,count(*) over (partition by lv_rqst) thecount
[code]...
Now I add an additional column to the inner query, and an additional predicate against that column to the outer query. We know that 9539 is the count of all rows where LV_RQST='746780192' as we have seen that before above. I also know the data and know about half say Y and half say N for this indicator column. This query gives the right answer. I just put it here are additional demonstration of the way analytics work. It demonstrates that the new predicate is not pushed into the innser query to filter rows. Again this is how Analytics are documented to work.
It used the index to range scan only the rows where LV_RQST='746780192'. So only those predicates that have their columns in the anlytics OVER clause are allowed to be pushed into the query for filtering and accessing purposes.
13:27:03 SQL> col LV_PLN_USGE_DEL_IND format a20 trunc 13:27:03 SQL> select distinct * 13:27:03 2 from ( 13:27:03 3 select LV_RQST
[code]...
This next query shows a little more clarity. If we add the indicator column to our OVER clause then the rowcount changes to be the number of rows where LV_RQST='746780192' AND lv_pln_usge_del_ind = 'N'. So by putting the column into the OVER expression, Oracle decides to push the predicate down into the inner query and filter the data before the analytic count is done. Again this just demonstrates for clarity how it works. I think I am describing it right anyway.
And once again the speed clearly indicates that an index range scan was done using LV_RQST='746780192'. Recall I said there is an index that starts with this column.
NOW WE COME TO MY PROBLEM.Instead of using the constant value '746780192' we are going to create a one column one row table that has this value in it. We are then going to join to the the analytic subquery instead of doing a contant test against it.
Commit complete. Elapsed: 00:00:00.00 This query is in my mind the same query we did before but we loose the use of the index and go back to doing a FULL TABLE SCAN. 13:27:03 SQL> select distinct x.* 13:27:03 2 from ( 13:27:03 3 select LV_RQST
[code]...
For a little bit more clarity, two more queries. Pay attention to how long it takes, and to how the additional joins affect things. Notice, particularly with the last statement, that the join criteria is being pushed into the inner query with the analytics. Otherwise how did it get that count?
So after looking at all this, here is my question: How do I get Oracle the use the index and nested loop join to the table lv_pln_usge_fact. We know Oracle pushes the predicates down when the columns are referenced in the OVER expression because we see that in several places. We also know the CBO can do a nested loop join with index access on LV_RQST because it does it when we use a constant test. But it won't use the index and nested loop when we do a join to a table with the same data, no matter how much rewriting or hinting I do.
I tested this in 9i/10g/11g and got same behavior in all three places.
I have a database containing the following after entering the following sql command
SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES), TITLES.ROYALTY_RATE AS ROYALTLY_RATE, AUTHOR_TITLES.ROYALTY_SHARE AS ROYALTY_SHARE, AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME FROM TITLES, AUTHOR_TITLES, AUTHORS WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID
What I need to do is create a subquery and use Aggregation to list the author receiving the greatest royalties on revenue. so i used the command to get the royalties
SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES), AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME, ((PRICE * SALES) * TITLES.ROYALTY_RATE * AUTHOR_TITLES.ROYALTY_SHARE) AS ROYALTIES FROM TITLES, AUTHOR_TITLES, AUTHORS WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID
So how do I add up the royalties values associated with each author and find the max? for example I add klee hulls's royalties from each book and get 2,123,336.32(doing it by hand on calculator) what is the sql to find the max royalties for each author? P.S the answer should be KLEE HULL with 2,123,336.32
I have a result-set which has 4 columns like (Region, PaymentDate, DebitAmt, CreditAmt). This result-set will always have a maximum of one month's records in it.
Suppose, imagine i have data for a month (ex, Mar 1 to Mar 31)...
Now... to aggregate (sum) the amount columns (DebitAmt, CreditAmt) in my resultset based on different date ranges, i wrote a sql like below...
Quote: SELECT REGION, SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN DebitAmt ELSE 0 END) AS Debit_H1, SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN CreditAmt ELSE 0 END) AS Credit_H1,
[code]...
My doubt is, in the above query, to aggregate two different columns based on same condition, i am checking the same condition twice...
I have to write function that receives department name and an aggregation operation (average, maximum, minimum) and apply the operation on the salary of employees working on the given department and return the result.
here is my select statement:
select distinct d.deptno, d.deptname, max(e.salary) from employee e join department d on e.deptno=d.deptno where d.deptname=upper('finance') group by d.deptno, d.deptname; [code]...
Is it possible to link to Discoverer Viewer from Forms without requiring the user to log in again. If so, can I then link to a specific Workbook within Discoverer?
When running my query in sql developer, I have to execute the command apps.fnd_global.apps_initialize(user_id, resp_id, resp_appl_id) in order to show the result of my query. Without this, the query returns nothing. The problem is I am using this query as a custom query for my Discoverer report and my report does not show any record.
I need to copy my discoverer report from dev to prod. How should I do this? Are the items created using calculation in the report can also be copied? These items are not present in the folder in discoverer admin.
[Message #1] how to get a unapplied receipts for ageing report? and RMA(credit and debit note)columns ?
Which tables should be refered for Ageing reports.
[Message #2] How to create an ageing report in discoverer?
Which tables should be refered for a Ageing reports.
Ageing report colums customer name, customer id,location,segment, credit note and debit note in different column,unapplied amount and applied amount in a different colum.Already i got a due remaning date and total amount I want a linked table names.
we have upgraded our discoverer from 3i to 4i and from 4i to 10g. After that we faced a strange problem related to arabic font
when opening discoverer desktop and open the list of the saved reports. some reports that was saved by an Arabic name are display by name '????'(question marks)
also another issue when opening it . It open normal but some columns that are Arabic are displayed in this way (ßÇÑÊ-ÇíÌíÈÊ Êáì ßÇÑÏ- ÝÆÉ 100 Ìã). so i must change the column font from "tahoma" to "arial" or any other font that support Arabic.
- my NLS_lANG =american_america.ar8mswin1256 - this problem didnot happens on the discoverer 3i
We are using Discoverer 10g, apps mode EUL. I have assigned administrator privileges to System Administrator responsibility using EUL_US as well as SYSADMIN (re-did with SYSADMIN, just in case). I have 2 Business Areas in EUL and gave access to both of them to System Administrator responsibility and the userid TESTER (this user already has System Administrator responsibility assigned to it). But when I login to Administrator using TESTER and System Administrator, I don't see any business Areas available to select.
I am using Oracle Discoverer Plus and I have some columns in the database table that periodically take the zero value. I have to use that column to divide other values. Is there any way I can handle this to show "0" when it is divided by zero?
Discoverer Plus shows that PL/SQL functions can be used in calculations. But I can't find the Register PL/ SQL function option in the Tools menu.
I want to setup Web based Discoverer 10g on Oracle EBsuite 12.1.1.Is there any way to do this without installing a separate Oracle Application Server 10g Home????.The EBsuite 12.1.1's Oracle Application Server version is 10.1.3.
I'm trying to create an install script to install Discoverer 10g R2 with its needed patch and opatches applied without any user interaction. I've already created the necesary response files and a batch file to sequence it. The installer should work when the it is placed on a server with the main folder shared and it does so flawlessly.
the user sees a dos window which is kindly stating that he has to wait for the primary installer to finish before hitting enter to start the patch installer.The problem I'm having is that, on slow networks, it takes a while for the primary installer of discoverer 10g to show up a window and of course the user isn't always patient enough to wait for it and hits enter before the primary installer is showing itself causing the patch installer to start before discoverer is completely installed.
Is there a way to avoid this? Or am I wrong in using a batch file to sequence this install? second problem is the needed interaction while applying opatches, can this be automated as well?
here is the contents of my batch file:
net use x: /delete net use X: \\servername\Oracle_cd\disco10gr2 /persistent:no @ECHO off cls :start
I am creating a report using EMP table . At Runtime User Input deptno ,and report shows him data from that Deptno .Output vary from deptno ,Sometime It returns 6 ,5 Or 3 Rows .
But I want the pagesize to be same for every query . Wheather its returning 1 rows ,It should print on Full page .
migrating reports 6i to reports 11g. I did some reading up and only found the the document Upgrading Oracle Forms 6i to Oracle Forms 11g 11g Release 1 (11.1.1) E10394-01 which talk about forms migration. I am looking for a reports migration and found no good documentation on it.
1) Can I migrate to 11g directly or should i migrate to 10g first.
2) Can I use the migation assitant for reports migration or is it only for the forms.
3) If i need to recompile the reports in 11g is there a a 11g developer suite that i should use for it.
I am using Oracle 11.5.10 ebusiness suite.I have modified a custom report (an rdf) that it now picks up field xx for the address rather than POH_ SHIP_ ADDRESS_LINE1..I modified the template.rtf and uploaded it using the responsibility Oracle XML publisher. However instead of getting the field value in the report. I get XX (i.e. the field name not the field value). How can I ensure that the report picks up the value?
I'm using it with MICROS and I want to edit an "RDF" then convert to an "REP" I have edited it but when I try to compile it it gives errors such as "statement ignored" or "must be declared". What these error messages mean!
If I do nothing to them and then try to save it as an REP i get a pop up error "REP-0736 there exists uncompiled program unit(s)" then i press OK and get an error reading "REP1430 cannot create REP file for file "uk_conf_lorne(name of my file)". Compilation errors found.
One of my columns consists of room numbers ie: room 1, room 2, these can range from 1 - 99. Now, it is very important that I am able to order these rooms, howver when i try to order them, as you may imagine it goes like just take these following data examples. The way I have wrote them is the current way in which they are displayed:
I am using reports builder 6i version. I have couple of reports developed in RDF format and it is working fine in Oracle apps 11.5.10 server.
I converted the RDF to REP(using File->Administration->Compile) for some security reasons and moved this .REP to same Oracle Apps 11.5.10 Server but the program ends up with this below error.
REP-0002: Unable to retrieve a string from the Report Builder message file. REP-1439:
i want to display values of same column with comma seperation like this
SELECT RTRIM(XMLAGG(XMLELEMENT(e,DESCR_INDI || ',')).EXTRACT('//text()'),',') INTO DESCR FROM STUDENT_SKILL_PER WHERE GROUP_NAME=:GROUP_NAME AND SKILL_NAME=:SKILL_NAME AND SUB_SKILL_NAME=:SUB_SKILL_NAME AND TERM=:TERM AND STUDENT_CODE=:STUDENT_CODE AND YEAR_CD=:YEAR_CD;
but the error shown to me that xmlelement must be declared...but when i deploy same query at sql editor it gives me correct result..how i can use it on oracle report 6i... i am using oracle 9i as database.
I am trying to add fields from an SQL server database to the EUL. We have a lot of Oracle tables and fields in the existinging EUL, and we have created a database link from the Oracle database to the SQL database. But how can I find til SQL database tables and fields in Discoverer admin?
