internal SQL queries

Josh Mellicker josh at dvcreators.net
Tue Aug 8 16:34:50 EDT 2006


I thought of filter, but the problem is the ID column always has to  
be the first one, right?

That's fine for data fields where there is only one ID column, but  
there might be several...


I've been thinking one could ascertain the column number of the  
desired ID column, then make a certain number of tabs, like:

put itemOffset(locatorColumnHeader,p) into locatorColNo



REPEAT locatorColNo -1 times
   put tab & "*" after theNumberOfTabsNeededToGetToDesiredColumn
END REPEAT

Then filter with

theNumberOfTabsNeededToGetToDesiredColumn & locatorValue & tab & "*"


something like that...

-----------------

So FILTER is definitely faster than a REPEAT loop going through all  
lines of a variable, right?

(because FILTER has to look at every line anyway, right?)



On Aug 7, 2006, at 12:51 PM, Jim Ault wrote:

> I would try this approach, although not the only one:
> ---------------
> get cr & whichDataFld & tab
> filter it with ( cr & locatorColNo &tab &"*")
> -- now you have a list of one line
> -- this filter pattern will insure only column 1 and
> --  whole matches between the cr and tab..
> -- no need to redo your IDs
> --the last tab is appended to 'it' since you could have a last line  
> table
> entry with only an ID and you would want to located it.  It is the  
> last line
> and has empty values, perhaps by mistake.
>
> if the number of lines in it > 1 then
>    answer "Multiple IDs found "& (the number of lines in it)
>    return empty
> else if the number of lines in it = 0 then
>    answer "ID " & locatorColNo & "  could not be found"
>    return empty
> else
>   return item dataColNo of it
> end if
> ------------------------------
> Jim Ault
> Las Vegas
>
> On 8/7/06 11:47 AM, "Josh Mellicker" <josh at dvcreators.net> wrote:
>
>> Here is a function I wrote to retrieve data from a table field.
>>
>> All it needs is:
>>
>> whichDataFld -  the long name of the data field
>> locatorColumnHeader - assumes the top row is a header row with the
>> column name, this is which column to use to locate the correct row
>> locatorValue - this is the value in the column with the header
>> "locatorColumnHeader" that tells you you have the right row
>> headerOfDataToGet - this is the column containing the data you want
>>
>>
>> FUNCTION getDataFromDataFld
>> whichDataFld,locatorColumnHeader,locatorValue,headerOfDataToGet
>>      put the text of whichDataFld into p
>>      set itemdel to tab
>>      put itemOffset(locatorColumnHeader,p) into locatorColNo
>>      put itemOffset(headerOfDataToGet,p) into dataColNo
>>      REPEAT with x = 2 to the number of lines of p
>>          IF item locatorColNo of line x of p = locatorValue THEN exit
>> REPEAT
>>      END REPEAT
>>      return item dataColNo of line x of p
>> END getDataFromDataFld
>>
>>
>>
>> So if your table field is:
>>
>> ID     name     color     food
>> 5        Ken        blue     cheese
>> 7       Jerry      green    pizza
>> 21    Sarah      red        sushi
>>
>>
>> you could say
>>
>> put getDataFromDataFld(whichDataFld,ID,7,"food") into tData
>>
>> RESULT: "pizza"
>>
>>
>> What I don't like about this handler is that it must scan every line
>> to find the right row... there must be a faster way to write this,  
>> no?
>>
>>
>>
>>
>> On Jul 21, 2006, at 1:01 PM, Viktoras Didziulis wrote:
>>
>>> Dear group,
>>>
>>> is it possible to query [tab] delimited data stored in fields or
>>> containers
>>> within a stack using SQL? e.g. without any external database  
>>> engine or
>>> database files or connections...
>>>
>>> All the best!
>>> Viktoras
>>> _______________________________________________
>>> use-revolution mailing list
>>> use-revolution at lists.runrev.com
>>> Please visit this url to subscribe, unsubscribe and manage your
>>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-revolution
>>
>> _______________________________________________
>> use-revolution mailing list
>> use-revolution at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your  
>> subscription
>> preferences:
>> http://lists.runrev.com/mailman/listinfo/use-revolution
>
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your  
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution




More information about the use-livecode mailing list