Re: [sqlite] How to make this calculation in VIEW

[Jeff Archer]

Thank you, Simon and Owen.

I have tried and both solutions work equally well.  I have actually used C code 
to make the calculation as I pull out the data.
But to help further my understanding of SQL/SQLite, can anyone comment on 
potential performance differences of the 2 SQL solutions.

CREATE VIEW IF NOT EXISTS [vw_patterns0] 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 * COUNT(DISTINCT Offset_X) AS wTotalMicrons,
   Patterns.hMicrons * COUNT(DISTINCT Offset_Y) AS hTotalMicrons,
   Patterns.wPixels * COUNT(DISTINCT Offset_X) AS wTotalPixels,
   Patterns.hPixels * COUNT(DISTINCT Offset_Y) AS hTotalPixels,
   Patterns.Description 
FROM [Tiles] INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID 
GROUP BY Tiles.PatternID;

CREATE VIEW IF NOT EXISTS [vw_patterns1] 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;

CREATE VIEW IF NOT EXISTS [vw_patterns2] AS 
SELECT Pattern, 
   Origin_X, 
   Origin_Y, 
   wMicrons, 
   hMicrons, 
   nTilesX, 
   nTilesY, 
   wMicrons * nTilesX AS wTotalMicrons,
   hMicrons * nTilesY AS hTotalMicrons,
   wPixels * nTilesX AS wTotalPixels,
   hPixels * nTilesY AS hTotalPixels,
   wPixels,
   hPixels,
   Description 
FROM [vw_patterns1];
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to make this calculation in VIEW

[O'Neill, Owen]

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

Re: [sqlite] How to make this calculation in VIEW

[Simon Davies]

On 17 November 2010 22:13, Jeff Archer  wrote:
> First let me say thank you to all for the very good support that receive here.
>
> I have the these tables and view
>
.
.
.
>
> 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"

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 * COUNT(DISTINCT Offset_X) AS wTotalMicrons,
   Patterns.hMicrons * COUNT(DISTINCT Offset_Y) AS hTotalMicrons,
   Patterns.wPixels * COUNT(DISTINCT Offset_X) AS wTotalPixels,
   Patterns.hPixels * COUNT(DISTINCT Offset_Y) AS hTotalPixels,
   Patterns.Description
FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID
GROUP BY Tiles.PatternID;

>
> 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

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to make this calculation in VIEW

[Jeff Archer]

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