New Topic

Community Topic Question Data Sources

Nathan Schroeder

NA Business Team

Assigned

Category Data Sources

Product Version 7.3

Can you have a Table Join where the table name set with parameter

Dennis Haller shared this question ago

Answered

We have a situation where a long list of IDs are composed outside of Yellowfin, then need to be provided to Yellowfin for a report that will JOIN on this list and produce its results. To date we have a design where we maintain a table called a transaction table, holding a transaction_id as a key and multiple values for that key. Then the Yellowfin view joins on the transaction table, with the transaction_id as a WHERE condition.

Because of concurrency conflicts on that transaction table, we were wondering if we could redesign our approach to use a named temporary table to hold the list of IDs that need to be passed to Yellowfin for the report. It would be a different temporary table (holding different IDs) for each call to the report. Is there a way to provide a Table name as a parameter to a Yellowfin view, that would then JOIN on that table? To my reading I don't think that would be possible, but I'm looking for your feedback on the issue, and any other possible suggestions.

Thanks

Dennis

The same question

Replies (1)

Nathan Schroeder ●

Hi Dennis,

The only way I can think of doing this is through a stored procedure view. In these views, the stored procedure parameters are specified at the report level prior to the report running. Once provided, the stored procedure will run, and the report will run on top of the results.

Will this work in this case?

Regards,

Nathan

Reply URL

Dennis Haller ●

Hi Nathan,

It seems Vertica external procedures do not return result sets back to Vertica.

So it wouldn't be possible just to access our ID list through an external procedure. We would have to implement the entire SQL query as an external procedure, then store the result set in a table, and then the Yellowfin report runs on that generated table.

But don't we then have the same problem again? If the output of the stored procedure goes to a table, then that table is subject to concurrency collisions when the report is requested by different users at the same time.

Is that how you see it?

Dennis

URL

Nathan Schroeder ●

Hi Dennis,

I am not terribly familiar with Vertica, but from a quick search it does seem that you are right here.

Can you elaborate a bit on what exactly will go wrong when users access this table concurrently?

Regards,

Nathan

URL

Dennis Haller ●

Basically there is a Vertica issue when too many reports are run at the same time, and we are loading lists of IDs into the same transaction table (against different keys). There has been a situation where this transaction table locks up waiting for all the loads to finish from the multiple users. The loads are coming from connections to Vertica triggered by our Application UI. The application loads the list of IDs, then initiates the Yellowfin report through our integration interface with YF.

The loading problem has nothing to do with Yellowfin as far as I know.

We were searching for an alternative design and that is the genesis of my question to you.

Thanks

Dennis

URL

Nathan Schroeder ●

Hi Dennis,

Thanks for the elaboration. Again my lack of knowledge of Vertica is limiting me here, but it sounds like the final options might be to employ a custom function to pull these values, perhaps in the form of a free-hand SQL calc field.

Sorry for the troubles :)

Regards,

Nathan

URL

Dennis Haller ●

Thanks for your suggestions.

If we get a list of values from a SQL calc field, can we JOIN on that list, or equivalently include them in a WHERE IN(..) clause?

There might yet be a way to do this, but it would take some further investigations.

Dennis

URL

Nathan Schroeder ●

Hi Dennis,

The only way to include custom SQL in a view, and be able to use that in a join, is through virtual tables.

However, if this join is being done at the report level through append sub queries, then you can use calculated fields as part of the join clause.

Hope this helps, I would probably need to see this in person to offer any specific suggestions.

Nathan

URL

Dennis Haller ●

Hi Nathan,

Thanks for these ideas, and it will take some more investigations from our side before we have more questions.

Regards

Dennis

URL

Nathan Schroeder ●

Sounds good, let me know if you do.

Nathan

URL

Nathan Schroeder ●

Hi Dennis,

I am going to close this one, but if you do have questions just reply and the case will be automatically re-opened.

Regards,

Nathan

URL