1. Jonathan Ashwell
  2. Valentina Database ADK
  3. Понедельник, Июнь 10 2024, 07:17 PM
  4.  Подписаться через email
I'd have records with a field that contains a list of newline-separated names. In some cases I'd like to find records where the query matches the first name in the list, and others cases where I'd like it to match the last name in the list. I don't want to post-process the search results, I'd like the logic to be in the Valentina search. I think this might be possible with Regex, but I thought perhaps creating a Valentina method would be a better way to go. Can anyone suggest the wording for such a method? One that would take into account that the number of names in the field can vary from 0 to many, and that names are separated with \n. Or Regex, if that would be better.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Hello Jon,

Something like this one?

create table t1 ( f1 string(100) );
insert into t1 values ('first value
second value
third value');

-- Check 2nd items
select * from t1 where f1 REGEX '^([^\\n]*\\n){1}second value'


I think you get a pattern:


-- First item:
'^([^\\n]*\\n){0}your_value'
-- Second item:
'^([^\\n]*\\n){1}your_value'
...
Комментарий
There are no comments made yet.
Jonathan Ashwell Ответ принят
Hi Ivan,

Hm, I'm not sure. Let's say I have a table with 1000 records, each with a text field containing a list of names, like this

Smith, RR
Jones, A
Martinez, S

I want to search the table for all records in which Martinez is the last name in this field and return it in a cursor. I don't want to retrieve records where the Martinez is not the last name, such as this one

Martinez, S
Gordon, MM

I was thinking of creating a Valentina method that would do the equivalent of (and I'll use Xojo syntax, but I think it's clear)

var a() as string = split(field, "\n";) //make an array of names
return a(a.ubound) //return last name

And use this method when searching. Wouldn't that approach work?

But I don't see array manipulation functions in the documentation -- of course I may have missed them.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Another ideas:


CREATE OR REPLACE FUNCTION GetLastStringListItem( inList VarChar, inValue VarChar ) RETURNS BOOLEAN
BEGIN
DECLARE res BOOLEAN default false;

DECLARE v_EOL String;
v_EOL = '
';

DECLARE items_count LLONG;
set items_count = List_ItemCount( inList, v_EOL );
IF items_count > 0 THEN
DECLARE last_item VARCHAR;
SET last_item = List_NthItem( inList, items_count, v_EOL );
IF last_item = inValue THEN
res = true;
END IF
END IF

RETURN res;
END



--
SELECT * FROM t1 WHERE GetLastStringListItem(f1, 'third value') = true;

Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Комментарий
There are no comments made yet.
Jonathan Ashwell Ответ принят
Thanks, Ivan, this is interesting. I've found the documentation about lists and the create procedure methods

http://valentina-db.com/docs/dokuwiki/v5/doku.php?id=valentina:vcomponents:vsql:reference:create_procedure#function_examples

But I don't see where to make created procudures, how to create them in Xojo, and where to store them.

Furthermore, I already have Valentina regex searches that find any name in a field with multiple variations (name begins with, name contains, whole name) and I don't want to recreate those searches in a new function just for first/last names.

What I'm really looking for is a way to feed the entire field to a Valentina function/method and have it return just the first or last line, which will be subjected to the regex search.

We have a VText field "names" holds a list of names in each record.

I'd like to make a new temporary field "firstNames", OR use a created function, that returns just the first name in for each record, which is what Valentina uses for the SQL search.

This pseudocode would return all records in which the first name starts with "Jam",

SELECT * FROM t1 WHERE firstNames REGEX 'Jam'

Maybe this can't be done so easily or at all, in which case I'll take a look at post-processing the whole field search and drop the records that don't meet the first/last criterion.

I appreciated your help and patience.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
But I don't see where to make created procudures, how to create them in Xojo, and where to store them.


It's db structure's object (like table, field, trigger ...) - so db stores it inside. Creation is also as simple as executing any other SQL statement.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
What I'm really looking for is a way to feed the entire field to a Valentina function/method and have it return just the first or last line, which will be subjected to the regex search.


It's no problem, just slightly modify the UDF above to return not bool but string:


CREATE OR REPLACE FUNCTION GetLastStringListItem( inList VarChar ) RETURNS STRING
BEGIN
DECLARE res STRING default '';

DECLARE v_EOL String;
v_EOL = '
';

DECLARE items_count LLONG;
set items_count = List_ItemCount( inList, v_EOL );
IF items_count > 0 THEN
DECLARE last_item VARCHAR;
SET res = List_NthItem( inList, items_count, v_EOL );
END IF

RETURN res;
END

--
SELECT GetLastStringListItem(f1) FROM t1;


Very similar UDF for first item - just instead of

List_NthItem( inList, items_count,...)



List_NthItem( inList, 1,...)


--
About calculated fields (methods).

I think you don't need it here, because you may get such field from the cursor instead of having a permanent "method" in the original table:


Cursor cur = db.SqlSelect("SELECT GetLastStringListItem(f1) FROM t1;");
Field f1 = cur.get_field(1);
...
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
SELECT * FROM t1 WHERE firstNames REGEX 'Jam'



SELECT * FROM t1 WHERE GetFirstStringListItem(f1) REGEX 'Jam'
Комментарий
There are no comments made yet.
Jonathan Ashwell Ответ принят
Thank you Ivan, I learned a lot.

I've blended your approach with Valentina methods, because I understand them better than stored procedures, and I don't know how to create the stored procedures as easily. I create a temp field as follows

For first name search:



if myDatabase.t1.VarCharField("firstName") = nil then
dim s as string = "IF(List_ItemCount(names, chr(10))>0, List_NthItem(names, 1, chr(10)), '')"

call myDatabase.t1.CreateVarCharField("firstName", 120448, EVFlag.fTemporary + EVFlag.fIndexed, s)
end if

select * from t1 where left("firstName", 3) = 'Jam'



Finding the last name is similar except for the method:



if myDatabase.t1.VarCharField("lastName") = nil then

dim s as string = "IF(List_ItemCount(names, chr(10))>0, List_NthItem(names, List_ItemCount(names, chr(10)), chr(10)), '')"

call myDatabase.t1.CreateVarCharField("lastName", 2044, EVFlag.fTemporary + EVFlag.fIndexed, s)
end if

select * from t1 where left("lastName", 3) = 'Jam'



The temp fields are not retained between launches.
Комментарий
There are no comments made yet.
  • Страница :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories