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?


Sorry for the involved explanation, and thanks in advance for any help.

Elias Sabbagh
Victor Technologies, LLC