question on using SQL string functions

[Ruslan Zasukhin]

on 1/15/04 6:09 AM, Deane Venske at deane.venske at eduss.com wrote:

> On Wed, 14 Jan 2004 20:19:44 -0700, Dave Parizek <dave at Parizek.com> wrote:
> 
>>>> I am running the following query from within Valentina Studio:
>>>> 
>>>> UPDATE Item set storageloc = LEFT(Sku, (locate(Sku, '-')-1))
>>>> 
>>>> Sku and storageloc are fields of the table.  Sku's structure is
>>>> something like this: '1032-10200'
>>>> 
>>>> I would expect storageloc to become '1032'
>>>> 
>>>> but instead it becomes 'LEFT'
>>>> 
>>>> What am I doing wrong?
>>>> 
>>>> --Dave
>>> 
>>> Hi Dave,
>>> 
>>> I'm pretty sure this wont be able to do this until version 2.0 is
>>> ready. I'm looking forward to this too, being able to use functions
>>> inside of queries.
>>> 
>> 
>> If you can't do them inside of queries, can you use them some other
>> way?  It seems strange that they are documented in the sql pdf if you
>> can't use them yet.  Kind of almost false advertising...

Hi Dave,

Functions listed in the ValentinaSQL.pdf can be used inside
of BaseObject methods.

BaseObject methods is powerful feature, and you need read about them if you
did not yet.

Using BaseObject methods, you can get in 80% the same functionality.

In your above case, although, you need UPDATE by EXPR.
This is not implemented in 1.x.  Only UPDATE by CONST is implemented.


> They can be used in queries, but only in this context :
> 
> SELECT LEFT('1032-10200', (locate('1032-10200', '-')-1))
^^^^^^^^^^^^^^^^^^^^

Deane, not very correct.
Function LEFT()  btw only it can be used in the WHERE clause

        select ... WHERE left( fld, 5 ) = 'aaaaa'


> Which will return the result. It cannot be used in conjunctions with
> lookups to check actual values in the database.

Deane, again not very correct.

Functions can be used for search and sorting, but via BaseObject Methods.

> I often find I need to
> combine data or rip it apart like you've done. Some times I like adding
> things to the data, like for monetary values I like doing "SELECT '$'
> + Cost FROM items", but alas until v2.0 we can't do this.

Yes, in 2.0 this works.

    SELECT sin(5)

    SELECT 3 + 2

    SELECT '$ + Cost FROM items



-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------