Operator OR inside of Join clause in Open SQL issue

[MikeB]

Hi,

I'm trying to write SQL query with JOIN-clause in ABAP's Open SQL. The problem is that my JOIN contains operator OR inside of ON.

Example:

1. SELECT *
2. INTO CORRESPONDING FIELDS OF TABLE EDUCATION_ITAB
3. FROM tblStudent LEFT OUTER JOIN tblCourses
4.         ON ( tblStudent.ID1 = tblCourses.ID1 OR tblStudent.ID2 = tblCourses.ID2 ).

When I try to activate this code I got the error message: « You cannot use OR in ON conditions ». It seems strange, why I can use such basic stuff it in Microsoft SQL Server, but can't in Open SQL.

Is there any way to implement this query in Open SQL or should I move forward to Native SQL?

Thanks.

[FredericGirod]

Hi Mike,

SAP is not link to a specific database software. So it can't used all the possibility of all this software.

There is a tool that convert your ABAP Sql into specific database SQL commande line. This tool don't like your OR. That's all.

(forget to use "high level" of SQL statemetn inside SAP. Collect your data and managed it with ABAP code, it's simpler, better, ...)

regards

Fred

[MikeB]

Hi, Fred

Thanks for the « flash » answer, it was the fastest answer I ever got at SCN 😉

General question about «high level» of SQL, when I put my query inside of EXEC SQL…ENDEXEC does it will be proceeded as Native SQL or as T-SQL in case if MS SQL Server is used as DB-server?

[Former Member]

Hi Mike,

Fred & Shravan have explained the concept.

Please see Inner Join syntax and example at

http://help.sap.com/saphelp_rm65/helpdata/EN/e6/9e828fab72fb43b84a0badaddaaed9/content.htm.

BR.

[Former Member]

Putting an OR in an JOIN ON clause is not what I would call basic.  In fact in 15 years I've never seen the construct used before.  I've seen it used in where clauses.

Are you sure that is what you meant to write?

Neal

[MikeB]

Hi, Neal.

Yes. Operator OR is what I meant to write.

We have two ways to identificate the row (first-of-all by ID1 and only if ID1 is empty, thus by ID2), I agree, it's not a correct solution «according to the book», but due to some constraints & conditions I have to work with this structure of the table.

[Former Member]

Also please try not to use native SQL unless you are seeing serious performance problems. This will keep the program DB independent in case there is a change in the future.

Try using the where clause as already suggested for such conditions or into internal tables and some similar logic.

Regards,

Shravan