similar to the proposal of @Timothy Leavitt you use a Sub-Select for sorting
and do the output in the outer SELECT
SELECT Name, SSN, Home_State, Home_Street, Age, Company, DOB FROM (
SELECT top all * FROM Sample.Person
order by ID desc
)
As you use Caché you may try in Namespace SAMPLES
In a recent example I posted, I had the need to extend the naming and proposed folder structure
It was obvious if you were reading the downloaded repository.
The related article was an advertisement and a "heads up" that it just wasn't the default structure as usual.
So you have to examine this special record in detail in the underlying Global.
Seems to be a broken record that you hit by accident.
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.
I fear this might not show up on Caché ?
Isn't it ?
These ranks all relate to Global Masters (GM)
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),DateTijdSecfrom 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.
what date time format would you expect this to be ????
'2021-03-04-2021 09:04'
YYYY-MM-DD-YYYY HH:mm
Not really standard! Rather a wrong DateTime in your Query !
for Caché ODBC/JDBC drivers are part of standard distribution since ever
%installdir%/bin/CacheODBC.dll
%installdir%/bin/CacheODBC64.dl
Or use your Caché distribution kit and run a custom install with ODBC only
Well,
You can either use default SQL function UPPER() or LOWER() to force a specific case. e.g:
SELECT * FROM erp.Teste2 WHERE
$TRANSLATE(LOWER(firstname),'áéíóúçâêîôû','aeioucaeiou') like 'fabio'or
SELECT * FROM erp.Teste2 WHERE
$TRANSLATE(UPPER(firstname),'ÁÉÍÓÚÇÂÊÎÔÛ','AEIOUCAEIOU') like UPPER('Fabio')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 & dirtySELECT * 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 indexProperty firstshaved as %String [Calculated,SqlComputed,
SqlComputeCode = {set {*}=$TRANSLATE(firstname,"áéíóúÁÉÍÓÚçÇâêîôûÂÊÎÔÛ","aeiouAEIOUcCaeiouAEIOU")} ];
applied as SELECT * FROM erp.Teste2 WHERE firstshave like 'fabio'
@Alexey Maslov
This doesn't compile if the calling method doesn't have [ ProcedureBlock = 0 ]
ERROR #1038: Private variable not allowed : '..Flatten2($name(agg),.summary)'
calling from terminal has no problem as there are all variables in Global Scope
[ I fell also in this trap 
]
#1) check that you run a Unicode installation. The U is important
.png)
#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 .png)
#3) check the default of your database to see if your collation fits.
search for portuguese (?)
.png)
#4) any external commection should only use UTF-8
deep needs to be in the PublicList for @ {Argument Indirection}
using SQL you can compare the last change between definition and compilation of classes
a negative difference shows where recompilation is required.
I used Posix-Timeformat for an easier compare
SELECT
def.ID, cmp.TimeChanged compiled, def.TimeChanged defined,
to_char($piece(cmp.TimeChanged,',',1),'J')-to_char($piece(def.TimeChanged,',',1),'J') diff
FROM %Dictionary.ClassDefinition def
left outer join %Dictionary.CompiledClass cmp
on def.ID=cmp.ID
where NOT def.name %startswith '%'
order by 4In SMP / Explorer you can get
class by name and its last date .png)
under routines
the object by related name .png)
- - - -- - - -
In addition in Studio, a class that is saved but not compiled
is marked with + after the name .png)
while a changed class that is neither saved nor compiled
is marked by * .png)
I have no idea if VSCode or Atelier have similar features.
This list can be grouped into 3.5 categories:
Only the last 2 are critical for successful mirror failover and require dynamic handling
and the last requires also non-stop monitoring as it is not directly triggered by SysAdmin.
YES, of course!
Just any Select. e.g.
SELECT 17+4 as BlackJack, NOW() as TimeToWin
no table required
or
SELECT Top 5 * FROM %TSQL_sys.types
You are right. I mixed it up with some other ALL ["CompileAll" ?]
2 remarks:
OK: version is updated now.
<Description> . . . is in moduel .xml
But not visible with zpm "search -d -r" .
e.g. https://github.com/rcemper/fast-JSON-formatting-IRIS-ZPM
https://openexchange.intersystems.com/package/fast-JSON-formatting-in-IRIS?tab=history
and my other 35+ packages
explicitly PUSHING "publish release" doesn't show any reaction.
and no change for zmp "search -d -r"
What's the delay to be expected?
There is a trick that may work for some pieces but requires very careful setup:
- create a new database that can be part of the MIRROR
- then you can map the components that are INDEPENDENT of the local instance to this DB
Be warned:
Identification and Isolation of these pieces can be quite tricky and sometimes also impossible
In addition, this can make troubleshooting and maintenance (release update?) rather complicated
and you should do an in-depth evaluation if the extra effort values the achieved result.
There is no explicit type Byte . In IRIS it's just an %Integer <256.
The equivalent to a byte[] array is a String of data type %Binary
To access the Byte values of the "array" use $A(your_binstring, position)
Attention: position starts with 1
Thanks for the inspiration for this article
Using ClassQueries() as Tables
BINGO ! you got the idea behind 
It is not meant to replace one of the queries:
do #1 or #2 or do both by UNION
maybe a misunderstanding:
I call both Class Queries as Tables and join them with UNION
without modifying the existing class queries
Using Class_Queries like a table the SQL Statement may look similar to this example:
select 'Q1' as Qry ,ID,name,SSN
from sample.SP_sample_by_name() where name %startswith 'A'
UNION
select 'Q2','--',SSN,name
from sample.employee_byname() where name %startswith 'K' with this result:.png)