Joined views

Yuan Terry Chao

Joined views
Is there an easy way to match view columns to their underlying table
columns using system catalog tables for a joined view ?

CREATE VIEW OWNER.V1 AS SELECT
T1.C1 AS VC1,
T2.C1 AS VC2 FROM T1, T2 WHERE join predicates.....

How can one find out using SQL against catalog tables that view column VC1
is really column C1 from table T1 and VC2 is C2 from table T2 ?


Thanks in advance.

Terry Chao

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Doyle Mark

Re: Joined views
(in response to Yuan Terry Chao)
The simple answer is "you can't (easliy)"

The reason is that a view may contain column functions, NTE's, column
renames, etc.

Putting all of those things aside, for 'simple' views -- those that
don't do a lot of the things I mentioned above-- you can probably get
close with a join on the systables, syscolumns (2 copies -- 1 for the
table and 1 for the view) and sysviewdep catalog tables. Join on the
either the column names, or the colno's.

You could always write a parser program and parse the text of the view
definition, but you'd have to be a braver man than I ;0)

Mark


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of T. Chao
Sent: Friday, January 26, 2007 2:48 PM
To: [login to unmask email]
Subject: Joined views



Is there an easy way to match view columns to their underlying
table columns using system catalog tables for a joined view ?

CREATE VIEW OWNER.V1 AS SELECT
T1.C1 AS VC1,
T2.C1 AS VC2 FROM T1, T2 WHERE join
predicates.....

How can one find out using SQL against catalog tables that view
column VC1 is really column C1 from table T1 and VC2 is C2 from table T2
?


Thanks in advance.

Terry Chao
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Dey

Re: Joined views
(in response to Doyle Mark)
Haven't tried this, but there's a new table_name() function. I believe this
may resolve view columns.


>From: Doyle Mark <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: [DB2-L] Joined views
>Date: Fri, 26 Jan 2007 15:12:14 -0600
>MIME-Version: 1.0
>Received: from www.idugdb2-l.org ([207.36.18.105]) by
>bay0-mc12-f18.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2444); Fri,
>26 Jan 2007 13:13:04 -0800
>Received: from affinity-3pmvlr (idugdb2-l.org) by www.idugdb2-l.org (LSMTP
>for Windows NT v1.1b) with SMTP id <[login to unmask email]>; Fri, 26
>Jan 2007 15:01:17 -0600
>Received: by WWW.IDUGDB2-L.ORG (LISTSERV-TCP/IP release 14.3) with spool id
> 923311 for [login to unmask email]; Fri, 26 Jan 2007 15:01:15
>-0600
>Received: from win-na.com (mail2.gencas.com) by www.idugdb2-l.org (LSMTP
>for Windows NT v1.1b) with SMTP id <[login to unmask email]>;
>Fri, 26 Jan 2007 15:01:14 -0600
>X-Message-Info: LsUYwwHHNt13rkPdVpjzpI2xW14AuphR/bObv5TRLy0=
>X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
>Content-class: urn:content-classes:message
>X-MS-Has-Attach:
>X-MS-TNEF-Correlator:
>Thread-Topic: Joined views
>Thread-Index: AcdBi1Kx0dJM2PvzTFujg5M6uzLQrwAAEb2w
>Newsgroups: bit.listserv.db2-l
>Precedence: list
>Return-Path: [login to unmask email]
>X-OriginalArrivalTime: 26 Jan 2007 21:13:04.0728 (UTC)
>FILETIME=[C4DC4580:01C7418E]
>
>The simple answer is "you can't (easliy)"
>
>The reason is that a view may contain column functions, NTE's, column
>renames, etc.
>
>Putting all of those things aside, for 'simple' views -- those that
>don't do a lot of the things I mentioned above-- you can probably get
>close with a join on the systables, syscolumns (2 copies -- 1 for the
>table and 1 for the view) and sysviewdep catalog tables. Join on the
>either the column names, or the colno's.
>
>You could always write a parser program and parse the text of the view
>definition, but you'd have to be a braver man than I ;0)
>
>Mark
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]
>On Behalf Of T. Chao
> Sent: Friday, January 26, 2007 2:48 PM
> To: [login to unmask email]
> Subject: Joined views
>
>
>
> Is there an easy way to match view columns to their underlying
>table columns using system catalog tables for a joined view ?
>
> CREATE VIEW OWNER.V1 AS SELECT
> T1.C1 AS VC1,
> T2.C1 AS VC2 FROM T1, T2 WHERE join
>predicates.....
>
> How can one find out using SQL against catalog tables that view
>column VC1 is really column C1 from table T1 and VC2 is C2 from table T2
>?
>
>
> Thanks in advance.
>
> Terry Chao
>------------------------------------------------------------------------
>--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
>archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
>From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences
>at http://conferences.idug.org/index.cfm
>
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

