locked
sql server to oracle converting report RRS feed

  • Question

  • hi

     i create report in ssrs ,source i used as sql server now i convert those reports to oracle server so in my old report i used left outer join while converting this to oracle server that query is automatically changed like {oj} like this and also while executing that query its shows table name error or invalid table name how to fix this in SSRS

    Saturday, June 30, 2012 6:07 AM

Answers

  • Hi There

    Thanks for your posting. Which Version or Oracle you are using. As syntax for Left outer join is different in Oracle 8i to Oracle 9i onwards

    Please have a look on the similar issue in another post, This type of syntax is normally when you used ODBC drivers for oracle but I assumed you can change the query to left outer as below

    Left outer join:  Oracle8i
    select
       last_name,
       department_name
    from
       employees e,
       departments d
    where
       e.department_id = d.department_id(+);
    Left outer join:  Oracle9i
    select
       last_name,
       department_name
    from
       employees e
    left outer join
       departments d
    on
       e.department_id = d.department_id;

    https://forums.oracle.com/forums/thread.jspa?threadID=51756&tstart=1830

    If you have any question please do ask

    Many thanks

    Syed Qazafi Anjum

    • Proposed as answer by Edward Zhu Monday, July 9, 2012 8:16 AM
    • Marked as answer by Edward Zhu Tuesday, July 17, 2012 3:39 AM
    Saturday, June 30, 2012 7:13 AM

All replies

  • Hi There

    Thanks for your posting. Which Version or Oracle you are using. As syntax for Left outer join is different in Oracle 8i to Oracle 9i onwards

    Please have a look on the similar issue in another post, This type of syntax is normally when you used ODBC drivers for oracle but I assumed you can change the query to left outer as below

    Left outer join:  Oracle8i
    select
       last_name,
       department_name
    from
       employees e,
       departments d
    where
       e.department_id = d.department_id(+);
    Left outer join:  Oracle9i
    select
       last_name,
       department_name
    from
       employees e
    left outer join
       departments d
    on
       e.department_id = d.department_id;

    https://forums.oracle.com/forums/thread.jspa?threadID=51756&tstart=1830

    If you have any question please do ask

    Many thanks

    Syed Qazafi Anjum

    • Proposed as answer by Edward Zhu Monday, July 9, 2012 8:16 AM
    • Marked as answer by Edward Zhu Tuesday, July 17, 2012 3:39 AM
    Saturday, June 30, 2012 7:13 AM
  • The problem is the graphic query designer in VS.  When you add left, right, full joins then it adds { oj to the syntax.

    You can still do all your design in the graphical. When done: Switch to the TEXT view and replace { oj with (  then replace } with )

    Do NOT switch back to graphical view or it will jack it up again. Microsoft really needs to fix this fiasco. It's a pain for any one wanting to ditch Crystal Reports.

    Tuesday, March 29, 2016 11:09 PM