CODEWITHSUNDEEP
javasql
user961501
user961501

Reputation: 11

Java With SQL Mapper

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.

Upvotes: 0

Views: 738

Answers (1)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

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.

Upvotes: 1

Related Questions