Re: [EXTERNAL] records between two dates

[jerry ven <jerryven95@xxxxxxxxx>]

Hi,

I just changed field names and file name and library name here but on the
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.


Thanks.



On Fri, 1 Jul 2022 at 23:38, jerry ven <jerryven95@xxxxxxxxx> wrote:

> Hi,
>
> I tried creating SQL query on main file file1 and view V1 on this like
> below:-
>
> SQL-if my sql query is like  this :-  ' SELECT * FROM LIB1/file1  where
> ((field1* 1000000) + ( field2*10000) + (field3*100) + field4)  between
> '20210601'  and  '20210607'
>
> View:- CREATE VIEW LIB1/V1 AS ( SELECT DIGITS(DEC(FIELD1*1000000)  +
> (FIELD2*10000)  +  (FIELD3*100)   +  FIELD4,8,0)) AS MYDATE , A.*  FROM
> LIB1/FILE1  A)
>
>
> Then tried below as well after including/removing single quotes from
> numbers for below SQL queries ( on file1) and View Queries (V1):-
>
> 1.   SELECT count(*) FROM LIB1/file1  where
>         ((field1* 1000000) + ( field2*10000) + (field3*100) + field4)
>  between
>           '20210601'  and  '20210607'
>
>
> 2.            SELECT count(*) FROM LIB1/file1  where
>                ((field1* 1000000) + ( field2*10000) + (field3*100) +
> field4)  between
>                20210601  and  20210607
>
>
>
>
>
> 3.  select count(*) from lib1/v1 where MYDATE  between '20210601'  and
>  '20210607'
>
>
>
> 4)  select count(*) from lib1/v1 where MYDATE  between 20210601  and
>  20210607
>
>
>
> Output :-  1.  = 2.  for record count.
>            3.  = 4.  for record count.
>
> but record count for (1. and 2. SQL Queries ( on file1) was too less than
>  compared to record count for (3. and 4. (SQL queries for record count for
> view (V1)  )
>
>
> So What correction either in SQL query on file1 or in the SQL query on
> view V1 need to be done here or is there any correction required in both
> here  ?
>
>
>
> Thanks.
>
>
>
>
>
>
>
> On Fri, 1 Jul 2022 at 22:11, Therrien, Paul <ptherrien@xxxxxxxxxxx> wrote:
>
> > You are in debug and data analysis mode now.
> >
> > If your view is not getting the set of data you are expecting then your
> > view definition needs to be looked at and your data analysis assumptions
> > need to be looked at.
> >
> >
> >
> >
> >
> >
> >
> > *From:* jerry ven <jerryven95@xxxxxxxxx>
> > *Sent:* Friday, July 1, 2022 12:35 PM
> > *To:* Therrien, Paul <ptherrien@xxxxxxxxxxx>
> > *Cc:* Midrange Systems Technical Discussion <
> > midrange-l@xxxxxxxxxxxxxxxxxx>
> > *Subject:* Re: [EXTERNAL] records between two dates
> >
> >
> >
> > Hi,
> >
> >
> >
> > I meant after changing the date to some other date name like 'mydate':-
> >
> >
> >
> > Select count(*) from V1 where mydate between 20210601 and 20210607’
> >  Became  equivalent to WRKQRY's report count.
> >
> >
> >
> > ... But the main file on which this view was based has  too low record
> > count compared to this view count i mean for below main SQL query:-
> >
> >
> >
> >  ' SELECT * FROM LIB1/file1  where ((field1* 1000000) + ( field2*10000) +
> > (field3*100) + field4)  between  '20210601'  and  '20210607'
> >
> >
> >
> > It shows very   few records compared to the above  View (V1) Query's
> > count.
> >
> >
> >
> >
> >
> > Thanks.
> >
> >
> >
> >
> >
> > midrange.com by shopping at amazon.com with our affiliate link:
> > https://amazon.midrange.com