Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle 8i syntax

Posted on 2009-05-15
3
Medium Priority
?
518 Views
Last Modified: 2013-12-19
I have a SQL statement that I wrote in 9i. I tried to run it in Visual studio and apparently the oracle data adapter that we are using only recognizes the 8i syntax. I wanted to know if anyone knew how to convert the following statement into its equivilent 9i counterpart...

SELECT DISTINCT CC.CABLE_NUMBER, CD.DRAWING_NUMBER  
           FROM  (CMS_DUC.CABLE CC LEFT JOIN EIDB_DUC.CABLE EC
                      ON CC.CABLE_NUMBER = EC.CABLE_NO)  
I                     INNER JOIN CMS_DUC.DRAWING CD  
                      ON CC.MASTER_SCHEMATIC = CD.DRAWING_ID
         WHERE EC.CABLE_NO Is Null
         ORDER BY CC.CABLE_NUMBER  ;
0
Comment
Question by:imonfireDAMMIT
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24399548
oracle 8 does not know the JOIN syntax:
SELECT DISTINCT CC.CABLE_NUMBER, CD.DRAWING_NUMBER  
    FROM CMS_DUC.CABLE CC 
      , EIDB_DUC.CABLE EC
      , CMS_DUC.DRAWING CD  
      WHERE CC.CABLE_NUMBER (+) = EC.CABLE_NO
        AND CC.MASTER_SCHEMATIC = CD.DRAWING_ID 
        AND EC.CABLE_NO Is Null 
     ORDER BY CC.CABLE_NUMBER  ;

Open in new window

0
 
LVL 1

Author Comment

by:imonfireDAMMIT
ID: 24399738
Thank YOU SOOO MUCH...i was pretty close. I had this:

SELECT DISTINCT CC.CABLE_NUMBER, CD.DRAWING_NUMBER  
        From  cms_duc.cable cc, eidb_duc.cable ec, cms_duc.drawing cd
        where cc.master_schematic = cd.drawing_id
              and ec.cable_no = cc.cable_number (+)
              and ec.cable_no is null
        order by cc.cable_number;   I see that my error was in the placement of the (+)...i put it on the other side of the = and it worked...

Your statement also put the (+) on the wrong side. WHen I switched it...it worked. The Left join was on the Cable_NO column.

THANKS!! i am understanding this much better now
0
 
LVL 1

Author Closing Comment

by:imonfireDAMMIT
ID: 31582101
thank you...i was pulling out my hair all day with this and it turned out that I was soooo close...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to take different types of Oracle backups using RMAN.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question