Can anyone give me some direction on how to work with effective dating in the diagram window of Toad for Data Analysts? I’m currently testing the software as a possible replacement for Hyperion. In Hyperion to get the maximum effective dated row from an effective dated table I would use a subquery linked back to the table. I have not been able to accomplish this using the Toad product.
Oracle 10g.
Bumping this up. I was able to recreate the effective dating I need in TDP but it took much longer to accomplish. Does anyone have a tutorial on working with effective dates in TDP?
We do have a date tab in the Query Builder that gives common date ranges. This uses a variable that is generated at run time. You can however, hold your cursor over the variable and it will tell you what the date range syntax would be. This sometimes helps me build date ranges.
Subqueries can also be made in the Query Builder. There is a subquery button on the where clause dialog.
We do not have any support for a look up for max date but when using the drop down for literals you will see the distinct dates.
This is not exactly what you want but hope this helps.
Debbie
Debbie,
Is is possible to make a request for an enhancement in the next version? When I’m working with an Orcale database looking at payroll and benefits data, 95% of the time I’m looking for the max effective date in a table so I can look at the most current data for a user. The current outdated software I’m looking to replace as well as PS Query both have this functionality. I would imagine this is basic functionality that most users need in writing reports.
I was able to replicate the max effective date I needed from some tables using TDP, but I had to type out the SQL for them manually in the SQL section. This doesn’t save me any time.
Sure. I entered this request as QAT-558
We want to add this enhancement. I want to make sure we get all the use cases here. Does anyone want to give me an example of the SQL? As well as screen shot of any other tool where this feature exists?
SELECT A.EMPLID, A.NAME, A.BUSINESS_TITLE, A.JOBCODE, A.POSITION_NBR, A.EMPL_STATUS, A.REG_TEMP, A.FULL_PART_TIME, A.EMPL_TYPE, A.EMPL_CLASS, A.GRADE, A.JOB_FAMILY, A.DEPTNAME
FROM PS_UA_EMPLOYEES A PS_LOCATION_TBL B
WHERE ( B.LOCATION = A.LOCATION
AND B.SETID = A.SETID
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_UA_EMPLOYEES A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= SYSDATE)
I was able to complete the effective dating needed for max row by doing the following:
First I set up the main query establishing the basic filters and fields to display from the core table used for the information .
Second I added two subqueries; the first subquery was used to get the Max value for the Effective Date field from this same table. I linked the subquery to two keys; Employee ID and Employee Record which allows for multiple employee records for the same Employee ID. Since this table can have future dated records I added a where clause to select the maximum Effective Date that was less than or equal to the current date.
The second subquery was to get the Max value for the Sequence Number as there can be multiple rows with the same Effective Date field. I linked this second subquery to the same Employee ID and Employee Record field but added a match on the Effective Date as well with the value for this pulled from the first subquery.
I then used the where to clause in the main query to point to the values in the Max Effective Date and Max Sequence Number fields established in the two subqueries.
It provided the same results as the current report generated out of the PeopleSoft Query tool.
Any inquiries, I’d be happy help contact me at jcorby@uakron.edu.