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


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

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.


On Sun, 3 Jul 2022 at 13:45, jerry ven <jerryven95@xxxxxxxxx> wrote:


i tried below suggestion as well:-

create or replace table lennons1.file1 (
field1 decimal (2 , 0),
field2 decimal (2 , 0),
field3 decimal (2 , 0),
field4 decimal (2 , 0)
insert into lennons1.file1

CREATE or replace VIEW lennons1.V1 AS (
(FIELD1*1000000) +
(FIELD2*10000) +
(FIELD3*100) +
,8,0) as mydate
FROM lennons1.FILE1 A

View created here (V1) shows the correct record counts and the correct
records for this file (file1) here considering these inserted values here.
in file1.


On Sun, 3 Jul 2022 at 12:28, jerry ven <jerryven95@xxxxxxxxx> wrote:

Qry/400 and View show the same result as I mentioned earlier.( i mean
when i use the view in WRKQRY both select count(*) from lib1/v1 and
WRKQRY's report count is exactly same when i used this view in the WRKQRY
and used that RANGE (in test) for date 20210601 20210607)

But that record count in view and file differ this is i am not able to
figure out so far why the view which is based on the same file show
different record count from its based file for same selection criteria (i
mean for these dates), i tried both ways by removing single quotes and
including single quotes from the dates but in both cases record count in
the view was too much than the file for the same dates selection criteria)
all these 4 fields (fiield4,field5,field6,field7) are pure decimal only
(with packed data type and 0 decimal position)
also i already shared the structure of this file ( like how is the
sequence and their data types and length of all the fields in this file) i
am not sure what else is required to explain it in more detail ?

I also compared record by record and then i found that view has too
many additional records compared to the file apart from the matching
records( the records which match from this file in the view are exactly
same but view has many additional records too which were not found in the
file for the same dates (between) selection criteria)

also i checked that view is based on physical file only and in the same
library only.


On Sat, 2 Jul 2022 at 23:48, Sam_L <lennon_s_j@xxxxxxxxxxx> wrote:

Since you are getting different numbers you've got to do some debugging.

Maybe qry400 and sql handle bad data differently? Is all data in the 4
fields definitely valid decimal data?

You may also want to compare record by record the results of using the
view vs using the file.

You are sure that the view is built over the correct physical file in
the right library ? And it's not a logical?

And to repeat what others have said, without seeing actual code and
results, we're kind of stuck.

On 7/1/2022 2:59 PM, jerry ven wrote:

Ok, so if the record counts for (1. and 2. SQL Queries ( on file1) was
less than compared to record count for (3. and 4. (SQL queries for
count for view (V1) )
then is it fine or acceptable or is there any probability of Typos -
field names, transposed characters here which might cause this
in terms of record counts here?

I mean to ask, is it OK to say that a view can have more records
to the file on which it is based ?

or conceptually it's wrong here? ( considering my SQL queries and
Views for
which I shared Queries in my last posts ( 1.,2.,3.,4.)

if it's right then I have equal records for my view and in the WRKQRY
if it's wrong then my view and WRKQRY has too many more records
compared to
the SQL queries ( on file1 SQL Queries 1. and 2.)


On Sat, 2 Jul 2022 at 00:05, Therrien, Paul <ptherrien@xxxxxxxxxxx>

Typos – bad field names, transposed characters.

Proof of concept? … everything you have written should work – so if
isn’t working then you have some bad code somewhere.

SQL works fine. QRY works fine.

*From:* jerry ven <jerryven95@xxxxxxxxx>
*Sent:* Friday, July 1, 2022 2:33 PM
*To:* Therrien, Paul <ptherrien@xxxxxxxxxxx>
*Cc:* Midrange Systems Technical Discussion <

*Subject:* Re: [EXTERNAL] records between two dates


But how would it make a difference? I just want to make a proof of
for this. How does it matter whether the field name is field1 or
'z' etc.


On Fri, 1 Jul 2022 at 23:56, Therrien, Paul <ptherrien@xxxxxxxxxxx>

You need to share the actual code and the actual file layouts.

*From:* jerry ven <jerryven95@xxxxxxxxx>
*Sent:* Friday, July 1, 2022 2:24 PM
*To:* Therrien, Paul <ptherrien@xxxxxxxxxxx>
*Cc:* Midrange Systems Technical Discussion <

*Subject:* Re: [EXTERNAL] records between two dates


I just changed field names and file name and library name here but on
actual file and field names when I replace these SQL queries with the
actual file and field names then getting the same kind of results as I
shared here.


filiate link: https://amazon.midrange.com

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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 On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2023 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.