Omnis Mysql defined list

Phil (OmnisList) phil at pgpotter.co.uk
Fri Dec 16 09:23:52 EST 2016


Hi Doug,

Yes, thanks, it was literally just the double lines I'd missed before now.

But I don't understand why adding your code is any better than just 
putting. ie a sub query, directly into a query class... in the 'Column 
name' column, and then putting in the required column name in the Alias...

Yes, there are limits to how much you can type into a query class, but 
in most cases it is long enough?

Why is what your suggesting any better?

Or have I just missed the point of the original question?

regards
Phil Potter
Based in Chester in the UK.

On 16/12/2016 13:31, Doug Easterbrook wrote:
> hi Phil.
>
> ||   is concatenate strings.   I just picked that as an example, but you can use anything
>
>
> we even use conditional constructs to fill in some lookup table values and return as strings
>
> case when A = 1 then B when A=2 then C else Y end
>
>
> or my favourite    coalesce( )
>
>
> and we also use a fair number of subselects within a select, so the variable gets replaced with the following — which is extremely helpful if you have more than one join to a lookup table from a main table
>
> (select x from anothertable  where key=xx)
>
>
> in other words, the sky is the limit on the sql you can use with this technique
>
>
>
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
>
>> On Dec 16, 2016, at 1:53 AM, Phil (OmnisList) <phil at pgpotter.co.uk> 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
>
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com



More information about the omnisdev-en mailing list