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

Hi,

Below is the structure of that file1 from which i was saying (
field4(CC),field5(YY) ,field6(MM), field7(DD) ) i need the records between
these dates (20210601 and 20210607) between these fields
field4,field5,field6,field7 ( in my SQL query I was referring these fields
as field1,field2,field3,field4)

There are some other fields as well in this same file( File1) where I have
some other Date fields split in MM,DD,CC,YY format( between field13 to
field24 there are 3 such other date fields as well). but i need the data
between two dates (for example between (20210601 and 20210607) with respect
to field4,field5,field6 and field7 considering the same only i was
referring my SQL queries earlier.

structure of this file is like below ( taken from select * from file1 by
doing F4 on 'Select and Sequence Fields) :-

field type length scale
field1 decimal 6
field2 character 9
field3 decimal 7
field4 decimal 2 CC
field5 decimal 2 YY
field6 decimal 2 MM
field7 decimal 2 DD
field8 character 7
field9 character 30
field10 character 3
field11 character 1
field12 character 1
field13 decimal 2 MM
field14 decimal 2 DD
field15 decimal 2 CC
field16 decimal 2 YY
field17 decimal 2 MM
field18 decimal 2 DD
field19 decimal 2 CC
field20 decimal 2 YY
field21 decimal 2 MM
field22 decimal 2 DD
field23 decimal 2 CC
field24 decimal 2 YY
field25 character 10
field26 character 10
field27 character 6

Thanks

On Sat, 2 Jul 2022 at 01:17, Rob Berendt <rob@xxxxxxxxx> wrote:

I seem to recall saying that you needed to stop comparing numerics to
characters...
<snip>
Your next problem is that you are comparing a numeric column to a
character column:
</snip>
Looks like you're figuring that out.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
jerry ven
Sent: Friday, July 1, 2022 2:08 PM
To: Therrien, Paul <ptherrien@xxxxxxxxxxx>
Cc: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXTERNAL] records between two dates

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.

Hi,

I tried creating SQL query on main file file1 and view V1 on this like
below:-

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

View:- CREATE VIEW LIB1/V1 AS ( SELECT DIGITS(DEC(FIELD1*1000000) +
(FIELD2*10000) + (FIELD3*100) + FIELD4,8,0)) AS MYDATE , A.* FROM
LIB1/FILE1 A)

Then tried below as well after including/removing single quotes from
numbers for below SQL queries ( on file1) and View Queries (V1):-

1. SELECT count(*) FROM LIB1/file1 where
((field1* 1000000) + ( field2*10000) + (field3*100) + field4)
between
'20210601' and '20210607'

2. SELECT count(*) FROM LIB1/file1 where
((field1* 1000000) + ( field2*10000) + (field3*100) +
field4) between
20210601 and 20210607

3. select count(*) from lib1/v1 where MYDATE between '20210601' and
'20210607'

4) select count(*) from lib1/v1 where MYDATE between 20210601 and
20210607

Output :- 1. = 2. for record count.
3. = 4. for record count.

but record count for (1. and 2. SQL Queries ( on file1) was too less than
compared to record count for (3. and 4. (SQL queries for record count for
view (V1) )

So What correction either in SQL query on file1 or in the SQL query on view
V1 need to be done here or is there any correction required in both here ?

Thanks.

On Fri, 1 Jul 2022 at 22:11, Therrien, Paul <ptherrien@xxxxxxxxxxx> wrote:

You are in debug and data analysis mode now.

If your view is not getting the set of data you are expecting then your
view definition needs to be looked at and your data analysis assumptions
need to be looked at.

*From:* jerry ven <jerryven95@xxxxxxxxx>
*Sent:* Friday, July 1, 2022 12:35 PM
*To:* Therrien, Paul <ptherrien@xxxxxxxxxxx>
*Cc:* Midrange Systems Technical Discussion <

midrange-l@xxxxxxxxxxxxxxxxxx

*Subject:* Re: [EXTERNAL] records between two dates

Hi,

I meant after changing the date to some other date name like 'mydate':-

Select count(*) from V1 where mydate between 20210601 and 20210607’
Became equivalent to WRKQRY's report count.

... But the main file on which this view was based has too low record
count compared to this view count i mean for below main SQL query:-

' SELECT * FROM LIB1/file1 where ((field1* 1000000) + ( field2*10000) +
(field3*100) + field4) between '20210601' and '20210607'

It shows very few records compared to the above View (V1) Query's

count.

Thanks.

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

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