Re: [libreoffice-users] problem in sql query

Hi

On Tue, 2011-10-04 at 17:00 +0200, Daniele Palumbo wrote: 

> hi all
>
> i am using eloquence database, linked with base.
>
> this query is working:
> SELECT TORD.NUMORD AS ORDINE, CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( 
> TORD.GGORD, '/' ), TORD.MMORD ), '/' ), '20' ), TORD.AAORD ) AS 
> DATAORDINE, CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( TORD.DCORGG, '/' ), 
> TORD.DCORMM ), '/' ), '20' ), TORD.DCORAA ) AS DATACONSEGNA, 
> ANACLI.CODCLI AS CODICECLIENTE, ANACLI.RAGSOC AS RAGIONESOCIALE, 
> ANARTI.CODART AS CODICEARTICOLO, ANARTI.DESART AS DESCRIZIONEARTICOLO, 
> ANARTI.UM AS UM, DORD.QTAORD AS QUANTITAORDINATA, DORD.QTASPE, 
> DORD.PREORD AS PREZZO, DORD.SC0ORD, ANARTI.PREART AS LISTINO, 
> ANAGRU.CODGRU, ANAGRU.DESGRU, ANAGRU.CATMER, FILPAG.DESPAG FROM 
> INFODB.ANARTI AS ANARTI, INF2DB.DORD AS DORD, INF2DB.TORD AS TORD, 
> INFODB.ANACLI AS ANACLI, INFODB.ANAGRU AS ANAGRU, INFODB.FILPAG AS 
> FILPAG WHERE ANARTI.CODART = DORD.CODART AND DORD.NUMORD = TORD.NUMORD 
> AND TORD.CODCLI = ANACLI.CODCLI AND ANARTI.GRUPPO = ANAGRU.CODGRU AND 
> ANAGRU.CATMER = FILPAG.CODPA AND TORD.TAGORD = 0
>
> but i have to do ANAGRU.CATMER = SUBSTRING( FILPAG.CODPA, 3, 4) cause 
> the structure of FILPAG is not so well formatted (and i cannot do 
> anything about that).
>
> SELECT TORD.NUMORD AS ORDINE, CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( 
> TORD.GGORD, '/' ), TORD.MMORD ), '/' ), '20' ), TORD.AAORD ) AS 
> DATAORDINE, CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( TORD.DCORGG, '/' ), 
> TORD.DCORMM ), '/' ), '20' ), TORD.DCORAA ) AS DATACONSEGNA, 
> ANACLI.CODCLI AS CODICECLIENTE, ANACLI.RAGSOC AS RAGIONESOCIALE, 
> ANARTI.CODART AS CODICEARTICOLO, ANARTI.DESART AS DESCRIZIONEARTICOLO, 
> ANARTI.UM AS UM, DORD.QTAORD AS QUANTITAORDINATA, DORD.QTASPE, 
> DORD.PREORD AS PREZZO, DORD.SC0ORD, ANARTI.PREART AS LISTINO, 
> ANAGRU.CODGRU, ANAGRU.DESGRU, ANAGRU.CATMER, FILPAG.DESPAG FROM 
> INFODB.ANARTI AS ANARTI, INF2DB.DORD AS DORD, INF2DB.TORD AS TORD, 
> INFODB.ANACLI AS ANACLI, INFODB.ANAGRU AS ANAGRU, INFODB.FILPAG AS 
> FILPAG WHERE ANARTI.CODART = DORD.CODART AND DORD.NUMORD = TORD.NUMORD 
> AND TORD.CODCLI = ANACLI.CODCLI AND ANARTI.GRUPPO = ANAGRU.CODGRU AND 
> ANAGRU.CATMER = SUBSTRING(FILPAG.CODPA, 3, 4) AND TORD.TAGORD = 0
>
> give me error
> (with isql: [ISQL]ERROR: Could not SQLPrepare)
>
> instead
> SELECT SUBSTRING ( CODPA, 3, 4 ) AS CM, FILPAG.DESPAG AS DESCRIZIONE, 
> ANAGRU.CODGRU, ANAGRU.DESGRU FROM INFODB.ANAGRU AS ANAGRU, INFODB.FILPAG 
> AS FILPAG WHERE ANAGRU.CATMER = SUBSTRING ( FILPAG.CODPA, 3, 4 ) AND 
> FILPAG.CODPA LIKE '..%'
>
> does work.
>
> where is the trick? :)
>
> thanks,
> d.

Looking at the WHERE clause, I do not follow your selection criteria.
You may want to use parentheses to clarify your logic. It looks like all
the conditions must be true for the query to give any results. Nothing
obvious comes to mind. You might trying to use a column of text as if it
was a column of integers, pure guess. I would look at the column
definitions to make sure that the data types match correctly. In the US
we have numeric postal codes that are often defined as text to keep the
leading zero - 08540 is stored as text not as an integer (8540).

Also, I would try breaking up the query, at least for testing.

-- 
Jay Lozier
jslozier@gmail.com

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
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