i have a problem with query error code -104 char 231 '

Applications Grid / Reports
#1

I need to do a query with LEFT OUTER JOIN basically when I put a field to compare with data in some specific error I get the character 231. For example
FROM
P LEFT OUTER JOIN SAAIO_PEDIME CTRAO_EMBAR E ON (E. NUM_REFE = P. NUM_REFE)
LEFT OUTER JOIN ON SAAIO_IDEPED I (I. NUM_REFE = P. NUM_REFE) AND (I. CVE_IDEN = ‘CR’)
I mark this bug in query ‘CR’ in the char 231

Error
Error accessing the database
Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 230
select count (*) from P LEFT OUTER JOIN SAAIO_PEDIME CTRAO_EMBAR E ON (E. NUM_REFE = P. NUM_REFE) CTRAC_CLIENT LEFT OUTER JOIN C ON (C. CVE_IMP = P. CVE_IMPO) LEFT OUTER JOIN ON SAAIO_IDEPED I (I. NUM_REFE = P . NUM_REFE) AND (I. CVE_IDEN = 'CR ') where (P. NUM_PEDI IS NOT NULL) AND (P. FEC_PAGO IS NOT NULL)

if I do the query with a WHERE does not give me any problem with that character (I. CVE_IDEN = ‘CR’)

FROM
P LEFT OUTER JOIN SAAIO_PEDIME CTRAO_EMBAR E ON (E. NUM_REFE = P. NUM_REFE)
CTRAC_CLIENT LEFT OUTER JOIN C ON (C. CVE_IMP = P. CVE_IMPO)
LEFT OUTER JOIN ON SAAIO_IDEPED I (I. NUM_REFE = P. NUM_REFE)
WHERE
(P. NUM_PEDI IS NOT NULL) AND (P. FEC_PAGO IS NOT NULL) AND (I. CVE_IDEN = ‘CR’) ORDER BY P. NUM_REFE

#2

Re: i have a problem with query error code -104 char 231 ’

can you post the complete query?

#3

Re: i have a problem with query error code -104 char 231 ’

I did the test in the “SQL Builder” of scriptcase and the query works perfectly fine, but when you run the query in the grid I get the error, this is the query I put in the grid

SELECT P.CVE_CAPT,P.NUM_REFE,P.NUM_PEDI,C.NOM_IMP,P.CVE_PEDI, G.GUIA, LIST(O.NUM_CONT) AS NUM_CONT, S.GRA_TOT, CASE P.IMP_EXPO WHEN ‘1’ THEN (‘IMPORTACION’) WHEN ‘2’ THEN (‘EXPORTACION’) END, P.AUT_OBSE, P.FEC_ENTR,P.FEC_ENTR AS CIERRE, I.COM_IDEN,P.FEC_PAGO,E.FEC_EDES
FROM
SAAIO_PEDIME P LEFT OUTER JOIN CTRAO_EMBAR E ON (E.NUM_REFE=P.NUM_REFE)
LEFT OUTER JOIN CTRAC_CLIENT C ON (C.CVE_IMP=P.CVE_IMPO)
LEFT OUTER JOIN SAAIO_GUIAS G ON (G.NUM_REFE=P.NUM_REFE)
LEFT OUTER JOIN SAAIO_CONTEN O ON (O.NUM_REFE=P.NUM_REFE)
LEFT OUTER JOIN CTRAO_SOLANT S ON (S.NUM_REFE=P.NUM_REFE)
LEFT OUTER JOIN SAAIO_IDEPED I ON (I.NUM_REFE=P.NUM_REFE) AND (I.CVE_IDEN = (‘CR’)
WHERE
(P.FIR_ELEC IS NULL))
GROUP BY P.CVE_CAPT,P.NUM_REFE,P.NUM_PEDI,C.NOM_IMP,P.CVE_PEDI,G.GUIA,
S.GRA_TOT,P.IMP_EXPO,P.FEC_ENTR,I.COM_IDEN,P.FEC_PAGO,E.FEC_EDES,P.AUT_OBSE
ORDER BY P.NUM_REFE

the error I get is as follows

Error
Error al acceder el banco de datos
Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 429
select count(*) from SAAIO_PEDIME P LEFT OUTER JOIN CTRAO_EMBAR E ON (E.NUM_REFE=P.NUM_REFE) LEFT OUTER JOIN CTRAC_CLIENT C ON (C.CVE_IMP=P.CVE_IMPO) LEFT OUTER JOIN SAAIO_GUIAS G ON (G.NUM_REFE=P.NUM_REFE) LEFT OUTER JOIN SAAIO_CONTEN O ON (O.NUM_REFE=P.NUM_REFE) LEFT OUTER JOIN CTRAO_SOLANT S ON (S.NUM_REFE=P.NUM_REFE) LEFT OUTER JOIN SAAIO_IDEPED I ON (I.NUM_REFE=P.NUM_REFE) AND (I.CVE_IDEN = (‘CR’) where (P.FIR_ELEC IS NULL)) group by P.CVE_CAPT,P.NUM_REFE,P.NUM_PEDI,C.NOM_IMP,P.CVE_PEDI,G.GUIA, S.GRA_TOT,P.IMP_EXPO,P.FEC_ENTR,I.COM_IDEN,P.FEC_PAGO,E.FEC_EDES,P.AUT_OBSE

#4

Re: i have a problem with query error code -104 char 231 ’

If the query runs in SQL builder and does not in the grid, than you most probably ran into a limitation of the sql that is supported in the grid. I had some trouble getting a query that contains several functions (like ROUND) to run well in a grid. Unfortunately there’s no documentation on these limitations. I guess you have 3 options:

  1. isolate the problem by starting with a simple query that runs and then extending it until you get the error. Than try to find a workaround, or go to option 2…
  2. get in touch with support
  3. create a view of the query and base the grid on the view
#5

Re: i have a problem with query error code -104 char 231 ’

tk?s for your reply, I will try isolate the problem by starting with a simple query that runs and then extending it until you get the error.