Thread: create view error
Hi folks,
I know I'm missing something blindingly obvious, can someone point it out to
me please.
create table locos ( -- Locos table - contains details of locos
lid int4 default nextval('loco_lid_seq'::text) unique not null,
lclass int4 references lclass(lcid), -- Loco Class
lbuilt date, -- Date off-shed
lcme int4 references cme(cmid), -- Chief Mechanical Engineer
lname character varying(30), -- Name of Loco
lcomments text -- free text comments
);
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'locos_lid_key' for
table 'locos'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create table lnumbers ( -- alternate loco numbers
lnid int4 not null references locos(lid),
lnumber character varying(10),
lncurrent bool,
primary key (lnid, lnumber)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'lnumbers_pkey'
for table 'lnumbers'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create view loco_dets as select * from locos l left outer join (select * from lclass) lc on lc.lcid =
l.lclass left outer join (select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent
= true left outer join (select * from company) c on c.coid = lc.lcompany;
ERROR: No such attribute or function ln.lnid
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
On Mon, 7 Jul 2003, Gary Stainburn wrote:
> Hi folks,
>
> I know I'm missing something blindingly obvious, can someone point it out to
> me please.
>
> create table locos ( -- Locos table - contains details of locos
> lid int4 default nextval('loco_lid_seq'::text) unique not null,
> lclass int4 references lclass(lcid), -- Loco Class
> lbuilt date, -- Date off-shed
> lcme int4 references cme(cmid), -- Chief Mechanical Engineer
> lname character varying(30), -- Name of Loco
> lcomments text -- free text comments
> );
> NOTICE: CREATE TABLE / UNIQUE will create implicit index 'locos_lid_key' for
> table 'locos'
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE
> create table lnumbers ( -- alternate loco numbers
> lnid int4 not null references locos(lid),
> lnumber character varying(10),
> lncurrent bool,
> primary key (lnid, lnumber)
> );
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'lnumbers_pkey'
> for table 'lnumbers'
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE
> create view loco_dets as
> select * from locos l
> left outer join
> (select * from lclass) lc on lc.lcid = l.lclass
> left outer join
> (select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent ^^^
select also lnid
> = true
> left outer join
> (select * from company) c on c.coid = lc.lcompany;
> ERROR: No such attribute or function ln.lnid
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr
On Monday 07 Jul 2003 12:40 pm, Gary Stainburn wrote: > left outer join > (select lnumber from lnumbers) ln on ln.lnid = l.lid and ^^^^^^^ ^^^^^^^ > ERROR: No such attribute or function ln.lnid Is is this? -- Richard Huxton
On Monday 07 Jul 2003 1:07 pm, Richard Huxton wrote: > On Monday 07 Jul 2003 12:40 pm, Gary Stainburn wrote: > > left outer join > > (select lnumber from lnumbers) ln on ln.lnid = l.lid and > > ^^^^^^^ ^^^^^^^ > > > ERROR: No such attribute or function ln.lnid > > Is is this? Yup, thanks to both of you for this answer. Is there any way to do this so that lnid is not visible in the resulting view? Also, using examples from this list, I've created a concat function and aggregate so that I can convert a number of rows to a comma delimited string. I can then use this in a select as shown below, but what I can't work out is how to put this into my join. I want to include the second of the two selects shown below (the one with 'lncurrent = true' where clause) into my view (shown at bottom). I can't work out where to put the where and group by clauses. nymr=# select lnid, concat(lnumber) as lnalternate from lnumbers group by lnid;lnid | lnalternate ------+-------------- 1 | 29 2 | 2392,65894 3 | 4277 4 | 80135 5 | 30926,926 6 | 45212 7 | 44767 8 | 60532 9 | 75014 10 | 75029 11 | 60007 12 | 25 278,D7628 13 | 08850,4518 14 | 62005,62012 15 | 24 061,D5061 16 | 45337 17 | 6619 18 | 64360,901 19 | 5 20 | 825 21 | 45157 22 | 76079 23 | 4771,60800 24 | 55019,D9019 25 | D9009 26 | 08556,D3723 (26 rows) nymr=# select lnid, concat(lnumber) as lnalternate from lnumbers where lncurrent = false group by lnid;lnid | lnalternate ------+------------- 2 | 2392 5 | 926 12 | 25 278 13 | 08850 14 | 62012 18 | 64360 23 | 4771 24 | D9019 26 | D3723 (9 rows) nymr=# create view loco_dets as select * from locos l left outer join lclass lc on lc.lcid = l.lclass left outerjoin lnumbers n on n.lnid = l.lid and n.lncurrent = true left outer join (select lnid, concat(lnumber)as lnalternate, lncurrent from lnumbers ) na on na.lnid = l.lid and na.lncurrent = false left outer join company c on c.coid = lc.lcompany; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
On Monday 07 Jul 2003 1:36 pm, you wrote: > On Monday 07 Jul 2003 1:07 pm, Richard Huxton wrote: > > On Monday 07 Jul 2003 12:40 pm, Gary Stainburn wrote: > > > left outer join > > > (select lnumber from lnumbers) ln on ln.lnid = l.lid and > > > > ^^^^^^^ ^^^^^^^ > > > > > ERROR: No such attribute or function ln.lnid > > > > Is is this? > > Yup, thanks to both of you for this answer. > > Is there any way to do this so that lnid is not visible in the resulting > view? > I've managed to get the view I wanted by using sub-selects - as shown below, but I now have the 'lid' field showing in the resulting view three times (as lid, lnid and lnaid). How can I remove lnid and lnaid from the result? create view loco_dets as select * from locos l left outer join lclass lc on lc.lcid = l.lclass left outerjoin lnumbers n on n.lnid = l.lid and n.lncurrent = true left outer join (select lnid as lnaid, concat(lnumber)as lnalternate from (select lnid, lnumber from lnumbers where lncurrent = false order by lnid, lnumber) alt group by lnaid) na on na.lnaid = l.lid left outer join company c on c.coid = lc.lcompany; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
On Monday 07 Jul 2003 2:12 pm, Gary Stainburn wrote: > > I've managed to get the view I wanted by using sub-selects - as shown > below, but I now have the 'lid' field showing in the resulting view three > times (as lid, lnid and lnaid). How can I remove lnid and lnaid from the > result? > > create view loco_dets as > select * from locos l > left outer join [snip] Don't do "select *" do "select field_a,field_b..." - the * doesn't just refer to the locos table. -- Richard Huxton
On Monday 07 Jul 2003 3:34 pm, Richard Huxton wrote: > On Monday 07 Jul 2003 2:12 pm, Gary Stainburn wrote: > > I've managed to get the view I wanted by using sub-selects - as shown > > below, but I now have the 'lid' field showing in the resulting view three > > times (as lid, lnid and lnaid). How can I remove lnid and lnaid from the > > result? > > > > create view loco_dets as > > select * from locos l > > left outer join > > [snip] > > Don't do "select *" do "select field_a,field_b..." - the * doesn't just > refer to the locos table. Sorry if I didn't make myself plain enough, but I had create view loco_dets as select * from locos l -- includes lid which I want left outer join (select lnid, lnumber.......) ln on ln.lnid = l.lid ....... The problem is that I have to have lnid in the sub-select to allow the 'on' clause to work, but I don't want lnid to appear in the resulting view. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
On Monday 07 Jul 2003 4:10 pm, Gary Stainburn wrote: > On Monday 07 Jul 2003 3:34 pm, Richard Huxton wrote: > > Don't do "select *" do "select field_a,field_b..." - the * doesn't just > > refer to the locos table. > > Sorry if I didn't make myself plain enough, but I had > > create view loco_dets as > select * from locos l -- includes lid which I want > left outer join > (select lnid, lnumber.......) ln on ln.lnid = l.lid > ....... > > The problem is that I have to have lnid in the sub-select to allow the 'on' > clause to work, but I don't want lnid to appear in the resulting view. Yep, so don't do "SELECT *", list the fields you want instead. The * in that second line applies to the rest of the query, not just the "locos" table. There is something to be said for a format such as "SELECT * EXCEPT lnid" but I don't think it's mentioned in any sql spec. -- Richard Huxton
Hi, Gary! > CREATE > create view loco_dets as > select * from locos l > left outer join > (select * from lclass) lc on lc.lcid = l.lclass > left outer join (*) (select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent > = true > left outer join > (select * from company) c on c.coid = lc.lcompany; > ERROR: No such attribute or function ln.lnid your subselect on line (*) does not contain lnid in the list of selected fields. I suspect you can also use constructions like select * from locos l left outer join lclass lc on (lc.lcid = l.lclass) instead of what you use: > select * from locos l > left outer join > (select * from lclass) lc on lc.lcid = l.lclass in this way you automatically avoid the error that you had! cheers, Mila