internal SQL queries

[Jim Ault]

Yes, filter is very fast, but not always the fastest, yet rarely far off.

You could do the variable column filter string, perhaps as

put cr & coolDataTable & tab into foundLines
put "*"&tab into C
get item 1 to colNeeded-1 of (C&C&C&C&C&C&C&C&C&C&C&C&C)
filter foundLines with (it & IDnum & tab&"*")


Jim Ault
Las Vegas

On 8/8/06 1:34 PM, "Josh Mellicker" <josh at dvcreators.net> wrote:

> 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
> 
> _______________________________________________
> 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