SQL Query running in SQL developer but not with Java file

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.
jverasqlAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PortletPaulConnect With a Mentor Commented:
The Java that is parsing the query may have a bug? ... it is pure conjecture ... i.e. a guess
0
 
sdstuberCommented:
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
 
DavidSenior Oracle Database AdministratorCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
awking00Commented:
Can you post the query?
0
 
jverasqlAuthor Commented:
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
 
sdstuberCommented:
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
 
jverasqlAuthor Commented:
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
 
sdstuberCommented:
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
 
jverasqlAuthor Commented:
eprtotal is a view, which runs fine??
0
 
sdstuberCommented:
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
 
jverasqlAuthor Commented:
thanks I will verify the data returned from the view solely is correct.
0
 
sdstuberCommented:
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
 
PortletPaulCommented:
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
 
sdstuberCommented:
how could that be a problem in java?

the table aliases shouldn't be part of the result sets returned.
0
 
sdstuberConnect With a Mentor Commented:
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
 
jverasqlAuthor Commented:
Thanks for all the input.  Really appreciate it.
0
All Courses

From novice to tech pro — start learning today.