This seems like an odd problem. Have you tried putting the date result in a var before you run the select and then use the var:
set var vd date=.#date-90 select … where mydate > .vd maybe the date add is confusing the optimizer Troy From: 'Karen Tellef' via RBASE-L <[email protected]> Sent: Friday, December 13, 2024 8:35 AM To: 'Daniel Goldberg' via RBASE-L <[email protected]> Subject: Re: [EXTERNAL] [RBASE-L] - RE: date minus slow I was gonna suggest something similar, but thought "no that'd be too silly". But here goes. If you can, add another column to the Invoice table. You can even call it DateCol. Before you do your Select command, do an update to "set datecol = (.#date - 90)" Then you can say "orddate > datecol". Keeping it all in one table will greatly reduce the time compared to introducing another table. If you CAN add a column (or sometimes I find columns in a table that are no longer used, check for unused date columns?) Karen On Friday, December 13, 2024 at 08:00:17 AM CST, 'Daniel Goldberg' via RBASE-L <[email protected]<mailto:[email protected]>> wrote: Hmm interesting idea. got the wheels spinning… Thanks Dan Goldberg From: [email protected]<mailto:[email protected]> <[email protected]<mailto:[email protected]>> On Behalf Of Steve Sweeney Sent: Thursday, December 12, 2024 3:39 PM To: [email protected]<mailto:[email protected]> Subject: [EXTERNAL] [RBASE-L] - RE: date minus slow Dan, This is a silly idea which might be worth a try. If you create a table for that purpose: TableName CurrentDate_LessDays ColumnName Type DEFAULT ComputedColumnExpression CurrentDate DATE .#DATE LessDays INTEGER 90 LessDate DATE (CurrentDate – LessDays) All you need is one row – update the CurrentDate and/or the LessDays at will. Simply join CurrentDate_LessDays.LessDate in place of (.#date - 90) Sounds a bit ridiculous, but it just may work. 😊 Steve Sweeney From: 'Daniel Goldberg' via RBASE-L <[email protected]<mailto:[email protected]>> Sent: Thursday, December 12, 2024 2:41 PM To: [email protected]<mailto:[email protected]> Subject: [RBASE-L] - date minus slow I have a select statement running in oterro. SELECT INVOICE.INVOICENUM, INVOICE.DLRNAME, INVOICE.SHIPNAME, INVOICE.PURCHSENUM, INVOICE.ORDERBY, INVOICE.ORDDATE, INVOICE.SHIPDATE, INVOICE.SHIPVIA, INVOICE.INVBO FROM INVOICE inner join useraccess on INVOICE.dlrnum = useraccess.dlrnum where (useraccess.logon_name = 'sw059') and INVOICE.orddate > (.#date - 90) order by orddate desc It runs but slow. I have narrowed the slowness down to the (.#date – 90), which brings up orders 90 days and newer. If I change it to 9/12/2024, the results come up instantly. I was wondering if anyone had another way to bring up the last 90 days. Since it is oterro I cannot set a variable before it runs. TIA Dan Goldberg -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. To view this discussion visit https://groups.google.com/d/msgid/rbase-l/BY3PR19MB5027E860666B2BC483579510D43F2%40BY3PR19MB5027.namprd19.prod.outlook.com<https://groups.google.com/d/msgid/rbase-l/BY3PR19MB5027E860666B2BC483579510D43F2%40BY3PR19MB5027.namprd19.prod.outlook.com?utm_medium=email&utm_source=footer>. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. To view this discussion visit https://groups.google.com/d/msgid/rbase-l/BYAPR03MB37182BD931A99385B42F9DAD893F2%40BYAPR03MB3718.namprd03.prod.outlook.com<https://groups.google.com/d/msgid/rbase-l/BYAPR03MB37182BD931A99385B42F9DAD893F2%40BYAPR03MB3718.namprd03.prod.outlook.com?utm_medium=email&utm_source=footer>. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. To view this discussion visit https://groups.google.com/d/msgid/rbase-l/BY3PR19MB5027794BE8C372AA765ACD8FD4382%40BY3PR19MB5027.namprd19.prod.outlook.com<https://groups.google.com/d/msgid/rbase-l/BY3PR19MB5027794BE8C372AA765ACD8FD4382%40BY3PR19MB5027.namprd19.prod.outlook.com?utm_medium=email&utm_source=footer>. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. To view this discussion visit https://groups.google.com/d/msgid/rbase-l/1002726294.2554697.1734104086994%40mail.yahoo.com<https://groups.google.com/d/msgid/rbase-l/1002726294.2554697.1734104086994%40mail.yahoo.com?utm_medium=email&utm_source=footer>. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/rbase-l/EA2P222MB1187CFADA3E79C0F49C6A71E9C042%40EA2P222MB1187.NAMP222.PROD.OUTLOOK.COM.
