|
Code:
- 1. SQL-if my sql query is like this :- ' SELECT * FROM LIB1/file1 where
- ((field1* 1000000) + ( field2*10000) + (field3*100) + field4) between
- '20210601' and '20210607'
-
- 2. 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)
-
- 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) )
-
-
- File1(PF) structure:-
-
-
- field type length scale
- field1 decimal 6
- field2 character 9
- field3 decimal 7
- field4 decimal 2 CC
- field5 decimal 2 YY
- field6 decimal 2 MM
- field7 decimal 2 DD
- field8 character 7
- field9 character 30
- field10 character 3
- field11 character 1
- field12 character 1
- field13 decimal 2 MM
- field14 decimal 2 DD
- field15 decimal 2 CC
- field16 decimal 2 YY
- field17 decimal 2 MM
- field18 decimal 2 DD
- field19 decimal 2 CC
- field20 decimal 2 YY
- field21 decimal 2 MM
- field22 decimal 2 DD
- field23 decimal 2 CC
- field24 decimal 2 YY
- field25 character 10
- field26 character 10
- field27 character 6
-
-
- *****************************************************************************************************************
-
- Above structure of file1 is obtatined by joining some files 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 finally the file file1 - for which i shared above sql queries 1.,2.,3.,4. ( on file1) and views (V1)
-
- And above SQL queries on file1(1,2,3,4) and view (v1) go executed without any error( i did not find any typo errors while executing them).
-
-
-
-
-
-
-
-
-
-
|
|