Helen;

In my last post I recognized from Matijn and Woody that I had a redundant
(not necessary) reference to Z_GRUPOUSUARIOS_V in the "form" part of the SQL
statement.

> I'm surprised your computer didn't suffer
> spontaneous combustion.

:-))

Thank you Helen,
Guillermo


"Helen Borrie" <helebor@...> wrote in message
news:5.1.0.14.2.20020830012043.091d2060@......

> At 09:55 AM 29-08-02 -0500, you wrote:
> >Martin, Woody, Helen.
> >
> >I saw your messages; thank you. I begun this morning to play with the

select

> >statement of the views where I get this error. I have 5 views and in 2 of
> >them I was getting this message. What I did is change the order of table
> >names in the FROM segment of the select.... and funny thing: it worked

fine

> >... the error message did not appear any more. In the example I sent you,

I

> >made the change:
> >
> >it was:
> >FROM Z_ELEMUSUARIO, Z_GRUPOUSUARIOS_V
> >
> >I changed it to:
> >FROM Z_GRUPOUSUARIOS_V, Z_ELEMUSUARIO
> >
> >and worked fine. Is there an SQL rule that specifies the order of the

"from"

> >in a select statement? I'm happy because now its working, but I'd like to
> >know this change was the solution ...
>
> If this (your sample) really was the way you defined your view, its syntax
> is totally confused...and I missed it first time around. You have jumbled
> up implicit and explicit joins there so that the view
> Z_GRUPOUSUARIOS_V appears joined to itself without aliasing or any
> criteria for the self-join. I'm surprised your computer didn't suffer
> spontaneous combustion. See my annotations:
>
> CREATE VIEW Z_ELEMUSUARIO_V(
> OID_ELEMUSUARIO,
> OID_ELEMGRUPO,
> OID_GRUPO_USUARIO,
> NOMBRE)
> AS
> SELECT Z_ELEMUSUARIO.OID_ELEMUSUARIO,
> Z_ELEMUSUARIO.OID_ELEMGRUPO,
> Z_ELEMUSUARIO.OID_USUARIO_GRUPO,
> Z_GRUPOUSUARIOS_V.NOMBRE
> FROM Z_ELEMUSUARIO, /* leftmost table */
> Z_GRUPOUSUARIOS_V /* middle table */
> left join Z_GRUPOUSUARIOS_V /* rightmost table is a faulty self-join */
> on ( Z_GRUPOUSUARIOS_V.OID_USUARIO_GRUPO =
> Z_ELEMUSUARIO.OID_USUARIO_GRUPO);
>
> So, which instance of Z_GRUPOUSUARIOS_V is to be used in the join?
>
> If you didn't mean to do a 3-way join, one of which was a self join, then
> choose either implicit or explicit joining, but not both, and remove one
> instance of Z_GRUPOUSUARIOS_V. If you really wanted a left join then the
> implicit inner join makes toast out of it. If you really wanted a
> self-join on the view (which I'm not sure is possible but don't have time
> to test) then your syntax would require full aliasing *and* explicit joins
> throughout *and* a join criterion for the self-join.
> ...
> AS
> SELECT ze..OID_ELEMUSUARIO,
> ze.OID_ELEMGRUPO,
> ze.OID_USUARIO_GRUPO,
> zgv1.NOMBRE
> FROM Z_ELEMUSUARIO ze /* leftmost table */
> left join Z_GRUPOUSUARIOS_V zgv1 /* middle table */
> on zgv1.OID_USUARIO_GRUPO = ze.OID_USUARIO_GRUPO
> join Z_GRUPOUSUARIOS_V zgv2
> on <missing join criteria> ;
>
> heLen
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>