× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.