AW: create a SQL view over multiple members

Hi,

Even though alisases can be used to access members and to join members or
merge members (with union), they cannot be used within views.
A view can only be created over one or more tables (physical files) or view,
but not on aliases!

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Vern Hamberg
Gesendet: Wednesday, 29. July 2009 22:27
An: Midrange Systems Technical Discussion
Betreff: Re: create a SQL view over multiple members

oh, sorry - did not read your post right - did not see the join.

But it seems you should be able to join aliases - I've not tried it,
however. But you might go look at the notes for the CREATE VIEW
statement - there would normally be a comment about what cannot be done.

HTH
Vern

Gqcy wrote:

I, Like Rob got the alias to create, but I don't know
how to change my join to a Union like Vern suggested:

1 CREATE ALIAS XXXSLF_618 FOR XXXLIB/XXXSPF (F618001801) ;

2

3 CREATE VIEW XXXML1

4 AS SELECT T02.OMSO#, T01.DSWCTR, T01.DSFSDT, T01.DSSTDT,

5 T01.DSTO#, T01.DSOPR, T01.DSOPRS, T01.DSWSDT,

6

7 FROM "XXXLIB"/XXXSLF_618 T01,

8 PSMOMPF T02

9

10 WHERE T01.DSTO# = T02.OMTO#

11 AND( T01.DSWCTR = 1801)

* * * * * E N D O F S O U R C E *
* * * *
SQL7994 0 1 Position 1 Alias PSDSLF_618 created in PDATALIB.

SQL7030 30 3 Position 1 Alias PSDSLF_618 for table PSDSPF in
PDATALIB
not valid for statement.

SQL7961 0 Rollback completed.



rob@xxxxxxxxx wrote:

Tried to use an alias with no luck. An alias is how one often handles
members.

CREATE ALIAS ROB/DB FOR ROB/AAAKEY (DB)

create view cgcyview

as select db.loc, aaakeyc.duh
from db left outer join aaakeyc on db.loc=aaakeyc.loc
Alias DB for table AAAKEY in ROB not valid for statement.

Repeat after me: Multiple members <expletive deleted>.

Rob Berendt