Re: [libreoffice-users] Re: Base and SQL problems

Denis,

On Mon, 2011-06-20 at 08:57 -0400, Denis Poitras wrote:

> Hi François,
>
> In fact, I'm rebuilding a database that I can't no more using in my old program (Helix). In the 
> other program I had pop menu in each field from differents tables: prefix, first name, name, etc. 
> Same thing for many others databases.
>
> Now, I want to store id number for each prefix, first name, family name, etc.
>
> I understand that my old method is easier. So, I imported my old datas in different tables and 
> using a vlookup in Calc, I bring those tables in Base and make relation between differents Id.
>
> I'll try tomorrow what Alex wrote. 
>
> Thanks for your help and for your patience, I'm learning SQL in the same time and it's not easy 
> as my old program.
>
> Denis
>
>
>
> > > Le 20/06/11 02:56, Denis Poitras a écrit :
> > >
> > > Hi Denis,
> > >
> > > > (excuse my english, I speak french)
> > >
> > > You know, we have a French user mailing list too...
> > >
> > > > The problem is  in Propriety list zone on the data tab, for list content, 
> > on the form, to concatenate PREFIX
> > > FIRSTNAME FAMILY NAME, I have this:
> > > > SELECT "PREFIX"."Prefix" || ' ' || "PRENOMS"."Prenom" || ' ' ||
> > "NOMS"."Nom", "PERS"."Idpp" FROM "PERS"
> > > > LEFT JOIN "PREFIX" ON "PREFIX"."Cprefix" = "PERS"."PREFIXC"
> > > > LEFT JOIN "PRENOMS" ON "PRENOMS"."Cpr" = "PERS"."PRENOMC"
> > > > LEFT JOIN "NOMS" ON "NOMS"."Idnom" = "PERS"."NOMC"
> > > > ORDER BY "NOMS"."Nom"
> > > >
> > > > It's work but each time I quit this base and reopen it, Base keep only the
> > first line of this sql things...
> > > >
> > >
> > > Sounds like a bug. I'm a bit surprised that you don't use an ALIAS for
> > > your SELECT CONCAT statement - have you tried with an ALIAS and did it
> > > make any difference ?
> > >
> > > i.e. SELECT "PREFIX"."Prefix" || ' ' || "PRENOMS"."Prenom" || ' ' ||
> > > "NOMS"."Nom" AS 'NOMCOMPLET'
> > >
> > > There may also be an issue with the SQL parser that is built-in to LibO.
> > >
> > > Alex
> >
> > Denis,
> > I don't really understand your base set up: usually, it is not needed (and not
> > recommended) to have id, forename, surname within separate tables. As far as I
> > understood what you described in this query, one single table "PERS" may contain
> > id, forename, name fields (or "columns"), that would make your task much easier.
> > Perhaps should you describe what you are willing to achieve and where data are
> > coming from?
> > Hope this may help.
> > Francois
> >
> >
> > -- 
> > 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

Not a problem, we are learning.


-- 
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