Toad World® Forums

How to Create a Correlated Sub-query in Toad Data Point using Visual Designer/Diagram Tab

Hello,

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.

Paul

Hi Paul,

To create such query using Query Builder you should put a table in Query builder and checked all columns you include in the query.

After that select a column that you are going to use in where condition (#1)

On Where condition form select operator (#2).

And press Subquery button (3).

A subquery will be added to the query.

You may find addition info in the help http://dev.toadfordataanalyst.com/webhelp/Content/Query_Builder/Add_Subqueries.htm

Regards

Aleksey

Hi Aleksey,

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?

Best,

Paul

Hi Paul,

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.

Another more manual way would be to add the joins via formula in your subquery.

sample_subquery_formula.png

--Matt

Hi Paul,

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

After that you will see Subquery model where you could visually design a subquery

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

You may change the query alias if you want.

At the end the query will look something like this

You can add as many subqueies as you want at any level.

Regards

Aleksey