Re: [EXTERNAL] records between two dates

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.