Hi Jeff, I haven't actually tried it, but just by inspection I would guess that a view can't refer to another column within itself, so there are 2 options.
Create a second view on top of the first view. (I've not tested this - note how the view name is aliased to just 'Patterns' because I'm lazy) CREATE VIEW IF NOT EXISTS [vwPatterns2] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.wMicrons * nTilesX AS wTotalMicrons, Patterns.hMicrons * nTilesY AS hTotalMicrons, Patterns.wPixels * nTilesX AS wTotalPixels, Patterns.hPixels * nTilesY AS hTotalPixels, Patterns.Description FROM Tiles INNER JOIN vwPatterns AS Patterns ON Tiles.PatternID = vwPatterns.PatternID GROUP BY Tiles.PatternID; Or change the definition of the column within the view to...... Patterns.wMicrons * COUNT(DISTINCT Offset_X) AS wTotalMicrons, Hmm will this work ? try it and see ! Cheers Owen -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jeff Archer Sent: Wednesday, November 17, 2010 10:14 PM To: SQLite-user.org Subject: [sqlite] How to make this calculation in VIEW First let me say thank you to all for the very good support that receive here. I have the these tables and view // Patterns table CREATE TABLE IF NOT EXISTS [Patterns] ( PatternID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name VARCHAR NOT NULL UNIQUE, Description VARCHAR NOT NULL, Origin_X REAL NOT NULL DEFAULT 0.0, Origin_Y REAL NOT NULL DEFAULT 0.0, wMicrons REAL NOT NULL DEFAULT 0.0, hMicrons REAL NOT NULL DEFAULT 0.0, wPixels INTEGER NOT NULL DEFAULT 0.0, hPixels INTEGER NOT NULL DEFAULT 0.0 ); // Tiles table CREATE TABLE IF NOT EXISTS [Tiles] (\n" TileID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" PatternID INTEGER NOT NULL REFERENCES [Patterns] DEFERRABLE INITIALLY DEFERRED,\n" Offset_X REAL NOT NULL DEFAULT 0.0,\n" Offset_Y REAL NOT NULL DEFAULT 0.0\n" ); // vwPatterns CREATE VIEW IF NOT EXISTS [vwPatterns] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.Description FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; I would like to add these calculated columns to my view but not sure how to make this work. Patterns.wMicrons * nTilesX AS wTotalMicrons, Patterns.hMicrons * nTilesY AS hTotalMicrons, Patterns.wPixels * nTilesX AS wTotalPixels, Patterns.hPixels * nTilesY AS hTotalPixels, i.e. // vwPatterns CREATE VIEW IF NOT EXISTS [vwPatterns] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.wMicrons * nTilesX AS wTotalMicrons, Patterns.hMicrons * nTilesY AS hTotalMicrons, Patterns.wPixels * nTilesX AS wTotalPixels, Patterns.hPixels * nTilesY AS hTotalPixels, Patterns.Description FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; But it get error: "no such column: nTilesX" It works if I substitute a constant for the nTilesX and nTilesY so I think I do not know how to correctly reference these. Thank you. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users