Base and SQL problems

leonn · June 20, 2011, 12:56am

Hi,

I'm new with Libre Office base and there is my actual problem...

(excuse my english, I speak french)

I have a base with a lot of tables.
Tables for Prefix, first name (prenoms) and Family names (Noms)

In another table, I have PERSONS table: build from the 3 previous table.

So on the Form, I see names, first name etc. but in the table only numbers are presents.

I don't know if it is clear....

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

Any help would be appreciate.

Thx

Denis

iplaw67 · June 20, 2011, 8:40am

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

Francois_M · June 20, 2011, 12:46pm

Alexander Thurgood <alex.thurgood <at> gmail.com> writes:

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

leonn · June 20, 2011, 12:58pm

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

Tom_Davies · June 20, 2011, 3:24pm

Hi
Yes, i normally have 3 or 4 fields/columns for names with another field/column
at the front for id/key. So,

id/key
title (Mr, Mrs, Miss, Ms, Dr etc)
Name1st
NameMid
NameLast
Then a Query that merges the name fields in a few different ways so it's easy to
get the into forms and stuff.

I had assumed that the example given was not the real columns/fields being
used. Sometimes it's difficult to give people a reasonable example of what you
are trying to do i guess
Regards from
Tom

Roxy_Robinson · June 20, 2011, 5:02pm

From what I've seen on this list since signing on about 10 days ago, there should
perhaps also be a "split" list between Windows users and Linux users!
Roxy

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

leonn · June 20, 2011, 5:47pm

From what I've seen on this list since signing on about 10 days ago, there should
perhaps also be a "split" list between Windows users and Linux users!

Well, I'm on Mac...

Roxy_Robinson · June 20, 2011, 6:01pm

That, too!

From what I've seen on this list since signing on about 10 days ago, there should
perhaps also be a "split" list between Windows users and Linux users!

Well, I'm on Mac...

planas · June 20, 2011, 9:41pm

Roxy,

> From what I've seen on this list since signing on about 10 days ago, there should
> perhaps also be a "split" list between Windows users and Linux users!

Well, I'm on Mac...

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

I use Windows and Linux - one for work and one at home. Some of the
issues are not OS specific either.

planas · June 20, 2011, 9:45pm

Denis,

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

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