Re: Using Variables Within Views
I have experimented with versions 4.12.UC1, 5.03.UC1, 6.00.UE1 of OnLine on
a Sun Sparc 10 running Solaris 2.4 with the two views below, and they all
work OK. Can you check these views on your machine? If they don't work,
you should report the machine type, the operating system version, and the
versions of OnLine you are using. Note that if you create the views OK,
there will be entries in the system catalogue which should be returned by
the SELECT statements, even if you own no other tables in the database.
CREATE VIEW mytables(tabid, tabname, ncols) AS
SELECT tabid, tabname, ncols
FROM 'informix'.Systables
WHERE owner = USER;
SELECT * FROM mytables;
CREATE VIEW mycolumns(tabid, tabname, colno, colname, coltype, collength) AS
SELECT T.tabid, T.tabname, C.Colno, C.Colname, C.Coltype, C.Collength
FROM 'informix'.Systables T, 'informix'.Syscolumns C
WHERE T.Tabid = C.Tabid
AND T.Owner = USER;
SELECT * FROM mycolumns;
Thanks,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
PS: Note that version 5.05 OnLine is generally the current version.
Even I am behind the times!
}From: marco greco <mar.greco@agora.stm.it>
}Date: 13 Jul 1995 17:58:59 GMT
}X-Informix-List-Id: <news.15410>
}
}Dave Cook <davec@excelsis.demon.co.uk> wrote:
}>Please can you help?
}>
}>Informix RDS 4.11 SE 5.0
}>
}>I am currently trying to develop a 'view' to form part of a security
}>system for an application, to allow a user to only access certain rows within a
}>table. My initial idea was to create the view as follows (simplified):
}>
}>CREATE VIEW view_name AS
}>SELECT table1.* FROM table1, table2
}>WHERE table1.col1 = table2.col1 AND
}>table2.col2 = USER
}>
}>This gives the following error:
}>-217: column(user) not found in any table in the query
}>
}>Does anyone have a solution as to how I can use variables i.e. USER, within
}>a 'view'?
}
}We Have the same problem on Online 5.00, but not on Online 4.10 (which is
}installed on another machine). This also happens with other built in
}functions (time, today...).
}
}We also used views like that in security schemes, and becouse of this bug,
}now we can't. Informix Italia and our dealer say they haven't heard of
}this bug...
}
}Marco.