Solved

SQL Query running in SQL developer but not with Java file

Posted on 2014-09-22
16
661 Views
Last Modified: 2014-09-25
Hello,

I am able to run a query successfully with Oracle SQL developer, but when I enter the exact query into a text file which is read by a JAVA file it returns ORA-00918: column ambiguously.  Any thoughts?  Please let me know if any additional information is needed.  There is a join in the query.  It worked before I added the join and I have given the tables aliases.

Thanks.
0
Comment
Question by:jverasql
  • 7
  • 5
  • 2
  • +2
16 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 40337385
sql developer is automatically handling duplicate column names for you.

for example

select * from tab1, tab2
where tab1.id = tab2.id

both tables have a column called ID, so the results will have 2 columns called ID, but that's not really appropriate.
Alias the column names, prefix with objects or only select one of them.
0
 
LVL 23

Expert Comment

by:David
ID: 40337387
Your error suggests you are missing a synonym, public or private. A typical situation is that you are now executing from a different user account. What else may have changed with the Java effort?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40337470
Can you post the query?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:jverasql
ID: 40337486
Below is the revised query: I added an alias to epr.panum.  No I get an ORA:00933 error.

Select
atb.panum,
atb.currentcc,
epr.epr
FROM
dss.eomatb atb LEFT JOIN fin.eprtotal epr ON atb.panum = epr.panum Patient
where atb.DOS >= to_date('2014-02-01','YYYY-MM-DD')
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40337505
the patient alias isn't needed, in fact, that's the ora-933 error.

you would use aliases if you were selecting multiple columns of the same name from multiple objects.

when you prefix them with the object names you remove the abiguity
0
 

Author Comment

by:jverasql
ID: 40337525
It now states:

Select
atb.panum,
atb.currentcc,
epr.epr
FROM
dss.eomatb atb LEFT JOIN fin.eprtotal epr ON atb.panum = epr.panum  
where atb.DOS >= to_date('2014-02-01','YYYY-MM-DD')

It now gives me the ORA:00918 again.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40337545
is  eomatb or eprtotal a view?

there's nothing ambiguous in what you have posted, but if your objects are views then there might be errors within them.
0
 

Author Comment

by:jverasql
ID: 40337584
eprtotal is a view, which runs fine??
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40337611
if your view has no errors then you've encountered a bug because the query syntax you posted is valid.

you could try using oracle outer join syntax as a workaround (no guarantee that'll work though)
but better is to contact oracle support with your view syntax and your query.

or, double check your view and your other object - make sure everything you think is ok, actually is.

is the query posted above exactly what you are running that generates the error?
0
 

Author Comment

by:jverasql
ID: 40337626
thanks I will verify the data returned from the view solely is correct.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40337631
Obviously I don't have your objects but just to make sure I haven't overlooked something,
I just created 2 schemas and objects that match your description.


SQL> CREATE USER dss IDENTIFIED BY pa55w0rd;

User created.

SQL> CREATE USER fin IDENTIFIED BY pa55w0rd;

User created.

SQL> CREATE VIEW fin.eprtotal
  2  AS
  3      (SELECT 1 panum, 1 epr FROM DUAL);

View created.

SQL> CREATE TABLE dss.eomatb
  2  (
  3      panum     NUMBER,
  4      currentcc NUMBER,
  5      dos       DATE
  6  );

Table created.

SQL> SELECT atb.panum, atb.currentcc, epr.epr
  2    FROM dss.eomatb atb LEFT JOIN fin.eprtotal epr ON atb.panum = epr.panum
  3   WHERE atb.dos >= TO_DATE('2014-02-01', 'YYYY-MM-DD');

no rows selected

SQL>

Open in new window



I have no data so it doesn't return anything, but that's fine.  The point of the exercise was to confirm the validity of the syntax for the objects as presented here.

If I've created something in a way that is significantly different than what you have, please elaborate.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40338030
I think the "epr.epr" might be an issue in the java (it won't be in SQL)

I'm going to suggest you try a different alias from "epr" to "ept" this way you do not get "epr.epr" which is the only possible cause of ambiguity I can see.

SELECT
      atb.panum
    , atb.currentcc
    , ept.epr
FROM dss.eomatb atb
      LEFT JOIN fin.eprtotal ept
                  ON atb.panum = ept.panum
WHERE atb.DOS >= to_date('2014-02-01', 'YYYY-MM-DD')

also:
you state "It now gives me the ORA:00918 again." do you mean SQLplus or Java?
please don't say "it"
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40338894
how could that be a problem in java?

the table aliases shouldn't be part of the result sets returned.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 40338916
The Java that is parsing the query may have a bug? ... it is pure conjecture ... i.e. a guess
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 40338958
ok, I was wondering if you knew of something specific to jdbc that might introduce an error.

If java was parsing the sql I would expect a java error,
Since the error is an Oracle error it looks to me like Oracle is doing the parsing.
0
 

Author Comment

by:jverasql
ID: 40344930
Thanks for all the input.  Really appreciate it.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

770 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