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:
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.