I think SQL is correct.
The. problem is caused by INSERT or UPDATE
NVL() is not a synonym for $GET() we know from ObjectScript
Take a look into OpenExchange there are 669 packages using Docker
Which means at least 90% of them are running on the latest free Community Versions down loaded.
I bet there are enough examples that fit your needs
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=2024
Union All
SELECT ' Abril', list($JUSTIFY(vendasAbril,10))
FROM Ped.MetasRepresen where ano=2024
Union All
SELECT 'Agosto', list($JUSTIFY(vendasAgosto,10))
FROM Ped.MetasRepresen where ano=2024I 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 quit 1
}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:
INSERT OR UPDATE pck.myTable
SET name='Omer'
WHERE pck.IncDec(-2)=1
AND .... 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
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
I feel deeply honored and congratulate all other winning contributors!
🌟🌟🌟🌟🌟🌟🌟
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
Calculated doesn't do it.
BUT;
[ SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ]
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.
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
As you refer to CSP combined with a Steam I assume
you have some similar sequence in you CSP page
<p align="center">
<!-- The trick is the use the encrypted oid of the stream as the STREAMOID parameter to the stream server -->
<image src="%25CSP.StreamServer.cls?STREAMOID=#(..Encrypt(oid))#">
</p>That's where your stream is dumped to browser
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
like this:
/// using $ZZFIX() custom function
Class 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(%val As %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
} }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.
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)
Thank You! That makes it clear.
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 cases
Class 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(%val As %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
}
}
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(%val As %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(%val As %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(%val As %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
}
}
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
}
}Sorry, no idea! It was the first time I met this suspicious parameter.
My personal style is quite traditional.
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
}
}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
LIMIT number;found here: https://www.w3schools.com/sql/sql_top.asp
My personal congratulations to the winners to complete the challenge in time
👍👏😎
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 is not null -- might be obsoleteIN is expecting a list.
Did you ever try
select id from FDRD_Com.List where vehicle IN
(select LIST(Car) from FDRD_Com.Prod where ProductLineName='Toyota' and Car is not null)
on the sub select ?
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.
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
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
Only 2 completed Packages from GitHub >
installed and verified with 2 test sets fulfill requirements.
You have to provide your test data set manually.
The contest is closed.
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.