Solved

SQL Query running in SQL developer but not with Java file

Posted on 2014-09-22
16
675 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +2
16 Comments
 
LVL 74

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
Technology Partners: 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!

 

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 74

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 74

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 74

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 74

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 74

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 74

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error in creating a view. 8 33
SQL*Plus debugging?  Is there a way to step through the SQL Script? 3 41
Database maintenance 36 101
MySQL-Design Help 12 41
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

756 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