O$8.0.3 plus Postgres 9.3 and 9.1

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