Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Question Unanswered: oracle old new join syntax

    Hello,

    We are using cr11 with oracle 9i.
    I create a report for oracle. The sqlstatement I create also hasa few joins in it. These joins have the old oracle-syntax like :
    select * from emp, dept where emp.deptno = dept.deptno(+)

    When I execute the report in crystal reports 11 everything is ok. When I get the sqlstatement through IReport::GetSqlQueryString() I get the sqlstatement with the new-oracle-join-syntax:
    select ename, dname, emp.deptno, dept.deptno
    from SCOTT.EMP LEFT outer join SCOTT.DEPT
    on emp.deptno = dept.deptno

    The problem with the new oracle-join syntax is that you can't join more columns than 1000, even if you select just 1 column.
    SELECT COLUMN1
    FROM X LEFT OUTER JOIN Y ON X.ID = Y.ID
    LEFT OUTER JOIN Z ON X.ID = Z.ID
    ETC......

    This is why we want the original query.

    Why don't I get the original-syntax? Is this strange cr11 behavior?
    Is it configurable? Can anyone tell me something about it?

    thanx beforehand,

    greetz Coen Dunnink
    The Netherlands

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm not sure what you mean by "you can't join more columns than 1000". Do you mean you have more than 1000 join conditions in your query?

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Exclamation Thanx for reading!!

    With "you can't join more columns than 1000" I mean:

    When table X has 300 columns and table y has 500 columns and table z has 300 columns. Than the next statement will result in the following error:
    SELECT COLUMN1
    FROM X LEFT OUTER JOIN Y ON X.ID = Y.ID
    LEFT OUTER JOIN Z ON X.ID = Z.ID
    -------------------------------
    ORA-01792: maximum number of columns in a table or view is 1000

    When you use the old join-syntax of oracle it will not result in this error-message.

    greetz Coen

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How annoying!

    Maybe you could re-write something like this:

    SELECT COLUMN1
    FROM X LEFT OUTER JOIN (SELECT ID,... FROM Y) Y ON X.ID = Y.ID
    LEFT OUTER JOIN (SELECT ID,... FROM Z) Z ON X.ID = Z.ID

    i.e. just involve the columns you actually want to use.

    Unless of course someone knows how to stop cr11 from re-writing your queries.

  5. #5
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Exclamation Many thanx

    Yes,

    That's a great solution!!

    thanx!

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Strangely though I just created a 600 column table and joined it to itself and got no error message on 9.2.0.6.0. So maybe this has been resolved in later releases than yours?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •