Re: [sqlite] Question about coalesce and data types

[Keith Medcalf]

>Is there a way to write my own coalesce-Function (or indeed any
>function) so that its result has an affinity? The documentation of the
>sqlite3_result_* family of functions suggests not.

No.  But you can cast the result to whatever type you wish:

cast(coalesce(a, 5) as TEXT)

and it will then have a type affinity.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about coalesce and data types

[Martin Engelschalk]

Am 21.08.2014 11:39, schrieb Clemens Ladisch:

Martin Engelschalk wrote:

It seems the solution is to actually pass all bind variable values by
their appropriate sqlite3_bind_* - function instead of just using
sqlite3_bind_text. However, this means quite a lot of work for me.

Isn't it also work for you to converting your values to text?
Yes, but this would mean to change a LOT of SQL and then testing it, 
Also, the SQL is generic and has to work on Postgres and Oracle too.

I will change the binding.



Is there a way to write my own Function so that its result has
an affinity?

No.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Thank you very much, i see my way now.

Martin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about coalesce and data types

[Clemens Ladisch]

Martin Engelschalk wrote:
> It seems the solution is to actually pass all bind variable values by
> their appropriate sqlite3_bind_* - function instead of just using
> sqlite3_bind_text. However, this means quite a lot of work for me.

Isn't it also work for you to converting your values to text?

> Is there a way to write my own Function so that its result has
> an affinity?

No.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about coalesce and data types

[Martin Engelschalk]

Hello Clemens,

thank you for your answer; i understand now why the where - condition 
returns 'false'. Also, the effect is independent of the function used.


It seems the solution is to actually pass all bind variable values by 
their appropriate sqlite3_bind_* - function instead of just using 
sqlite3_bind_text. However, this means quite a lot of work for me.
Is there a way to write my own coalesce-Function (or indeed any 
function) so that its result has an affinity? The documentation of the 
sqlite3_result_* family of functions suggests not.


Thank you
Martin


Am 20.08.2014 12:03, schrieb Clemens Ladisch:

Martin Engelschalk wrote:

create table TestTable (col_a numeric);
insert into  TestTable (col_a) values (1);

retrieve the row, as expected:

select * from TestTable where col_a = '1';

do not retrieve the row:

select * from TestTable where coalesce(col_a, 5) = '1'

Can someone please explain this to me or point me to some documentation?

The col_a column has numeric affinity, so the string '1' is converted
into a number.  

The return value of the function has NONE affinity, so no automatic
conversion happens.  


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about coalesce and data types

[Clemens Ladisch]

Martin Engelschalk wrote:
> create table TestTable (col_a numeric);
> insert into  TestTable (col_a) values (1);
>
> retrieve the row, as expected:
>
> select * from TestTable where col_a = '1';
>
> do not retrieve the row:
>
> select * from TestTable where coalesce(col_a, 5) = '1'
>
> Can someone please explain this to me or point me to some documentation?

The col_a column has numeric affinity, so the string '1' is converted
into a number.  

The return value of the function has NONE affinity, so no automatic
conversion happens.  


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about coalesce and data types

[Martin Engelschalk]

Hello list,

I checked the coalesce function and observed the follwoing results:

I create a simple table with one column and one row:

create table TestTable (col_a numeric);
insert into  TestTable (col_a) values (1);
commit;

The following statements retrieve the row, as expected:

select * from TestTable where col_a = 1;
select * from TestTable where col_a = '1';

Now when introducing coalesce, the following statements also retrieve 
the row:


select * from TestTable where coalesce(col_a, 5) = 1
select * from TestTable where coalesce(col_a, '5') = 1

Bur the two next statements do not retrieve the row:

select * from TestTable where coalesce(col_a, 5) = '1'
select * from TestTable where coalesce(col_a, '5') = '1'

When using coalesce, it seems to matter what is right of the = sign in 
the where clause. When comparing directly with the column, this dows not 
matter.
The same effect can be observed if i replace the constant '1' to the 
right of the = with a bind variable that I bind with sqlite_bind_text.


Can someone please explain this to me or point me to some documentation?

Thank you
Martin

--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users