Omnis Mysql defined list

[Phil (OmnisList)]

Hi Doug,

Not seen the double vertical lines before, what do they mean in this select?

Is this a PostGreSQl thing only?

regards
Phil Potter
Based in Chester in the UK.

On 15/12/2016 23:43, Doug Easterbrook wrote:
> hi Stephen:
>
> we do something like this all the time — and do sql tricks to make it happen with the table class.   The general philosophy is that you NEVER NEVER (ok, hardly ever) want to change lines in a list through calculations because it really affects performance.
>
>
> so, how can you do trick omnis to do this. ..
>
> Step 1:  add variables to your query class that don’t exist in the database
>
> eg, query has   firstName and LastName.   both are in the database.    you add variable called    CombinedName — which will be a calculation
>
>
> Step 2:
>
> Override the methods in the table class that fetch your data.      What you want to do is be able to get ahold of $SELECTNAMES just before it is sent to mysql.
>
> from the query class above, your $selectNames will have    firstName,LastName,CombinedName      Again combinedName is NOT in the database, so this will generate an SQL error — we know this..
>
>
>
> Step 3:
>
> write some code like this to make a calculation on any columns you want.   We do it in postgres, so we’ve become adept and using stored procedures or any postgres function, calculations, date conversions or whatever we want to use
>
> replacement = ‘ lastName || firstName || length(firstName)  as combinedName’
> and then
>
> calculate SQLSTRING as $replace(combinedname  with  replacement    in $selectNames )
>
>
> now your SQL String (which was formerly $selectNames) is
>
> firstName,lastName ,  lastName || firstName || length(firstName) as combinedName
>
>
> and when you send that to the dam, you’ll get three columns back, one of which is calculated.
>
>
>
>
> that gives you all sorts of performance … and can also be used in background workers to get data without manipulating the list.
>
>
>
>
>
>
> hope that helps with the concept.         like I said, we use it all the time.
>
>
>
>
>
>
>
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
>
>> On Dec 15, 2016, at 3:39 PM, Stephen Baugh <stephenbaugh at queensberry.com> wrote:
>>
>> Hi Everyone
>>
>> Is there a way to add columns to a .$definefromsqlclass list definition, so that a calculation is processed as the list is built?
>>
>> This might be a pick lookup or math function etc
>>
>> I want to add for example, the following calculations to this definition, after of course adding the new columns separately if required.
>>
>> Do $cinst.iWindowList.$definefromsqlclass($cinst.iRowTableName) Returns lvStatus
>>
>> Calculate Month as ovControl.$doDivide(C_SUPP_BAL_MTH,REG_EXCHANGE_RATE)
>> Calculate Current as ovControl.$doDivide(C_SUPP_BAL_CUR,REG_EXCHANGE_RATE)
>> Calculate 30days as ovControl.$doDivide(C_SUPP_BAL_30,REG_EXCHANGE_RATE)
>> Calculate 60days as ovControl.$doDivide(C_SUPP_BAL_60,REG_EXCHANGE_RATE)
>> Calculate 90days as ovControl.$doDivide(C_SUPP_BAL_90,REG_EXCHANGE_RATE)
>>
>> Both C_SUPP_BAL_MTH and REG_EXCHANGE_RATE are defined in the list definition.
>>
>> I hope this makes sense, and as always thanks for the help.
>>
>> Cheers
>> Stephen
>>
>>
>>
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com