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