cancel
Showing results for 
Search instead for 
Did you mean: 

Maxdb 7.9 SQL Question (+) operator

obermiller_w
Explorer
0 Kudos

Hi, I am slightly stumped on an sql query that ran without problems on

maxdb 7.6.027 for many years, but now returns error

-5010  Missing value specification on maxdb 7.9.27:

SELECT    v.id, v.modul, v.version, m.rechte, m.rechte2, m.rechte3, m.rechte4

FROM    versions v, menurechte m

WHERE    v.id >= 0 AND v.id = m.id ( +)

AND m.login ( +) = 'SCHWARTZ'   

ORDER BY v.id

The database server (7.9.27) runs under linux and the application is run on a

WIN-XP-client, unsing the original maxdb 7.6. ODBC driver.

Is there a compatibility setting that will allow me to execute the join syntax above

on a 7.9.27 server?

Any hint on how to deal with this would be greatly appreciated, as I cannot modify the

client (XP) application.

Accepted Solutions (1)

Accepted Solutions (1)

thorsten_zielke
Contributor
0 Kudos

Hi,

if you are a SAP customer, please have a look at SAP note 1325691.

The problem occurs, because the Oracle Outer Join syntax [+] is not supported by default in newer MaxDB versions anymore, because it is outdated and not fully ANSI standard compliant.

You now have two options:

1. Rewrite your SQL (and in addition benefit from the QueryRewrite MaxDB feature)
Example

select * from dual t1, dual t2

where t1.dummy = t2.dummy ( +)

and t1.dummy = 'x'

Ansi Outer Join Syntax:

select * from dual t1 left outer join dual t2 on t1.dummy = t2.dummy

where t1.dummy = 'x'

2. Change EnableLegacyOuterJoinSyntax to YES
Changing this MaxDB parameter will support the old join syntax, but disable the QueryRewrite feature for these 'old' joins. Still, the new join syntax should be faster depending on the query...

Regards,
Thorsten

obermiller_w
Explorer
0 Kudos

Worked after changing the EnableLegacyOuterJoinSyntax to YES and restarting the DB.

Thanks!

Answers (0)