O$8.0.3 plus Postgres 9.3 and 9.1
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
