New post
AllAccepted answers
Robert Cemper · Mar 30, 2021

you are mixing up 2 things:

  • one is the class that stores data in Caché
  • the Class generated as LINKED TABLE that stores its data in postgreSQL.'

so the LINKED describes the structure that is used by generated SQL statements
to work on postgreSQL. Take a look at the storage definition of the class
and see the difference.
But your code in Cache acts as if data were local !!
Either by Objects or by Tables. 
if you issue a %Save() on the class it runs a INSERT OR UPDATE undercover.

Robert Cemper · Mar 29, 2021

a few simple steps.

  • if not existing yet create a table on Postgres with the identic columns that you see in Cache or use an existing target
  • next use SMP > SQL > Wizzard > Link Table
  •  
  • It creates a class /table that reflects all columns /properties of the linked table. + storage method that can read, write, delete, insert over ODBC / JDBC on the postgreSQL table.
Robert Cemper · Mar 23, 2021

OK. this looks like the first record you find is already in trouble.
I'd expect your data type is something else then stored.
Next omit the conversion %ODBCOUT()  just to see what internal content is and where it starts

  select ID,%INTERNAL(VerzamelDatTijd),
        DateTijdSec from GLPPatTcActie
         where pnr = '27085070017'
         and LTestId->Makey='BLA'
         and glpactieid->makey in ('TAV','TMA') 

The next question is of course which program writes these values? and how? what's the source?

I get more and more the impression that some 'aged' program writes directly into the global.
Probably directly some $HOROLOG which is a combined %DATE,%TIME construct.
 

Robert Cemper · Mar 23, 2021

OK!
Now I see the problem. Each datatype has its specific conversion from internal format to ODBC format
So if VerzamelDatTijd is %Date or %Time or %Timestamp and the stored content doesn't fit
you get most likely this error.
It could be by principle := it fits never, a design issue
It could be just a glitch in some data record.
I suggest you start the SQL shell from terminal prompt.  USER>do $system.SQL.Shell()
And then run your modified query:

select  ID,%INTERNAL(VerzamelDatTijd),%ODBCout(VerzamelDatTijd),DateTijdSec
from  GLPPatTcActie 
 where pnr = '27085070017' and LTestId->Makey='BLA'
-- and VerzamelDatTijd < '2021-03-04 09:04'
and glpactieid->makey in ('TAV','TMA')
-- order by DateTijdSec desc

ID shows the flaky record
%INTERNAL() shows the raw content
%ODBCOUT() should trigger the error

Once you know the content you should be able to fix it.

Robert Cemper · Mar 19, 2021

Hi @Fábio Campos 
I see your problem. I don't assume that "Portuguese1" does solve this
So I see 3 workarounds to achieve "shaving" of characters.

#1)  quick & dirty
SELECT * FROM erp.Teste2 WHERE 
$TRANSLATE(firstname,'áéíóúÁÉÍÓÚçÇâêîôûÂÊÎÔÛ','aeiouAEIOUcCaeiouAEIOU') like 'fabio'

#2) more  elegant and also for other columns - create a SqlProcedure

ClassMethod shave(par) as %String [SqlProc, SqlName = "shave" ] 
{  quit $TRANSLATE(par,"áéíóúÁÉÍÓÚçÇâêîôûÂÊÎÔÛ","'aeiouAEIOUcCaeiouAEIOU") }


applied as 
SELECT * FROM erp.Teste2 WHERE erp.shave(firstname) like 'fabio'

#3) calculated property in your table - to be available also in index
Property firstshaved as %String [Calculated,SqlComputed,
SqlComputeCode = {set {*}=$TRANSLATE(firstname,"áéíóúÁÉÍÓÚçÇâêîôûÂÊÎÔÛ","aeiouAEIOUcCaeiouAEIOU")} ];
applied as 
SELECT * FROM erp.Teste2 WHERE firstshave like 'fabio'

Robert Cemper · Mar 18, 2021

#1) check that you run a Unicode installation. The U is important

#2) check the default language of your installation in SMP > ptbw and load it if required
http://localhost:52773/csp/sys/mgr/%25CSP.UI.Portal.NLS.zen 

#3) check the default of your database to see if your collation fits.
search for portuguese (?)

#4) any external commection should only use UTF-8