User comments | InterSystems Developer Community

Robert Cemper · Jan 22, 2025 go to post

something like this ?

| COL    | VAL |
| ------ | --- |
| codRep |        401,       428,       428,       464,       472 |
|  Abril |     100000,    180000,    160000,         0,         0 |
| Agosto |     100000,    350000,    200000,     90000,         0 |

then this is the SQL statement

SELECT'codRep'"COL", list($JUSTIFY(codRepresentante,10)) "VAL"FROM Ped.MetasRepresen where ano=2024Union All
SELECT' Abril', list($JUSTIFY(vendasAbril,10)) 
   FROM Ped.MetasRepresen where ano=2024Union All
SELECT'Agosto', list($JUSTIFY(vendasAgosto,10))
   FROM Ped.MetasRepresen where ano=2024

Robert Cemper · Jan 22, 2025 go to post

I understand that you want to have full control of your version
Increment and Decrement eventually also more than just +1,-1
so VERSIONPROPERTY is a dead herring.
BUT: You can achieve this in combination with a little SQL method.

Property RowVer As%Integer [
   SqlComputeCode = { if$i({*},$g(%IncDec)) },
   SqlComputed,
   SqlComputeOnChange = (%%INSERT, %%UPDATE) ];ClassMethod IncDec(step As%Integer = 0) As%Boolean [
      SqlName = IncDec, SqlProc ]
{
    set%IncDec=step quit1
}

Now you can set the increment to any %Integer of your choice.
e.g. -1 decrement by 1, 1 increment by 1, 0 leave it

How to use it:

INSERTORUPDATE pck.myTable
    SETname='Omer'WHERE pck.IncDec(-2)=1AND .... any other conditions ....

the IncDec SQLmethod is used as a static method
it doesn't reference any row dependency
So it is executed once before any row related processing.
if you omit it then row_version is not changed

Robert Cemper · Jan 22, 2025 go to post

the posted link is limited to HS licensed users only

HERE is an official public link: VERSIONPROPERTY
It does basically the same as my previous SqlComputeOnChange example
hidden and with less typing

Robert Cemper · Jan 21, 2025 go to post

I feel deeply honored and congratulate all other winning contributors!
🌟🌟🌟🌟🌟🌟🌟

Robert Cemper · Jan 21, 2025 go to post

what you see as \x.... is the hex image of a $LB("SERVERA.FOO.BAR.ORG/STAGE", ......)
try ZZDUMP of any $LB() and you see length + type + content
\t is the misinterpretation of length x\09
my guess it's the hex_dump of some object

Robert Cemper · Jan 21, 2025 go to post

Calculated doesn't do it.
BUT;
[ SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ]

Robert Cemper · Jan 20, 2025 go to post

Yes i! I'm quite sure!
DSTIME was introduced with or short after release of DeepSee more
than a decade back and didn't change.

Robert Cemper · Jan 20, 2025 go to post

you have to add you own RowVersion property.
my example only increases the version if there was a any change in the row.

Property RowVer As%Integer [ SqlComputeCode = { if$i({*}) }, 
       SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ];

works for SQL- and Object-mode

Robert Cemper · Jan 20, 2025 go to post

As you refer to CSP combined with a Steam I assume
you have some similar sequence in you CSP page

<palign="center"><!-- The trick is the use the encrypted oid of the stream as the STREAMOID parameter to the stream server --><imagesrc="%25CSP.StreamServer.cls?STREAMOID=#(..Encrypt(oid))#"></p>

Docs for StreamServer

That's where your stream is dumped to browser

Robert Cemper · Jan 17, 2025 go to post

Almost 6 years back I wrote and article on data synchronization
Using DSTIME and a related example in OEX.
It is focussed on detecting and optimizing insert, change, delete of specified Tables/Classes
and it takes care of processing cycles to avoid duplications.
So the output might be minimized.
Transmission speed to PostgreSQL is not part of the example

Robert Cemper · Jan 14, 2025 go to post

like this:

