I found that " CREATE VIEW allops AS SELECT acc1.operator.operatorid, acc1.operator.accountid, acc1.operator.name, acc2.operator.operatorid, acc2.operator.accountid , acc2.operator.name FROM acc1.operator, acc2.operator WHERE (acc1.operator.active = true OR acc2.operator.active = true); "
try this, CREATE VIEW allops AS select operatorid,operatorname from acc1.operator where active=true union select operatorid,operatorname from acc2.operator where active=true ;
works for the ambiguous errors. But there are identical column names in the view (operatorid, accountid, name, operatorid, accountid, name) witch obvious fires an error "ERROR: column "operatorid" duplicated" So how to merge the data from the different schema's in one column in the view?
The global / symbolic statement of what I wnat to do is SELECT operatorid FROM acc1.operator, acc2.operator,...... WHERE active = true; Are there perhaps other ways to do this if the views don't work?
Greetz,
Kristoff
"Toff" < kristoff.six@telenet.be> wrote in message news:XfwXe.197826$L_7.10628197@phobos.telenet-ops.be... > Hello! > > I want to create a view of a table that is in several schema's. > > The table is > CREATE TABLE "operator" > ( > "operatorid" INTEGER PRIMARY KEY, > "accountid" INTEGER REFERENCES "account"("accountid"), > "name" VARCHAR (40), > "active" BOOLEAN, > ); > > The schema's are "acc1", "acc2", ...... > > The view........ > CREATE VIEW allops AS > SELECT operatorid, accountid, name > FROM acc1.operator, acc2.operator > WHERE active = true; > > But I get the "ERROR: column reference "operatorid" is ambiguous" > If I remove "operatorid", or "accountid" in the view, the errortype is the > same. > > What is the ambiguous thing about this? And how to solve it? > On the internet I found some posts with the same issue, but no direct > answers. > > Thanks, > > Kristoff >
---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives?
