I am evaluating Toad Data Point as a replacement for OracleEPM/Hyperion/Brio. So far, I like what we see, but want to test in a bit more detail. I am not a developer but an applied data analyst. I generally do not write SQL directly, but rely instead on a visual query builder.
I need help writing a correlated subquery using the visual query designer in Toad Data Point. I can build a subquery but cannot figure out how to join it back to the main query properly. What follows is the SQL we wish to replicate using the Visual Designer in TOAD Data Point:
**SELECT AL1.SGBSTDN_PIDM, AL1.SGBSTDN_TERM_CODE_EFF FROM SATURN.SGBSTDN AL1 WHERE (AL1.SGBSTDN_TERM_CODE_EFF IN**
(SELECT MAX ( AL2.SGBSTDN_TERM_CODE_EFF ) FROM SATURN.SGBSTDN AL2 WHERE (AL2.SGBSTDN_PIDM=AL1.SGBSTDN_PIDM)))
I’ve exported the above from Oracle EPM/Hyperion/Brio. Of course, I can paste it into the query builder in TOAD Data Point, execute it, and it works fine. However, I would like to be able to build it using the visual designer (the DIAGRAM tab in Query Builder). The particular thing I cannot figure out is how to link the subquery back to the main query, which is represented by this:
WHERE (AL2.SGBSTDN_PIDM=AL1.SGBSTDN_PIDM)
I understand that Toad Data Point may approach a problem like this differently than did Oracle EPM/Hyperion/Brio, I just can’t figure out what that approach may be.
Thanks for any advice you may have, and please forgive my ignorance.
Thanks for your response. Unfortunately, it still doesn’t solve my problem. I can create a create a subquery as you suggest, but what I don’t seem to be able to do is link the identifier in the subquery back to the identifier in the main query. I feel certain that this can be done, but how to do that eludes me. Any ideas?
We too are in the same process of migrating from Hyperion to Data Point and have data sources with effective dates that need to be correlated in subqueries. One of our folks found a workaround that involves adding the subquery in the main query pane instead of on the where clause (right click and select Add Subquery), grouping the subquery by the key fields required to join, and then joining those fields visually from the main query pane.
You are talking about two different use cases - the first one is using subquery in WHERE clause and the second one is joining a subquery and a table or another subquery.
To join table and subquery add a table to Query Builder, select some columns and press Add Subquery right click menu
The main query will show the Subquery visually as a normal table with the selected columns and you can drag a column from from one table to another to join columns
I've recently starting using Toad Data Point v6.2 diagrams to create sub-queries. The database is SQL Server. I reviewed this forum topic.
Even after reviewing, I am having difficulty understanding how to use Toad Data Point diagram to create and link a sub-query. I can get into the sub-query. But I can't figure out how to back out and have it linked to the original query.
Assume that there are three tables, A, B and C. The relationship between A and B is a simple foreign key where A's UID is stored in B. The relationship between B and C is also a foreign key where B's UID is stored in C, and the parent is required.
Table C contains three attributes, the foreign key RS_UID, EffectiveDate and Priority. When reading data from Table B, I want to also read the current priority from Table C. The current priority is the row where the TableB UID is in the Table C row and the effective date is the most recent effective date that is less than the current date.
The effective sub-query would be something like:
( select MAX(EffectiveDate) from TableC
where (EffectiveDate <= SYSDATETIME()) )
I suspect that my answer may be in that topic but it's just not coming to me.
I can write it by hand but I can't figure out how to diagram it in Toad Data Point.
