Left Outer join to (+)

Discussion forum about all things SqlBase or SqlTalk
FRBhote
India
Posts: 2206
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Left Outer join to (+)

Post by FRBhote » 30 Dec 2021, 06:43

I'm using SB 8.5 and the LEFT OUTER JOIN is not at all efficient.

The predicate:

Code: Select all

 Z LEFT OUTER JOIN Z6 ON PTY = PARTY AND  @DECODE( CITY, 'B', 'Z', 'fm', 'fn', CITY )  =   @DECODE( MCITY, 'B', 'Z', 'fm', 'fn', MCITY ) 
works fine, but inefficient.

However with a (+) I get this error:

Code: Select all

	 PTY = PARTY(+) AND  @DECODE( CITY, 'B', 'Z', 'fm', 'fn', CITY )  =   @DECODE( MCITY, 'B', 'Z', 'fm', 'fn', MCITY ) (+)  AND
		                                                                                                            ^
Error: 00901 PRS CNE Command not properly ended
Any suggestions will be welcome.

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

Re: Left Outer join to (+)

Post by Steve Leighton » 02 Jan 2022, 02:18

.
as with Oracle, I believe the outer join can be specified only on column names. In this case MCITY inside the Decode, not on the whole Decode expression.

Code: Select all

Where
PTY = PARTY(+) AND
@DECODE( CITY, 'B', 'Z', 'fm', 'fn', CITY )  =   @DECODE( MCITY(+) , 'B', 'Z', 'fm', 'fn', MCITY(+)   )
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

Return to “SqlBase General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 1 guest