Re: [EXTERNAL] records between two dates

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.

Also if there is not any difference in records in SQL Query for file1 and
the view created on it for the same date selection criteria , then when we
will use this view in QUERY/400(WRKQRY) then it should also show same
records ( like SQL query of file1 and view(V1) created on it for the same
date 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) ?


Thanks.
















On Mon, 4 Jul 2022 at 23:35, Sam_L <lennon_s_j@xxxxxxxxxxx> wrote:

"Main point -- Why the view which is created on a physical file(physical
file which itself is created by joining two or more files like i shared
in my last post with SQL queries examples) shows much more records than
the file(file1 in my SQL queries examples 1.,2.,3,,4.)"

Doesn't matter how the PF was created. The consensus of those who have
commented here is that:

1) SQL on a view created over a PF

and

2) SQL on the PF using the same criteria you used to create the view

should give the same results.

If not then you must have an error (maybe a typo) in the SQL creating
the view or in the SQL using the view. But since you won't or can't show
the code, then there is no help we can give.


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.


"Also how would IBM i ACS be superior to traditional STRSQL here , I
mean what I can't do in STRSQL can do using IBM i ACS here?"

STRSQL is old and it is difficult to make changes to what you've keyed.
And easy to pick the wrong SQL when you go back to the history. That's
why I suggest you delete everything and start again.

With iACS you have the benefit of a GUI editor and is easier to change
code, you can easily save and reload code to files on your PC or on the
IBM i. It can have multiple panes open, each with a separate SQL
statement. It can also reformat code. And it has a lot of examples. It
leaves STRSQL in the dust.

But to each his own...


>On 7/4/2022 7:56 AM, jerry ven wrote:

Also how would IBM i ACS be superior to traditional STRSQL here , I mean
what I can't do in STRSQL can do using IBM i ACS here?



Main point -- Why the view which is created on a physical file( physical
file which itself is created by joining two or more files like i shared

in

my last post with SQL queries examples) shows much more records than the
file(file1 in my SQL queries examples 1.,2.,3,,4.)

is there a difference in a Physical file which is traditionally created

by

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