_________________________________________________________________
Invite your Hotmail contacts to join your friends list with Windows Live
Spaces
http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Yuan Terry Chao

Re: Joined views
(in response to James Dey)
Thanks, Mark and James for the reply.

Terry




james dey <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/27/2007 05:57 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Joined views






Haven't tried this, but there's a new table_name() function. I believe
this
may resolve view columns.


>From: Doyle Mark <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: [DB2-L] Joined views
>Date: Fri, 26 Jan 2007 15:12:14 -0600
>MIME-Version: 1.0
>Received: from www.idugdb2-l.org ([207.36.18.105]) by
>bay0-mc12-f18.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2444);
Fri,
>26 Jan 2007 13:13:04 -0800
>Received: from affinity-3pmvlr (idugdb2-l.org) by www.idugdb2-l.org
(LSMTP
>for Windows NT v1.1b) with SMTP id <[login to unmask email]>; Fri,
26
>Jan 2007 15:01:17 -0600
>Received: by WWW.IDUGDB2-L.ORG (LISTSERV-TCP/IP release 14.3) with spool
id
> 923311 for [login to unmask email]; Fri, 26 Jan 2007 15:01:15
>-0600
>Received: from win-na.com (mail2.gencas.com) by www.idugdb2-l.org (LSMTP
>for Windows NT v1.1b) with SMTP id
<[login to unmask email]>;
>Fri, 26 Jan 2007 15:01:14 -0600
>X-Message-Info: LsUYwwHHNt13rkPdVpjzpI2xW14AuphR/bObv5TRLy0=
>X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
>Content-class: urn:content-classes:message
>X-MS-Has-Attach:
>X-MS-TNEF-Correlator:
>Thread-Topic: Joined views
>Thread-Index: AcdBi1Kx0dJM2PvzTFujg5M6uzLQrwAAEb2w
>Newsgroups: bit.listserv.db2-l
>Precedence: list
>Return-Path: [login to unmask email]
>X-OriginalArrivalTime: 26 Jan 2007 21:13:04.0728 (UTC)
>FILETIME=[C4DC4580:01C7418E]
>
>The simple answer is "you can't (easliy)"
>
>The reason is that a view may contain column functions, NTE's, column
>renames, etc.
>
>Putting all of those things aside, for 'simple' views -- those that
>don't do a lot of the things I mentioned above-- you can probably get
>close with a join on the systables, syscolumns (2 copies -- 1 for the
>table and 1 for the view) and sysviewdep catalog tables. Join on the
>either the column names, or the colno's.
>
>You could always write a parser program and parse the text of the view
>definition, but you'd have to be a braver man than I ;0)
>
>Mark
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List
[mailto:[login to unmask email]
>On Behalf Of T. Chao
> Sent: Friday, January 26, 2007 2:48 PM
> To: [login to unmask email]
> Subject: Joined views
>
>
>
> Is there an easy way to match view columns to their
underlying
>table columns using system catalog tables for a joined view ?
>
> CREATE VIEW OWNER.V1 AS SELECT
> T1.C1 AS VC1,
> T2.C1 AS VC2 FROM T1, T2 WHERE join
>predicates.....
>
> How can one find out using SQL against catalog tables
that view
>column VC1 is really column C1 from table T1 and VC2 is C2 from table T2
>?
>
>
> Thanks in advance.
>
> Terry Chao
>------------------------------------------------------------------------
>--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
>archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
>From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences
>at http://conferences.idug.org/index.cfm
>
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences
at
>http://conferences.idug.org/index.cfm

_________________________________________________________________
Invite your Hotmail contacts to join your friends list with Windows Live
Spaces
http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm