go to post Robert Cemper · Mar 25, 2021 similar to the proposal of @Timothy Leavitt you use a Sub-Select for sortingand 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
go to post Robert Cemper · Mar 25, 2021 In a recent example I posted, I had the need to extend the naming and proposed folder structureIt 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.
go to post Robert Cemper · Mar 24, 2021 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.
go to post 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.
go to post Robert Cemper · Mar 23, 2021 OK!Now I see the problem. Each datatype has its specific conversion from internal format to ODBC formatSo if VerzamelDatTijd is %Date or %Time or %Timestamp and the stored content doesn't fityou get most likely this error.It could be by principle := it fits never, a design issueIt 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.
go to post Robert Cemper · Mar 23, 2021 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 !
go to post Robert Cemper · Mar 19, 2021 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
go to post Robert Cemper · Mar 19, 2021 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')
go to post Robert Cemper · Mar 19, 2021 Hi @Fábio Campos I see your problem. I don't assume that "Portuguese1" does solve thisSo 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'
go to post Robert Cemper · Mar 18, 2021 @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 ]
go to post 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 requiredhttp://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
go to post Robert Cemper · Mar 18, 2021 deep needs to be in the PublicList for @ {Argument Indirection}
go to post Robert Cemper · Mar 17, 2021 using SQL you can compare the last change between definition and compilation of classesa 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 4
go to post Robert Cemper · Mar 17, 2021 In SMP / Explorer you can getclass by name and its last date under routines the object by related name - - - -- - - - In addition in Studio, a class that is saved but not compiledis marked with + after the name while a changed class that is neither saved nor compiledis marked by * I have no idea if VSCode or Atelier have similar features.
go to post Robert Cemper · Mar 17, 2021 This list can be grouped into 3.5 categories: Installation and Configuration happens typically once and will not change under normal operation InterSystems IRIS license key Configuration Parameter File Databases Namespace interoperability settings Code management and change happens rarely and mostly not during full operation. the dependency of server environment might be required (mixed OS e.g. WIN - AIX) Custom code in %SYS namespace CSP, JS, and CSS files External linked libraries and custom shared libraries Almost Runtime changes Security settings (including users, roles, resources, services, and applications) Runtime changes Task Manager tasks Only the last 2 are critical for successful mirror failover and require dynamic handlingand the last requires also non-stop monitoring as it is not directly triggered by SysAdmin.
go to post Robert Cemper · Mar 17, 2021 Just any Select. e.g. SELECT 17+4 as BlackJack, NOW() as TimeToWinno table requiredor SELECT Top 5 * FROM %TSQL_sys.types
go to post Robert Cemper · Mar 17, 2021 You are right. I mixed it up with some other ALL ["CompileAll" ?]