/// using $ZZFIX() custom functionClass rcc.GetFixZZ Extends%Library.String
{
Parameter LENGTH As%String = 10;Parameter ALIGN As%String = "LEFT";Parameter PADCHAR As%String = " ";
Method Get() As%String [ CodeMode = generator, ServerOnly = 1 ]
{
	set code=+%parameter("LENGTH")_","""_$E(%parameter("PADCHAR")_" ",1)_""""set code=code_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"$$$GENERATE(" quit $ZZFIX(%val,"_code )
	QUIT$$$OK
}
ClassMethod StorageToLogical(%valAs%String) As%String [ CodeMode = generator, ServerOnly = 1 ]
{
	set code=+%parameter("LENGTH")_","""_$E(%parameter("PADCHAR")_" ",1)_""""set code=code_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"$$$GENERATE(" quit $ZZFIX(%val,"_code )
	QUIT$$$OK
} }

Robert Cemper · Jan 14, 2025 go to post

Though, if you need this functionality over multiple namespaces
I'd suggest to use a Language Extension:
#1 an .inc for the the code definition in namespace %SYS

ROUTINE ZZFIX [Type=INC]
/// fix length string + padding + l/r-adjustment
ZZFIX(%val, len , pad = "", right = 0)
        if right quit$e($tr($j("",len)," ",$e(pad_" "))_%val,*+1-len,*)
        quit$e(%val_$tr($j("",len)," ",$E(pad_" ")),1,len)

#2 add this line to %ZLANGF00.mac

#include ZZFIX

after compiling it you may run a test in any namespace

USER>set test="213abc"
USER>write$ZZFIX(test,10,"*")
213abc****
USER>write$ZZFIX(test,10,"*",1)
****213abc
USER>write$zzfix(test,5,"*",1)
13abc
USER>write$zzfix(test,15,"$")
213abc$$$$$$$$$
USER>write$zzfix(test,15,"$",1)
$$$$$$$$$213abc
USER>

If available this could of course also replace the
ClassMethod in the DataType definition.
It's a matter of taste.

Robert Cemper · Jan 14, 2025 go to post

I just felt the need of the last extension to adjust it to reality.

A side effect: because of the implementation as Classmethod

ClassMethod Fix(%val, rcc, rccp = "", rccal = 0) As %String

You can apply it dynamically to any String/Number that you get in hands.
(that's how I tested the core functionality)

Robert Cemper · Jan 14, 2025 go to post

Thank You! That makes it clear.

Robert Cemper · Jan 13, 2025 go to post

Thank you @Jeffrey Drumm !
Yet another idea:
Sometimes you may want a static non-blank padding character.
e.g. ****123 or 345~~~~
this is included as Parameter PADCHAR as %String =" " ; default= blank

/// padding other than " " might be useful in some casesClass rcc.GetFixSqlPad Extends%Library.String
{

Parameter LENGTH As%String = 10;Parameter ALIGN As%String = "LEFT";Parameter PADCHAR As%String = "";

Method Get() As%String [ CodeMode = generator, ServerOnly = 1 ]
{
    set code=+%parameter("LENGTH")_","""_$E(%parameter("PADCHAR")_" ",1)_""""set code=code_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"$$$GENERATE(" quit ##class(rcc.GetFixSqlPad).Fix(%val,"_code )
    QUIT$$$OK
}

ClassMethod StorageToLogical(%valAs%String) As%String [ CodeMode = generator, ServerOnly = 1 ]
{
    set code=+%parameter("LENGTH")_","""_$E(%parameter("PADCHAR")_" ",1)_""""set code=code_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"$$$GENERATE(" quit ##class(rcc.GetFixSqlPad).Fix(%val,"_code )
    QUIT$$$OK
}

/// PADCHAR=" "; LEFT=0, RIGHT=1 ClassMethod Fix(%val, rcc, rccp = "", rccal = 0) As%String
{
    if rccal{
        set%val=$e($tr($j("",rcc)," ",$e(rccp_" "))_%val,*+1-rcc,*)
        }
    else {
        set%val=$e(%val_$tr($j("",rcc)," ",$e(rccp_" ")),1,rcc)
        }	
    quit%val
}

}

Robert Cemper · Jan 12, 2025 go to post

This doesn't work for SQL - Therefore a new version

Class rcc.GetFixSql Extends%Library.String
{

Parameter LENGTH As%String = 10;Parameter ALIGN As%String = "LEFT";
Method Get() As%String [ CodeMode = generator, ServerOnly = 1 ]
{
    set code=+%parameter("LENGTH")_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"$$$GENERATE(" quit ##class(rcc.GetFixSql).Fix(%val,"_code )
    QUIT$$$OK
}

ClassMethod LogicalToDisplay(%valAs%String) As%String [ CodeMode = generator, ServerOnly = 0 ]
{
    set code=+%parameter("LENGTH")_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"$$$GENERATE(" quit ##class(rcc.GetFixSql).Fix(%val,"_code )
    QUIT$$$OK
}

ClassMethod LogicalToOdbc(%valAs%String) As%String [ CodeMode = generator, ServerOnly = 1 ]
{
    set code=+%parameter("LENGTH")_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"$$$GENERATE(" quit ##class(rcc.GetFixSql).Fix(%val,"_code )
    QUIT$$$OK
}

// only required for SQL Display Logical mode !ClassMethod StorageToLogical(%valAs%String) As%String [ CodeMode = generator, ServerOnly = 1 ]
{
    set code=+%parameter("LENGTH")_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"$$$GENERATE(" quit ##class(rcc.GetFixSql).Fix(%val,"_code )
    QUIT$$$OK
}

/// LEFT=0, RIGHT=1 ClassMethod Fix(%val, rcc, rccal = 0) As%String
{
    if rccal{
        set%val=$e($j(%val,rcc),*+1-rcc,*)
        }
    else {
        set%val=$e(%val_$j("",rcc),1,rcc)
        }	
    quit%val
}

}

Robert Cemper · Jan 12, 2025 go to post

A more compact data type with LENGTH and ALIGN
For RIGHT alignment I cut off oversized data by LENGHT counted from the end.

Class rcc.GetFixAligned Extends%Library.String
{
Parameter LENGTH As%String = 10;Parameter ALIGN As%String = "LEFT";

Method Get() As%String [ CodeMode = generator, ServerOnly = 1 ]
{
    set rcc=+%parameter("LENGTH")
    if"RIGHT"=$zcvt(%parameter("ALIGN"),"U") {
        set code="$e($j(%val,"_rcc_"),*+1-"_rcc_",*)"
        }
    else {
        set code="$e(%val_$j("""","_rcc_"),1,"_rcc_")"
        }	
    $$$GENERATE(" Quit "_code)
    QUIT$$$OK
}
}

