• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Can I do date range in Select Table I/O?

Andrew2009

Well Known Member
Below is my existing table I/O select. There's a Date field (DWK) also and I want to get records ONLY within a date range, for example in January, from 1/1/2017 to 1/31/2017. Can I do a Date range in the Table select I/O? If not, what other solutions can I do please?, right now the only solution I can think of is to loop through the list of returned records and check for the DWK field. But there could be a lot of records returned.

F06116.Select [Index 1: Address Num, Date Work, +B]
| VA rpt_szDS_BillingDetailJob_MCU [MCU] = TK Business Unit [MCU]
| BC Category Code - G/L2 (F5548112.0) [R002] = TK Subsidiary [SUB]



Thanks
 

craig_welton

Legendary Poster
Not directly in Table I/O. You can in C with the JDEBASE API. So, write a C function to do the query and pass the results back, if that makes sense. You can use a table handle with the Table I/O, pass that to a C function and have it perform the complex SetSelection on the handle, then do your fetch loop in your ER. There should be several examples here on the list.

Craig
 

johndanter

Legendary Poster
I do 2 things.
In a UBE I call a new UBE section and do it that way.

In an APPL or NER BSFN I create a date index on the table (asc or desc) and use a table IO select using GE to start, then have a loop looking for the variable LE and exit.

If your index is asc then start with GE (for your lower range date) and then the fetch next feeds them in by incremented date and you exit when you reach your threshold date
Messy, but it's worked for me as my C+ isn't the best.
Make sure you use open and close though
 
Last edited:
Not directly in Table I/O. You can in C with the JDEBASE API. So, write a C function to do the query and pass the results back, if that makes sense. You can use a table handle with the Table I/O, pass that to a C function and have it perform the complex SetSelection on the handle, then do your fetch loop in your ER. There should be several examples here on the list.

Craig
This is a interesting concept and it's the first I've heard of it. I'm not seeing anything on the 'list with more details, do you happen to know where I can find out more? In our custom code we often need table date range filtering (allowing blank start and/or end dates) as well as "best match" searching with wildcards, which I always assumed required 100% C BSFN code (harder to maintain) or inefficient and messy NERs. This would allow the best of both worlds.

Big fan of your tools, Craig! I'd go insane without them.
 
Call Orchestrations From Excel – The Easy Way to Make the Orchestrator Work for You.
Hi, so I was having a situation that I have to validate that one date, is in a range between two dates from a table by the time that I had to do a Table Operation(Fetch single) so with my date, I put it in those two fields and I validate that my date was less or equal than the Date - Expired and over or equal to my Date - effective.

Hope can help someone in the future.

1604613540314.png
 
Top