Re: [EXTERNAL] records between two dates

Jerry,
You got to cut and paste better...

Your create view doesn't work:
- it's got the parenthesis wrong.
- And it doesn't need the to include digits, because you are already working with decimal data. (Though this may not make any difference.)

May I suggest you indent you code so beginning and ending of functions and such line up, perhaps like this:

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
values
(19,98,12,25),
(00,01,12,31),
(99,99,12,31)
;

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

Also, if you are working with SEU, or STRSQL, you have my sympathy.



On 7/1/2022 2:08 PM, jerry ven 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)