Adam-

I think we're on the right track with the view definition, and thanks. I
understand why we have to use a left join -- because the table on the left
is the only true table, and the server has to loop through it first,
calling the SP for each record... I've gone ahead and used your SQL to
define the view.

Unfortunately, now I'm getting a "Dynamic SQL Error: feature is not
supported" message! I think that I'm going to have to try a different
tack. Perhaps pure stored-procedures instead of views.

Thanks,

Elias Sabbagh
Victor Technologies, LLC

On Fri, 8 Jun 2007, Adam wrote:

> --- 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
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>