Omnis Mysql defined list
Phil (OmnisList)
phil at pgpotter.co.uk
Fri Dec 16 04:04:30 EST 2016
Hi Doug,
Of course, no sooner than I ask, I find the answer online.
Its a string concat() function, but like an Omnis calculation if there
is a null it returns null.
I've always used a concat() function in these cases, never seen the
double lines in SQL?
regards
Phil Potter
Based in Chester in the UK.
On 16/12/2016 08:53, Phil (OmnisList) wrote:
> 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
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>
More information about the omnisdev-en
mailing list