Re: [EXTERNAL] records between two dates

Hi,

When I ran WRKQRY based on this "In "Define result fields" you can, say,
define "theDate" as "field1 *
1000000 + field2*10000 + field3*100 + field4" and then use "theDate" in
"Select and sequence fields".

--->I got the query report the same as the SQL query in the file in terms
of records and record counts.


But The View count SQL query shows different record count than SQL Query
record count on the file ( the same file on which view is created)

So does this mean if the view is created on the PF (physical file) which
is formed using a Join SQL query's result ( like the sequence i shared in
my last post for file1) then View count could differ ?

Because it's happening i tested it without deleting any SQL query or file
and just tested the same like above WRKQRY built like above shows same
records like SQL query but the view( which is created on a physical file
which is formed as a result of join of other files like i mentioned in my
last post) when used in the WRKQRY shows different count both in WRKQRY
and in the STRSQL as well.

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
defining it's DDS then compiling it then inserting records in it either
through UPDDTA or INSERT SQL queries than the Physical file which gets
created( i created this Physical file by doing SHIFT+ F1 - Change Session
Attributes - then SELECT output - 3= File then i gave output file name
and mentioned my library name over there ) as a result of joining two or
more files in STRSQL.



Thanks.

On Sun, 3 Jul 2022 at 23:14, Sam_L <lennon_s_j@xxxxxxxxxxx> wrote:

Jerry,
You've now provided information that we didn't have before.
Given what you've said there, my recommendations:

1) *Delete everything* and start again.

2) Structure the SQL so it is easier to read for anyone on the list

3) Save each SQL in a file.

4) If you still have problems, publish the entirety of the *original*
SQLs in the order you ran them.

5) If you haven't installed iACS, install it and use it instead of STRSQL.

6) Try making your posts easier to read--maybe it's my old eyes but I
find them hard to comprehend. If English isn't your first language I'm
sorry, but you've got to help us a bit. Perhaps write them in an editor
and review them a bit before you post them.

7) You do know that in QRY/400 you can define new fields?
In "Define result fields" you can, say, define "theDate" as "field1 *
1000000 + field2*10000 + field3*100 + field4" and then use "theDate" in
"Select and sequence fields". "

On 7/3/2022 4:34 AM, jerry ven wrote:

Hi,

Just missed to mention that the PF (file1) whose structure ( it's fields
sequence, data types,length and scale) i had shared earlier is formed

using

the join queries like below:-


select a.fld1,b.fld2,a.fld2,a.fld3,a.fld4,a.fld5,a.fld6, from libx/filea1
a leftjoin libx/fileb1 b on digits(a.fld1) = trim(b.fld2) and b.fld1

like

'%ABCD%'

then above sql query gives me filea2 in my library lib1.

then using this filea2 i create another SQL query like below:-

select * from lib1/filea2 A left join libx/filea3 B on
digits(a.fld2) = trim(b.filea3)

then i get file1 - for which i shared sql queries ,it's structure and SQL
queries for views on this file.
but the type of this file1 is 'Physical file ' only.

I hope these details should help to figure it out to find the record

counts

discrepancy in this file1 and view V1 create on it ( i mean in my earlier
post i shared those SQL Queries 1.,2.,3.,4.) for the same file file1 and

i

also shared it's structure.

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