Re: [libreoffice-users] Bug\feature in LO Base master\slave form linking

Hi Vladimir,

On Sun, 2011-06-26 at 15:36 +0600, Vladimir Drobyshevsky wrote:

> Hello!
>
>   I'm trying to create a form with master\slave relation. My tables
> linked to external PostgreSQL database, so I'm getting data for master
> and slave forms from queries. I make the relation by choosing fields
> in "Link with master table" and "Link with slave table" (I have
> localized version so I don't know how it exactly named) properties in
> subform's Property Editor. Subform's query is:
>
> SELECT "invTypes"."typeName" AS "Name",
> "invTypes"."description" AS "Description",
> "invTypeMaterials"."quantity" AS "Qty",
> "invTypeMaterials"."typeID"
> FROM "EDB"."public"."invTypes" AS "invTypes",
> "EDB"."public"."invTypeMaterials" AS "invTypeMaterials"
> WHERE "invTypes"."typeID" = "invTypeMaterials"."materialTypeID"
>
> Relation has to be build by "intTypeMaterials"."typeID" field, so I'm
> choosing "typeID" for "table" intTypeMaterials" and "ProductID" for
> master "table".
>
> When I'm trying to open the whole form in view mode, I'm getting an
> error: "Cannot load data. ERROR:  column reference "typeID" is
> ambiguous at character 338; Error while executing the query".
> If I look into the sent query, I will see that LO change WHERE clause
> to '( "invTypes"."typeID" = "invTypeMaterials"."materialTypeID" ) AND
> ( ( "typeID" = :link_from_ProductID ) )'.
> Both tables in query have "typeID" field, so server can't choose the
> need one. And Postgres can't use an AS aliases in WHERE clause.
>
> Is it possible to change the way of LO making relation's query or to
> find an another way to solve my problem?
>
> Thank you in advance!
>
> P.S. I have no subscription to this mailing list so send your answers
> to my address too, please!
>
> -- 
> Sincerelly yours,
> Vladimir G. Drobyshevsky
> Wanna call me? Do it right now: +7 912 2473415

My limited experience with SQL suggests you should qualify the typeID
fields to remove any ambiguity. I have had this problem when working
directly the outside database and developing queries for it. If one did
properly qualify the ambiguous fields you get a similar error message.

-- 
Jay Lozier
jslozier@gmail.com

-- 
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted