--- In firebird-support@yahoogroups.com, Elias Sabbagh <ehs@...> wrote:

>
> Hello all,
>
> I'm trying to create a view that takes some of its columns from a table,
> and computes additional columns by using a selectable stored procedure
> joined to the original table. The motivation is that the original

table,

> named "coords", stores rectangular coordinates in columns X, Y, and

Z, and

> I want to create computed cylindrical and spherical coordinates columns
> on-the-fly on the server side. Triggers will then allow the view to be
> updateable, so I can bind to the view's columns with Delphi TDataSet
> descendants. As users choose from different coordinate systems in the
> interface, I only have to show or hide columns in a data-aware grid
> connected to the TDataSet. Depending on what coordinates are being

edited

> in the grid, I can convert back to rectangular coordinates for storage
> with an update trigger on the server.
>
> I'm having problems defining the select for the view, however. I hope
> that it is possible to make my scheme work!
>
> Here's what I've got -- a stored proc, convert_coords(), that takes five
> arguments and returns three. F and T stand for "fromCoords" and
> "toCoords", and are 0 for rectangular, 1 for cylindrical, 2 for

spherical.

> i1..i3 and o1..o3 are the input and output coordinate triplets.
>
> CREATE PROCEDURE CONVERT_COORDS (F Smallint, T Smallint, I1 Float, I2
> Float, I3 Float)
> RETURNS (O1 Float, O2 Float, O3 Float )
>
> This procedure has been tested and works fine.
>
> I now want to define my view. I plan to select from convert_coords()
> twice in the view definition, joining the output columns to the original
> coords table. One selectable call converts the table's x, y, and z

coords

> to cyl, the other selectable call converts x, y, and z to spherical
> coords. Each convert_coords() invocation performs a singleton

select, so

> joining twice won't alter the record count of the original coords table,
> but merely add equivalent coordinate representations to each record.
>
> Here's my attempt at creating the view. Apparently, I'm having a hard
> time getting the syntax right. Why doesn't this work?
>
> CREATE VIEW coords_v (x, y, z, ctheta, cz, cr, stheta, sphi, srho)
> AS
> select coords.x, coords.y, coords.z, c.o1, c.o2, c.o3, s.o1, s.o2,

s.o3

> from
> coords full join convert_coords(0,1,coords.x,coords.y,coords.z) c full
> join convert_coords(0,2,coords.x,coords.y,coords.z) s;
>
> Does anybody see what I'm doing wrong?

Yes, there are two problems.

Firstly, you can't do a full join to a stored procedure where the
input parameters of that stored procedure are provided by the table on
the left. You will end up with some stream error. Use a left join
instead, and if requried knock out records with no match using the
where clause.

Secondly, it is illegal syntax to not provide an ON clause for your join.

The basic select should look something like this:

select coords.x, coords.y, coords.z, c.o1, c.o2, c.o3, s.o1, s.o2, s.o3
from coords
left join convert_coords(0,1,coords.x,coords.y,coords.z) c ON (1=1)
left join convert_coords(0,2,coords.x,coords.y,coords.z) s ON (1=1)
where c.o1 is not null
and s.o1 is not null;

Adam