O$8.0.3 plus Postgres 9.3 and 9.1
hi Greg
short answer — it does not (on the insert or update case).
however, we also do this sort of stuff in the select statement in the table class, What we’ve done is create a method called ‘$replaceSelectNames’ in the table class.
after the select statement is built using the normal studio stuff and before set send to postgres, we give it a last chance to change things.
eg:
calc statement as $selectnames
do $cinst.$replacementSElectNames(statement)
and in the $repacementSelectNames, we do ANYTHING we want — such as replace with case statements, callable functions, anything.
eg suppose ‘gPhone’ was a variable in a non existant table in the database .. but it exists in a schema.
so we build a schema with
C_LAST_NAME,C_FIRST_NAME,gPhone again, there is no variable called ‘gPhone’ anywhere, except in a schema to let us use it in a query.
obviously
select C_LAST_NAME,C_FIRST_NAME,gPhone from customer, fakeTable will fail.
so in $replacementSelectNames, we do something like
calc statement as C_LAST_NAME,C_FIRST_NAME,gPhone
do $cinst.$replacementSElectNames(statement)
and in $replacementSelectNames, we might have
calc query as ‘(select phone from PhoneTable where phone_customer_seq = customer_seq) as gPhone’
replace (statement,’gPhone’,query)
so that the new statement is
C_LAST_NAME,C_FIRST_NAME,(select phone from PhoneTable where phone_customer_seq = customer_seq) as gPhone
we;ve done that with case satements, sub selects, postgres functions, time zone calculations, fiunctions that get icons for headed lists, functions that fire off updates to other tables while only selecting data, lots of stuff.
I”m not sure if I’m answering your question — and it is certainly a beer discussion at Euromnis and I can show examples of what we’ve done.
but yes.. we do the same as what you do and we’ve put it into a separate method that just replaces variable names.
even in the example of selecting C_LAST_NAME,C_FIRST_NAME into a list.
if its a read only list that is displaying a bunch of customers from the database .. id somebody owes money, then the $repalcementSelectNames might look like
query = case when (select sum(balance) from orders where ord_cust_seq=cust_seq) < 0 then
getColour(red) || C_FIRST_NAME || getColour(black) else C_FIRST_NAME end as C_FIRST_NAME
replace (statment ‘C_FIRST_NAME’,query)
so that the statement becomes
C_LAST_NAME, case when (select sum(balance) from orders where ord_cust_seq=cust_seq) < 0 then
getColour(red) || C_FIRST_NAME || getColour(black) else C_FIRST_NAME end as C_FIRST_NAME
and the effect is that the first name is red if they owe you money. getCOlour() is a function we built that returns omnis style colours that the headed list will understand ..
just ideas.
Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug@artsman.com
www.artsman.com
Phone (403) 650-1978
> On Sep 9, 2017, at 9:07 AM, Grzegorz (Greg) Pasternak <grzegorz@myfastcom.ca> wrote:
>
> Doug;
>
> A little off topic question:
>
> How would the TMOBJS handle calculated columns in the select column lists?
>
>
> Let’s say one of the columns is defined something like:
>
> (CASE t1.end_date-t2.end_date WHEN 0 THEN ‘<#Out>‘ ELSE ‘<#In>‘ END) AS cIn
>
> where #Out, #In are used with substitution
>
> The simplified version of the select statement would be for example:
> SELECT t1.t1_pk AS cPk,(CASE t1.end_date-t2.end_date WHEN 0 THEN ‘<#Out>‘ ELSE ‘<#In>‘ END) AS cSts
> FROM table AS t1
> LEFT JOIN [vLeftJoinStr]
> WHERE [vWhereClause]
>
>
>
>
>
> On Sep 9, 2017, at 10:22 AM, Doug Easterbrook <doug@artsman.com> wrote:
>
>> hi greg:
>>
>> you can certainly do that — have the column names determined from a variable.
>>
>>
>> or.. if you are using TMOBJS.. there are two really great commands that are in the external that we use within the tableclesses $update and $insert.
>>
>> both work on $cinst of the list.
>>
>> normally you’d go after $insertnames or $updateNames to get a dynameic list of fields from studio that wold the ones that you are updating. Kind of like
>>
>>
>> update table [$updatenames]
>>
>>
>>
>> so these two statements in TMOBS ($prepareupdate,$prepareinsert) look at the values in the list and only pick up the ones that are DIFFERENT from the prior version of the history row in the smart list and give back the bind variables for only columns that are different.
>>
>>
>> we’ve used this trick for years to be able to update against any table, even if the query/table class has joins to other tables. It has allowed us to update the primary table even in lists from cross table joins. and it reduces the amount of data being sent from the standard omnis update or insert which always sends field in the query
>>
>>
>> so, in the $update of the table class, we’d dos something like
>>
>> Do TMOBJS.$prepareupdate(statement,otherThanStandardField,nam(iRowOld),”DATE_UPDATED`DATE_ENTERED`EDITNUM`CHANGED_BY_E_SEQ”,tempPrefix)
>>
>> and that would get us the ‘statement’ which has the fields that changed.
>>
>>
>> and then we’d build
>>
>> update table [statement] where editnum=lasteditnum
>>
>>
>>
>>
>>
>> Doug Easterbrook
>> Arts Management Systems Ltd.
>> mailto:doug@artsman.com
>> www.artsman.com
>> Phone (403) 650-1978
>>
>>> On Sep 9, 2017, at 8:05 AM, Grzegorz (Greg) Pasternak <grzegorz@myfastcom.ca> wrote:
>>>
>>> Interesting, I just realized that I have never used bind variables as part of the column names list in the select clause and that is probably why I didn’t pay much attention to something like this:
>>>
>>> “select @[value] from table”
>>>
>>> Now after reading your email Doug of course it makes sense to me.
>>>
>>> However, I do like building list of column names dynamically so I would use something like this:
>>>
>>> “SELECT [vColNameLstStr] FROM table”
>>>
>>> where the vColNameLstStr would be calculated comma separated list of fully qualified column names
>>>
>>>
>>>
>>> On Sep 9, 2017, at 9:52 AM, Doug Easterbrook <doug@artsman.com> wrote:
>>>
>>>> i imagine its because @[xxx] is a bind variable .. . so it gets substituted with values.
>>>>
>>>>
>>>> so you can do
>>>>
>>>> update table set var1=@[value] which might become after evaluation update table set var1=10
>>>> or
>>>>
>>>> insert into table (var1) values (@[value]) which might become insert into table (var1) values (10)
>>>>
>>>> but you cant to
>>>>
>>>> select @[value] from table because it may or may not work depending on what the value of ‘value is’
>>>>
>>>>
>>>> if value=10, then
>>>>
>>>> select 10 from table would work, but lucky only
>>>>
>>>> if value was xyq
>>>>
>>>> then
>>>> select xyq from table would only work if xyq was a field in the table.
>>>>
>>>>
>>>> so bind variables are usually only used in where clauses, or values from update and insert clauses to set values.
>>>>
>>>>
>>>>
>>>>
>>>> Doug Easterbrook
>>>> Arts Management Systems Ltd.
>>>> mailto:doug@artsman.com
>>>> www.artsman.com
>>>> Phone (403) 650-1978
>>>>
>>>>> On Sep 9, 2017, at 6:42 AM, Grzegorz (Greg) Pasternak <grzegorz@myfastcom.ca> wrote:
>>>>>
>>>>> Just out of curiosity, what did you do to make it work?
>>>>>
>>>>>
>>>>> On Sep 8, 2017, at 10:22 PM, Mike Rowan <michael.rowan3@gmail.com> wrote:
>>>>>
>>>>>> As always, my thanks to all for this information. I still don’t understand
>>>>>> the difference between @[…] in the SELECT, and @[…] in the WHERE.
>>>>>>
>>>>>> I’m not sure the manual explains it.
>>>>>>
>>>>>> Anyway, I am up and running. for now. Ta all.
>>>>>>
>>>>>> On Fri, Sep 8, 2017 at 11:29 PM, Doug Easterbrook <doug@artsman.com> wrote:
>>>>>>
>>>>>>> so.. what is in #S1. probably not a field name
>>>>>>>
>>>>>>> Doug Easterbrook
>>>>>>> Arts Management Systems Ltd.
>>>>>>> mailto:doug@artsman.com
>>>>>>> www.artsman.com
>>>>>>> Phone (403) 650-1978
>>>>>>>
>>>>>>>> On Sep 7, 2017, at 7:50 PM, Mike Rowan <michael.rowan3@gmail.com> wrote:
>>>>>>>>
>>>>>>>> Good point Greg. In my app however, ALL column names are unique, so that
>>>>>>>> is a problem that has never arisen.
>>>>>>>>
>>>>>>>> Cheers and thanks for the interest.
>>>>>>>>
>>>>>>>> On Fri, Sep 8, 2017 at 11:12 AM, Grzegorz (Greg) Pasternak <
>>>>>>>> grzegorz@myfastcom.ca> wrote:
>>>>>>>>
>>>>>>>>> Mike;
>>>>>>>>>
>>>>>>>>> Just a quick comment, I would advise to always fully qualify column
>>>>>>> names
>>>>>>>>> in your LEFT JOIN clause to avoid any ambiguity and consequently errors.
>>>>>>>>>
>>>>>>>>> Greg
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Sep 7, 2017, at 9:37 PM, Mike Rowan <michael.rowan3@gmail.com>
>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> The following slightly simplified query in which iCol1, iCol5 are as
>>>>>>>>>> follows:
>>>>>>>>>>
>>>>>>>>>> iCol1 integer 64 bit
>>>>>>>>>> iCol5 character simple char 1
>>>>>>>>>>
>>>>>>>>>> —————————————————————–
>>>>>>>>>> SELECT @[iCol1] AS ref, @[#S1], @[iCol5]
>>>>>>>>>> FROM company, branch, purchaseorder
>>>>>>>>>> LEFT JOIN contact ON (ct_id = po_ct_id)
>>>>>>>>>>
>>>>>>>>>> WHERE
>>>>>>>>>> co_id = br_co_id
>>>>>>>>>> AND br_id = po_br_id
>>>>>>>>>> AND po_invoice_nr >= 10000000
>>>>>>>>>> AND po_invoice_nr <= 10000100 AND ((po_type = 1) OR (po_type = 3))
>>>>>>>>>> ORDER BY po_invoice_nr
>>>>>>>>>> LIMIT 20000
>>>>>>>>>> ———————————————————-
>>>>>>>>>> results in this Error message:
>>>>>>>>>>
>>>>>>>>>> ERROR: could not determine data type of parameter $2
>>>>>>>>>>
>>>>>>>>>> As I am fairly new to SQL I’m not sure what is wrong. If I omit all
>>>>>>>>> terms
>>>>>>>>>> in the SELECT line except the first, there is no error. Any second or
>>>>>>>>> more
>>>>>>>>>> term results in the same error.
>>>>>>>>>>
>>>>>>>>>> Anyone got a minute for this one?
>>>>>>>>>>
>>>>>>>>>> Mike
>>>>>>>>>> _____________________________________________________________
>>>>>>>>>> Manage your list subscriptions at lists.omnis-dev.com
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> _____________________________________________________________
>>>>>>>>> Manage your list subscriptions at lists.omnis-dev.com
>>>>>>>>>
>>>>>>>> _____________________________________________________________
>>>>>>>> Manage your list subscriptions at lists.omnis-dev.com
>>>>>>>
>>>>>>>
>>>>>>> _____________________________________________________________
>>>>>>> Manage your list subscriptions at lists.omnis-dev.com
>>>>>>>
>>>>>>>
>>>>>> _____________________________________________________________
>>>>>> Manage your list subscriptions at lists.omnis-dev.com
>>>>>>
>>>>>
>>>>> _____________________________________________________________
>>>>> Manage your list subscriptions at lists.omnis-dev.com
>>>>
>>>> _____________________________________________________________
>>>> Manage your list subscriptions at lists.omnis-dev.com
>>>
>>> _____________________________________________________________
>>> Manage your list subscriptions at lists.omnis-dev.com
>>
>> _____________________________________________________________
>> Manage your list subscriptions at lists.omnis-dev.com
>
> _____________________________________________________________
> Manage your list subscriptions at lists.omnis-dev.com
_____________________________________________________________
Manage your list subscriptions at lists.omnis-dev.com
