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: [email protected] [mailto:[email protected]]
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
[email protected]
<330>819.4615
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
