CODEWITHSUNDEEP
sqldatabaseoracle-databasejoin
Bitterblue
Bitterblue

Reputation: 14085

Why do SQL joins fail in Oracle?

I just try simple joins with C# using oracle db. Should be no big deal. But it ALWAYS fails. It works in MS-Access. Where is the problem ? (OleDb or Odbc makes no difference here, I tried both)

Edit:

Code:

using System;
using System.Data.Odbc;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string n = Environment.NewLine + "--------------------------------" + Environment.NewLine + Environment.NewLine;

            // connect
            string connectionString = "dsn=TEST;uid=read;pwd=myPwd";
            OdbcConnection connection = new OdbcConnection(connectionString);
            connection.Open();

            // select (key is actually text not numeral)
            string query = "select * from INFOR.ZEITEN where (KEY = 0)";
            query = "select a.KEY, b.GREG from INFOR.ZEITEN a inner join INFOR.ZEITEN b on (a.AUSWEIS = b.AUSWEIS) where (a.KEY like '1')";

            try
            {
                query = query.Replace(Environment.NewLine, " ");
                Console.WriteLine(n + query);
                OdbcCommand command = new OdbcCommand(query, connection);
                OdbcDataReader reader = command.ExecuteReader(); // throws exception
                if (reader != null)
                    Console.WriteLine(n + "success, now read with reader!");
            }
            catch (Exception e)
            {
                Console.WriteLine(n + e.Message + n + e.StackTrace);
            }

            // wait
            Console.ReadKey();
        }
    }
}

Output:

enter image description here

And the successful, simple select:

enter image description here

Upvotes: 2

Views: 608

Answers (4)

DragoRaptor
DragoRaptor

Reputation: 735

What's the error? Could you edit your question and add the actual error the system's throwing at you? Firstly, I would personally recommend using the ODP .NET (Oracle Data provider for .NET). You can download the latest version for Oracle 12c here. Or look it up for the version you need. ODBC is a very old driver written in C and works using the native Windows RPC technique. For full .NET support you're better off with ODP .NET. Secondly, check if you have any constraints on the tables that's causing the sql to fail.

Upvotes: 0

Joe
Joe

Reputation: 6827

ANSI joins (ex. inner join) were first supported in 9i. You will need to use the old syntax:

select a.KEY, b.GREG 
 from INFOR.ZEITEN a,
      INFOR.ZEITEN b 
where (a.AUSWEIS = b.AUSWEIS) 
      and  (a.KEY like '1')

Note that the like operator is equivalent to = in this case, but you probably know that

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270633

The word key is a reserved word. That means that it is a very poor choice for an identity. You need to escape it with a double quote. This might work:

        query = "select a.\"KEY\", b.GREG
                 from INFOR.ZEITEN a inner join
                      INFOR.ZEITEN b
                      on (a.AUSWEIS = b.AUSWEIS)
                 where (a.\"KEY\" like '1')";

I am guessing the \" will work in this context, but there might be another method to insert this character.

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

I think the KEY is numeric then you can't use LIKE. It is because the WHERE KEY = 0 works fine.

Upvotes: 1

Related Questions