Re: [EXTERNAL] records between two dates

Hi,

I just meant there is difference of too many records for below two SQL
queries:-

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


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


And it does not matter whether i keep single quotes around these date
numbers or not , difference of many records remain always.


Thanks




On Tue, 5 Jul 2022 at 01:08, jerry ven <jerryven95@xxxxxxxxx> wrote:

Hi,

I just noticed that:-

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

Just missed that pair of parentheses before DEC and after Field4,8,0.

Rest all steps should be fine to understand why there is is difference of
record counts in the view and file?


Thanks

On Tue, 5 Jul 2022 at 00:46, Sam_L <lennon_s_j@xxxxxxxxxxx> wrote:

Jerry,

I cannot help further.

Maybe some else who volunteers on the list can help. And note that
there are now approximately 45 messages in this thread and volunteers
may not have the energy to scan prior messages.

As for typos, I will point out that I actually tried your create view
statement and it had syntax errors. (I know, what you posted it wasn't
the actual statement, but...) Again, this is why I suggest you start
over, keep track of your code, and post it.

I also said "On another front, I don't know how QRY/400 will treat a
view as input, which is why I pointed you to the "Define result fields"
approach for verification. if you are required to verify with QRY/400."

And "field1* 1000000) + ( field2*10000) + (field3*100) + field4)" is
basic mathematics.
cc 19 * 1000000 = 19000000
yy 98 * 10000 = 980000
mm 12 * 100 = 1200
dd 25 = 25
Add them together 19981215

Copying code you don't understand can be deathly dangerous!

Hope you solve your issue.


On 7/4/2022 2:44 PM, jerry ven wrote:

Hi,

However i had already shared the structure of file1 in my earlier post

also

shared SQL queries on file1 and view (V1) created on it , also i had

shared

how i had joined other files to form this file1,,may be it might have

been

not getting displayed properly due to some format issue while posting

in my

earlier posts.

Please find all these SQL queries and the file1's structure ( main

physical

file (file1) on which view v1 was created and sql query was formed ) in
the below midrange link.

https://code.midrange.com/edb5db827f.html

I was able to execute all these SQL queries without any typo error

,please

help to let me know the reason for the different record counts for main
file1 and in the view created on it for the same dates selection

criteria.

*Note* - What is the reason to do this calculation like -(field1*

1000000)

+ ( field2*10000) + (field3*100) + field4) for all these fields , I

mean

how is this calculation has been done, could someone please elaborate it
here ( so that it form as CCYYMMDD) ?

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