Robert Cemper · Jan 12, 2025 go to post

Sorry, no idea! It was the first time I met this suspicious parameter.
My personal style is quite traditional.

Robert Cemper · Jan 11, 2025 go to post

by extending %RegisteredObject you lost the basic data type
and all other parameters of %String
this should do it:

Class rcc.FixStr Extends (%Library.String, rcc.FixStr.Props)
{
/// Fill value according to LENGTH parameter
Method Get() As%String [ CodeMode = generator, ServerOnly = 1 ]
{
	set tCode="$e(%val_"""_$j("",+%parameter("LENGTH"))
	set tCode=tCode_""",1,"_+%parameter("LENGTH")_")"$$$GENERATE( "  Quit "_tCode)
	QUIT$$$OK
}
}

Robert Cemper · Jan 8, 2025 go to post

MySQL doesn't like TOP. Use LIMIT instead
You have an error in your SQL syntax

SELECT column_name(s)
FROM table_name
WHERE condition
LIMITnumber;

found here: https://www.w3schools.com/sql/sql_top.asp

Robert Cemper · Jan 3, 2025 go to post

My personal congratulations to the winners to complete the challenge in time
👍👏😎

Robert Cemper · Jan 2, 2025 go to post

different approach:

SELECT LI.id from FDRD_Com.List LI
JOIN FDRD_Com.Prod PR
ON LI.vehicle = PR.Car 
WHERE PE.ProductLineName='Toyota'AND PR.Car isnotnull-- might be obsolete

Robert Cemper · Jan 2, 2025 go to post

IN is expecting a list.

Did you ever try

selectidfrom FDRD_Com.List where vehicle IN
(selectLIST(Car) from FDRD_Com.Prod where ProductLineName='Toyota'and Car isnotnull)

on the sub select ?

Robert Cemper · Dec 31, 2024 go to post

As an active participant you know the practical case.
Though most readers that never touched AOC might be confused.
The original intention was to exclude parts not solved in ObjectScript:
(e.g Java, C# JS, ....)
It turned out this was not possible to implement with acceptable effort.

Robert Cemper · Dec 31, 2024 go to post

Big THANK YOU @Jani Hurskainen for sharing your story.
I'm impressed by the progress you made on ObjectScript
after only 1,5 years maybe 30% of working time,

AoC examples are definitely different from the target cases ObjectScript was designed for.
Its top target is the underlaying database, string manipulation and less math functionality.

My best wishes for Health and Success for the New Year
Robert

Robert Cemper · Dec 29, 2024 go to post

In 2021 I published my article IRIS easy ECP workbench
with a related a Docker based demo on OEX.
All you need is the ECP enabled license for containers or Platform Independent
Community version is not ECP enabled

Robert Cemper · Dec 27, 2024 go to post

Only 2 completed Packages from GitHub >
installed and verified with 2 test sets fulfill requirements.
You have to provide your test data set manually.

@Abdul Manan : see reply
@Yuval Golan : https://github.com/uvg/AdventOfCode2024Uvg

The contest is closed.

Robert Cemper · Dec 27, 2024 go to post

Thank you @Evgeny Shvarov !
I'd like to underline especially the use of Docker.
My hundreds reviews in OEX wouldn't be possible without it with my limited test environment.
It's not only the option to run various versions in parallel to my personal setup,
but much more the possibility to clean out all traces and specific settings just with a click.
If you ever tried to remove COMPLETELY an IRIS installation with all traces
from Windows you may understand me.
And with the available templates it is a really easy exercise to compose
your personal customized package with almost no effort and
test it over and over from scratch.

Robert Cemper · Dec 18, 2024 go to post

If available anyhow any SQL or TSQL is slower

Robert Cemper · Dec 17, 2024 go to post

Suggestion:
you crate your own stored procedure to decide during SELECT
example:

/// Return NEW for first occurance of item /// otherwise return OLDClass User.ItemStat Extends%RegisteredObject
{
ClassMethod NewOld(item As%String = "") As%String [ SqlProc ]
{
    if item=""quit"?"if$d(^||list(item)) quit"OLD"if$i(^||list(item)) quit"NEW"
}
}

How to use it:

SELECT *, ItemStat_NewOld(item) asStatusFROM items orderby2

Result:

ID	date	  item	Status
109/13/1932	A	NEW204/06/1933DNEW1006/15/1940	A	OLD
411/26/1940	A	OLD
602/19/1956	B	NEW804/22/1957D	OLD
705/01/1959D	OLD
906/29/1961		?
307/04/1992	B	OLD
512/08/2020D	OLD

User statistics