0

Ok this is what I'm trying to do. Let's say I have several views each view can have aliases and such like that and each view goes back to either another or view or a table. What I want to do is trace a specific field from it's view all the way down to the source. For example if I have this sql statement:

replace view blah
Select
t.acct_nmbr as I_account,
sum (t.num1+p.location) as location,
from blahTable as t 
left outer join blahTable2 p
on t.acct_nmbr=p.acct_nmbr;

This is a very simple case but what I want it is if I say trace(i_account) I will get the following hierarchy:

I_account --> blahTable ---> acct_nmbr

Or if I want location I would get the following:

location --> sum (t.num1+p.location)-->blahTable --> num1
------------------------------------------------>blahTable2 --> location

You can see that adding more and more it can get complicated to trace this especially if there is multiple joins and select and derived tables as well. I'm currently trying to code this from scratch but I wasn't sure if there was something out there already that does this.

Improve this question

1 Answer 1

Reset to default
1

I think this is an interesting concept. Ordinarily, I'd probably try to walk this recursively through a tree structure (which is what this is).
However, you're going to run into a couple of problems really quick;

  1. A view generally has the effect of hiding the implementation details from somebody who wants data from it. When your application code queries a view for data, it has no way to tell that no such table exists - from it's standpoint, the view is a base level table. For a number of reasons, this is usually the desired outcome (it's the database version of encapsulation). So unless your java code has access to the view creation scripts (or can somehow get the definition of the views), you will not be able to 'walk' the structure.
  2. Aggregate functions are a potential source of trouble. You're going to have issues with any CASE statements that switch fields for evaluation. You haven't listed your RDBMS, but some of them support multiple columns in some aggregates (like DB2 does for MAX()). This is going to cause problems because your destination column is dependent on the data retrieved.
  3. Any stored procedures have the potential to completely invalidate your results. It's perfectly valid for me to create a stored procedure that changes the tables it access based on the time of day (depending on use case, this might actually be necessary). Additionally, unless you have the source code, you may not be able to complete the walk.
  4. There is a command set called Alias, which basically repoints where a table (or view reference) actually points. This will have the effect of changing what 'base' table you're looking at (from an SQL standpoint). Depending on how you're getting your data, your Java code may resolve the alias or not. In either case, an alias is usually temporary, which has the potential to 'expire' your results.

So, in short, for anything like a real-world scenario, it's pretty much impossible...

5.How are you planning on handling recursive CTEs (I'm assuming teradata allows them)? This is something of a wierd subset of point 2; any fields in them either resolves to a 'base table' (what the optimizer sees as a table anyways), or a recursion towards the base table. You would need to design your program to detect recursion of this type, and deal with it appropriately. Unfortunately, you won't be able to rely on the terminating condition (evaluated per-line at runtime); thankfully, however, you won't have to deal with cyclical relationships.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

The RDBMS i'm using is teradata and i can do a show on the view and get the view definition with all syntax and everything like that. I was thinking of putting it into a tree structure based off of blocks of data. IE any derived tables or such and then from there doing more processing on the case statements or sum's.
How are you doing a 'show' on the view - is this through java? That solves point 1; what about the others? And why do you want this? This may be completely impossible for your intended use.
yes i just run query "show view view_name" and i get the definition from it as a string. Basically the whole point is to show the source that the field comes from whether it be a table or a calculation or whatever
Okay, that gets you the source for a view. What happens if you try to supply it with a table name - how do you tell it that something is a bottom level element? And what do you want to happen in regard to the other points? Especially in the case of stored procedures, where you do not have control over how it decides source/returns data?
i know when i hit the bottom level because of the specific db and how my architecture is designed. The other points i can determine from Sql reserved words for teradata. Procedures and functions must have a left and right paran and are not reserved words. So anything that is not a reserved word must be either a function or procedure or table/field name. If it has paran it than must be a function or procedure. in which case i can do a show on that and get the data as well.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.