[libreoffice-users] problem in sql query

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.

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