wild card in an index

Fri Sep 17 05:21:23 PDT 2021

When we transitioned to the ERP we currently use back in 2008, I thought it was sort of useless with the number of tables that had rows with TrnMonth and TrnYear as populated fields. However over time I have discovered it makes it so much easier to pull data for reporting, and a grateful for that fact almost on a daily basis. 

-----Original Message-----
From: Filepro-list <filepro-list-bounces+wvaughan=steelerubber.com at lists.celestial.com> On Behalf Of Jeff Harrison via Filepro-list
Sent: Thursday, September 16, 2021 10:22 PM
To: Richard D. Williams <richard at appgrp.net>; Jose Lerebours <fpgroups at gmail.com>
Cc: filepro-list at lists.celestial.com
Subject: Re: wild card in an index

 I'm not sure why anyone would need to wild card a date.  In most cases it can be done with a range.  for example *2021 is the same as "ge 01/01/2021 and le @td".  Does 04* make sense?  Are you really looking at all years and only want the Months of April?  If you want *10* that certainly does not make sense because that would include the year 2010 as well as all Octobers for any year plus the 10th day of every month!  

I guess 04* could make some sense if you were for example wanting to look at trends and compare the month of April from one year to another.  

If you are just looking at one year then you can in effect "wild card" the month by doing the range "ge 04/01/2021 and le 04/30/2021".  If you do the select with a range like this it will use the index.

If you must use a wildcard though you can create another field that is a string format like "*" and populate it with the value of your date field.  To maintain the date sort you could format this field as YYYYMMDD or something similar with Year, Month, Day in that order.  However, this will NOT use the index if used in this way.  Alternately you could create a "*" field and store the name of the month in this field and build an index on that.  This way you could select for "April" and it would use the index.

Jeff

    On Thursday, September 16, 2021, 07:58:39 PM EDT, Jose Lerebours via Filepro-list <filepro-list at lists.celestial.com> wrote:  

 Per OP, looking for date with format m/d/YYYY, possibly looking for 04* or 08* or *2021 or *10*

In SQL a simple LIKE "%%" statement would have sufficed if you store your date in string/variable character format (I personally store dates in BIGINT types).

Back to fp - thinking that this is what the OP meant is why I suggested using selection tables since one could use `co`  "/01" or co "01/" or co "/01/" ...

On 9/16/21 7:45 PM, Richard D. Williams wrote:
> Is it me, or does anyone else not understanding to task?
>
> What wildcard?
>
> Is this *clerk or *report?
>
> Richard
>
> Sent from my iPhone
>
>> On Sep 16, 2021, at 8:06 AM, Jose Lerebours via Filepro-list <filepro-list at lists.celestial.com> wrote:
>>
>> -v selectiontable is the only way I can think of - unless, you use  a dynamic [selectionset] and point to it using -s (I think it is -s for reports, I rarely ever used this option).
>>
>> I seem to recall someone in this list talking about dynamically 
>> creating selection tables and using them on demand, but this could be 
>> your way out
>>
>>
>>> On 9/16/21 8:58 AM, Jeff Harrison via Filepro-list wrote:
>>> What exactly are you trying to wildcard? The month? The year? You can use the index if you have a range between two dates. If you have multiple ranges then you would need to write your selection with select processing using the lookup dash.
>>> Jeff
>>>
>>> Sent from Yahoo Mail on Android
>>>      On Thu, Sep 16, 2021 at 8:46 AM, Richard Kreiss via Filepro-list<filepro-list at lists.celestial.com> wrote:  Not a lookup. This is just straight index access from do’s menu.
>>>
>>> Richard
>>> Sent from my iPhone
>>>
>>>>> On Sep 15, 2021, at 11:47 AM, Fairlight via Filepro-list <filepro-list at lists.celestial.com> wrote:
>>>> No.  Best you can do is a set of conditional lookups with static
>>>> indexes:
>>>>
>>>> if: condition1
>>>> then: lookup [...] i=a
>>>> if: condition2
>>>> then: lookup [...] i-b
>>>> [etc.]
>>>>
>>>> m->
>>>>
>>>>
>>>> On Wed, Sep 15, 2021 at 03:41:05PM +0000, Richard Kreiss via Filepro-list thus spoke:
>>>>> I can'
>>>>> T remember can I use a wild card with an index.
>>>>>
>>>>> If I can. How?
>>>>>
>>>>> I am trying to find a date record using an index the date is field 4(10,myyy/). This means that the month and date would be 2 digits and the years 4 digits with a slash (/) separating them.
>>>>>
>>>>> Thanks for any assistance.
>>>>>
>>>>>
>>>>> Richard Kreiss
>>>>> GCC Consulting
>>>>>
>>>>>
>>>>> -------------- next part -------------- A non-text attachment was 
>>>>> scrubbed...
>>>>> Name: winmail.dat
>>>>> Type: application/ms-tnef
>>>>> Size: 14426 bytes
>>>>> Desc: not available
>>>>> URL: 
>>>>> <http://mailman.celestial.com/pipermail/filepro-list/attachments/2
>>>>> 0210915/b2173782/attachment.bin> 
>>>>> _______________________________________________
>>>>> Filepro-list mailing list
>>>>> Filepro-list at lists.celestial.com
>>>>> Subscribe/Unsubscribe/Subscription Changes 
>>>>> http://mailman.celestial.com/mailman/listinfo/filepro-list
>>>>>
>>>> --
>>>> Audio panton, cogito singularis.
>>>> _______________________________________________
>>>> Filepro-list mailing list
>>>> Filepro-list at lists.celestial.com
>>>> Subscribe/Unsubscribe/Subscription Changes 
>>>> http://mailman.celestial.com/mailman/listinfo/filepro-list
>>> _______________________________________________
>>> Filepro-list mailing list
>>> Filepro-list at lists.celestial.com
>>> Subscribe/Unsubscribe/Subscription Changes  
>>>http://mailman.celestial.com/mailman/listinfo/filepro-list
>>>    -------------- next part --------------  An HTML attachment was 
>>>scrubbed...
>>> URL: 
>>><http://mailman.celestial.com/pipermail/filepro-list/attachments/2021
>>>0916/6524bc05/attachment.html>  
>>>_______________________________________________
>>> Filepro-list mailing list
>>> Filepro-list at lists.celestial.com
>>> Subscribe/Unsubscribe/Subscription Changes  
>>>http://mailman.celestial.com/mailman/listinfo/filepro-list
>> --
>> Jose Lerebours
>> (954) 559-7186
>> -------------- next part -------------- An HTML attachment was 
>> scrubbed...
>> URL: 
>> <http://mailman.celestial.com/pipermail/filepro-list/attachments/2021
>> 0916/a7f78f47/attachment.html> 
>> _______________________________________________
>> Filepro-list mailing list
>> Filepro-list at lists.celestial.com
>> Subscribe/Unsubscribe/Subscription Changes 
>> http://mailman.celestial.com/mailman/listinfo/filepro-list
--
Jose Lerebours
(954) 559-7186
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.celestial.com/pipermail/filepro-list/attachments/20210916/d7346b9b/attachment.html>
_______________________________________________
Filepro-list mailing list
Filepro-list at lists.celestial.com
Subscribe/Unsubscribe/Subscription Changes http://mailman.celestial.com/mailman/listinfo/filepro-list

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.celestial.com/pipermail/filepro-list/attachments/20210917/0c4cd874/attachment.html>
_______________________________________________
Filepro-list mailing list
Filepro-list at lists.celestial.com
Subscribe/Unsubscribe/Subscription Changes http://mailman.celestial.com/mailman/listinfo/filepro-list