Operators at the same precedence level are applied from left to right not happening

[FRBhote]

Attached is a small table X which can be loaded into ISLAND.

The statement:

Code: Select all

SELECT  @DATE( @MID( PF_CORP_ACTION, 12, 4 ), @MID( PF_CORP_ACTION, 9, 2 ), @MID( PF_CORP_ACTION, 6, 2 ) )
		FROM X
		WHERE  ( ( @LEFT(PF_CORP_ACTION, 5 ) = 'SPLIT' )) ;

gives:

Code: Select all

    DT
===========
08-APR-2020

But,

Code: Select all

SELECT  @DATE( @MID( PF_CORP_ACTION, 12, 4 ), @MID( PF_CORP_ACTION, 9, 2 ), @MID( PF_CORP_ACTION, 6, 2 ) ) as dt
		FROM X
		WHERE  ( ( @LEFT(PF_CORP_ACTION, 5 ) = 'SPLIT'  
AND @DATE( @MID( PF_CORP_ACTION, 12, 4 ), @MID( PF_CORP_ACTION, 9, 2 ), @MID( PF_CORP_ACTION, 6, 2 ) ) < 2020-03-31 )  ) ;

gives Error: 00318 EXE DNN Data is not numeric.

Since it is in an AND and evaluated left to right, if the first predicate fails, the second should not be evaluated.

Or am I doing something wrong?

[Steve Leighton]

.
Use a self-join. This way 'A' is a subset of 'B', kind of like a view.
Don't know why the precedence isn't working, but joining X to itself seems to do the job.

Code: Select all

SELECT 
@DATE( 
@MID( A.PF_CORP_ACTION, 12, 4 ), 
@MID( A.PF_CORP_ACTION, 9, 2 ), 
@MID( A.PF_CORP_ACTION, 6, 2 ) )

FROM X A , X B

WHERE 
( A.PF_CORP_ACTION = B.PF_CORP_ACTION
AND   
@LEFT(B.PF_CORP_ACTION, 5 ) = 'SPLIT' )
AND
@DATE( 
@MID( A.PF_CORP_ACTION, 12, 4 ), 
@MID( A.PF_CORP_ACTION, 9, 2 ), 
@MID( A.PF_CORP_ACTION, 6, 2 )    ) < 2020-03-31

[FRBhote]

Thanks.

Did it work for you? I get the same error:

Code: Select all

SELECT
@DATE(
@MID( A.PF_CORP_ACTION, 12, 4 ),
@MID( A.PF_CORP_ACTION, 9, 2 ),
@MID( A.PF_CORP_ACTION, 6, 2 ) )

FROM X A , X B

WHERE
( A.PF_CORP_ACTION = B.PF_CORP_ACTION
AND
@LEFT(B.PF_CORP_ACTION, 5 ) = 'SPLIT' )
AND
@DATE(
@MID( A.PF_CORP_ACTION, 12, 4 ),

^
Error: 00318 EXE DNN Data is not numeric

[Steve Leighton]

.
The code snippet in your last post is malformed.
The following works fine on SB 12.1.1

Code: Select all

SELECT 
@DATE( 
@MID( A.PF_CORP_ACTION, 12, 4 ), 
@MID( A.PF_CORP_ACTION, 9, 2 ), 
@MID( A.PF_CORP_ACTION, 6, 2 ) )

FROM X A , X B

WHERE 
( A.PF_CORP_ACTION = B.PF_CORP_ACTION
AND   
@LEFT(B.PF_CORP_ACTION, 5 ) = 'SPLIT' )
AND
@DATE( 
@MID( A.PF_CORP_ACTION, 12, 4 ), 
@MID( A.PF_CORP_ACTION, 9, 2 ), 
@MID( A.PF_CORP_ACTION, 6, 2 )    ) < 2020-04-30

Result:
08-APR-2020

If you change the where clause to '< 2020-03-31' as per your original post, you get no rows found ( which is correct ) but certainly no error.

If the above doesn't work for you, create a View and select from that.

Code: Select all

Create view vX_SPLIT ( vx_SPLITdate ) as
SELECT @DATE( 
@MID( PF_CORP_ACTION, 12, 4 ), 
@MID( PF_CORP_ACTION, 9, 2 ), 
@MID( PF_CORP_ACTION, 6, 2 ) ) 
FROM X
WHERE ( ( @LEFT(PF_CORP_ACTION, 5 ) = 'SPLIT' )) ;
/
Select vx_SPLITdate from vX_SPLIT where vx_SPLITdate <  2020-03-31
/

[FRBhote]

Finally had to do it with a view.