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

Discussion forum about all things SqlBase.
FRBhote
India
Posts: 2172
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

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

Post by FRBhote » 18 Mar 2021, 12:41

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?
You do not have the required permissions to view the files attached to this post.

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 274
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

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

Post by Steve Leighton » 19 Mar 2021, 01:38

.
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
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

FRBhote
India
Posts: 2172
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

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

Post by FRBhote » 19 Mar 2021, 05:40

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

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 274
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

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

Post by Steve Leighton » 20 Mar 2021, 05:06

.
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
/

Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

FRBhote
India
Posts: 2172
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

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

Post by FRBhote » 23 Mar 2021, 05:53

Finally had to do it with a view.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 1 guest