Solved

SQL Query running in SQL developer but not with Java file

Posted on 2014-09-22
16
642 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 31

Expert Comment

by:awking00
ID: 40337470
Can you post the query?
0
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now