Re: [EXTERNAL] records between two dates

[Sam_L <lennon_s_j@xxxxxxxxxxx>]

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)