Consider those tables:
CREATE TABLE [UserGroups] ([ID] CHAR(32) PRIMARY KEY NOT NULL
UNIQUE,[Active] BOOLEAN NOT NULL,[Name] VARCHAR(64) NOT
NULL,[PasswordExpiration] INTEGER NOT NULL,[Created] DATE NOT NULL,
[Modified] DATE NULL);
and
CREATE TABLE [Users] ([ID] CHAR(32) PRIMARY KEY NOT NULL
UNIQUE,[UserGroupID] CHAR(32) NOT NULL,[Active] BOOLEAN NOT NULL,[Name]
VARCHAR(64) NOT NULL,[EMail] VARCHAR(128) NOT NULL,[Password] VARCHAR(16)
NOT NULL,[LastPasswordChange] DATE NOT NULL,[Created] DATE NOT NULL,
[Modified] DATE NULL);
I have the following View:
CREATE VIEW [UserGroupsGrid] AS
SELECT ug.*,
(
SELECT COUNT(*)
FROM [Users] AS u
WHERE u.[UserGroupID]=ug.[ID]
) AS [UserCount]
FROM [UserGroups] AS ug
I need this view to render a dynamic data grid, which will generate
different columns from different data types, but when I issue a pragma
table_info([UserGroupsGrid]), the following info is returned:
RecNo cid name type notnull dflt_value pk
----- --- ------------------ ----------- ------- ---------- --
1 0 ID CHAR(32) 0 (null) 0
2 1 Active BOOLEAN 0 (null) 0
3 2 Name VARCHAR(64) 0 (null) 0
4 3 PasswordExpiration INTEGER 0 (null) 0
5 4 Created DATE 0 (null) 0
6 5 Modified DATE 0 (null) 0
7 6 UserCount 0 (null) 0
Notice that UserCount (my sub-query) does not return a data type. Even with
CAST(subquery AS INTEGER) does not work =(
I know SqLite is kinda of loosely typed, but, for dynamic uses, it is
essential that some information about the type of the column is returned.
--
[]
Júlio César Ködel G.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
