Re: [EXTERNAL] records between two dates -- MIDRANGE-L

If I need to verify the records are certain using this manner how can I
cross verify it through WRKQRY:-

I mean, if my sql query is like this :- ' SELECT * FROM LIB1/file1 where
((field1* 1000000) + ( field2*10000) + (field3*100) + field4) between
'20210601' and '20210607'

Then just to cross verify whether this SQL query shows the correct record
set then what is the counter WRKQRY I could prepare to be sure whether
both SQL and WRKQRY show the same result set here?

Thanks.

On Thu, 30 Jun 2022 at 22:24, Roger Harman <roger.harman@xxxxxxxxxxx> wrote:

Or, write a UDF to do the conversion. Be sure to specify it as
"deterministic" to aid in performance.

For clarity, I much prefer UDF's over calcs like this.

Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Alan Shore via MIDRANGE-L
Sent: Thursday, June 30, 2022 9:27 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: [EXTERNAL] records between two dates

Combine the field together in the following manner
((CC * 1000000) + (YY * 10000) + (MM * 100) + DD)

Alan Shore
Solutions Architect
IT Supply Chain Execution

[NHScsignaturelogo]

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx

'If you're going through hell, keep going.'
Winston Churchill

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of jerry ven
Sent: Thursday, June 30, 2022 12:01 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] records between two dates

Hi,

How to filter records from a date field if it's split in CC YY MM DD (
Each field here with 2 length and decimal data type) using SQL query from
a file?

i mean date field is like in file1 like below:-

fld1 fld2 fld3
fld4
CC YY MM
DD

So from this type of split fields how can we filter records for a specific
week, month, year and century ,and records between specific dates using
SQL query on this file1 ?

Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:
MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=m7dwPEgNWqV4UWa%2FBQ178kyqBaLWixJfIfIVNJ2by7o%3D&reserved=0
<
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=m7dwPEgNWqV4UWa%2FBQ178kyqBaLWixJfIfIVNJ2by7o%3D&reserved=0

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:
MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=EZwt75YdXWAlkv7CO27ZGxpmhrdfkegWElWL5xhPSPY%3D&reserved=0
<
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=EZwt75YdXWAlkv7CO27ZGxpmhrdfkegWElWL5xhPSPY%3D&reserved=0

.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:
support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com%2F&data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=tUlTmPYWZozfUnZuYUZdIqtwgTyfLbieenJ3q6UaAAs%3D&reserved=0
<
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com%2F&data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=tUlTmPYWZozfUnZuYUZdIqtwgTyfLbieenJ3q6UaAAs%3D&reserved=0

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=m7dwPEgNWqV4UWa%2FBQ178kyqBaLWixJfIfIVNJ2by7o%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=EZwt75YdXWAlkv7CO27ZGxpmhrdfkegWElWL5xhPSPY%3D&reserved=0
.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com%2F&data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=tUlTmPYWZozfUnZuYUZdIqtwgTyfLbieenJ3q6UaAAs%3D&reserved=0
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com