Home » SQL & PL/SQL » SQL & PL/SQL » Ambigious Column Error
Ambigious Column Error [message #258957] Mon, 13 August 2007 20:47 Go to next message
kdastageer
Messages: 28
Registered: January 2006
Location: Qatar
Junior Member
Dear All,

I am having small error in Crystal Reports.We are having two different schemas one is production and other is test schema.
If i run the report in test its running perfectly.But if i run in production schema its giving "Ambigious Column Defined" error.

What it could be the problem but both schema's are updated equally.

Any suggestions please.

by
DAS
Re: Ambigious Column Error [message #258966 is a reply to message #258957] Mon, 13 August 2007 23:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
apparently the two schemas are NOT equal.
Prod has a column in one of the tables that does not exist (anymore?) in test.
Re: Ambigious Column Error [message #258982 is a reply to message #258966] Tue, 14 August 2007 00:42 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Or two tables have a column with the same name, but you forgot to reference this column using a table (alias). An example:
SQL> select ename, deptno
  2  from emp, dept
  3  where emp.deptno = dept.deptno;
select ename, deptno
              *
ERROR at line 1:
ORA-00918: column ambiguously defined


SQL> select e.ename, d.deptno    --> use table alias to correctly reference a column
  2  from emp e, dept d
  3  where e.deptno = d.deptno;

ENAME          DEPTNO
---------- ----------
SMITH              20
ALLEN              30
...
Re: Ambigious Column Error [message #259074 is a reply to message #258982] Tue, 14 August 2007 06:17 Go to previous messageGo to next message
kdastageer
Messages: 28
Registered: January 2006
Location: Qatar
Junior Member
Dear Little,

I am very sorry to say that i am not clear.Can you please explain in detail with example using Full Outer Join as well.

Thank you very much.

by
DAS
Re: Ambigious Column Error [message #259075 is a reply to message #259074] Tue, 14 August 2007 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no relation between the error and the type of join.
This is the same reason: you retrieve/look at 2 columns with same name and have to qualify them.

Regards
Michel

[Updated on: Tue, 14 August 2007 06:19]

Report message to a moderator

Re: Ambigious Column Error [message #259082 is a reply to message #259075] Tue, 14 August 2007 06:27 Go to previous messageGo to next message
kdastageer
Messages: 28
Registered: January 2006
Location: Qatar
Junior Member
Dear Michel,Little

Please find out the sql query below which is running in test but not in production schema.I found the columns i used in this query on both schema is not same.
Please suggest me where i have to correct it,

Query:

SELECT "A_PFCHOLDINGS"."QUOTATION_CURRENCY", "A_PFCHOLDINGS"."POR", "A_TRANSACTIONS"."TRANSACTION_CANCELLATION_FLAG", "A_PFCHOLDINGS"."POR_CALC_SHORT_NAME", "A_SECS"."SEC_GROUP", "A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_1", "A_PFCHOLDINGS"."TO_DATE", "A_PFCKEYRATIOS"."MODIFIED_DURATION_YC", "A_PFCKEYRATIOS"."DIRTY_VALUE_QC", "SECURITIES"."XI_PUTCALLS", "A_PFCHOLDINGS"."POR_GRP_SHORT_NAME", "A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_2", "A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_3", "A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_4", "A_PFCHOLDINGS"."SEC_NAME", "A_PFCHOLDINGS"."POR_CALC_HOLDINGS_REF"
FROM "SCDAT"."SECURITIES" "SECURITIES" INNER JOIN (((("SCDAT"."A_PFCHOLDINGS" "A_PFCHOLDINGS" INNER JOIN "SCDAT"."A_SECS" "A_SECS" ON "A_PFCHOLDINGS"."SEC_REF"="A_SECS"."SEC_REF") INNER JOIN "SCDAT"."A_PFCKEYRATIOS" "A_PFCKEYRATIOS" ON "A_PFCHOLDINGS"."POR_CALC_HOLDINGS_REF"="A_PFCKEYRATIOS"."POR_CALC_HOLDINGS_REF") FULL OUTER JOIN "SCDAT"."A_SECS_BUSINESS_CLASS_TS" "A_SECS_BUSINESS_CLASS_TS" ON "A_SECS"."SEC_REF"="A_SECS_BUSINESS_CLASS_TS"."SEC_REF") FULL OUTER JOIN "SCDAT"."A_TRANSACTIONS" "A_TRANSACTIONS" ON "A_SECS"."SEC_REF"="A_TRANSACTIONS"."SEC_REF") ON "SECURITIES"."SECIK"="A_SECS"."SEC_REF"
WHERE "A_PFCHOLDINGS"."POR_CALC_SHORT_NAME"='FI_PORSUM' AND "A_TRANSACTIONS"."TRANSACTION_CANCELLATION_FLAG"='ACTIVE' AND "A_PFCHOLDINGS"."POR"='C-EUR' AND ("A_PFCHOLDINGS"."TO_DATE">=TO_DATE ('21-05-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "A_PFCHOLDINGS"."TO_DATE"<TO_DATE ('22-05-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

by
DAS
Re: Ambigious Column Error [message #259083 is a reply to message #259082] Tue, 14 August 2007 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format the query: How to format your posts

Regards
Michel
Re: Ambigious Column Error [message #259089 is a reply to message #259083] Tue, 14 August 2007 06:34 Go to previous messageGo to next message
kdastageer
Messages: 28
Registered: January 2006
Location: Qatar
Junior Member
Dear Mic,

Sorry...

below formatted







SELECT
"A_PFCHOLDINGS"."QUOTATION_CURRENCY",
"A_PFCHOLDINGS"."POR",
"A_TRANSACTIONS"."TRANSACTION_CANCELLATION_FLAG",
"A_PFCHOLDINGS"."POR_CALC_SHORT_NAME",
"A_SECS"."SEC_GROUP",
"A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_1",
"A_PFCHOLDINGS"."TO_DATE",
"A_PFCKEYRATIOS"."MODIFIED_DURATION_YC",
"A_PFCKEYRATIOS"."DIRTY_VALUE_QC",
"SECURITIES"."XI_PUTCALLS",
"A_PFCHOLDINGS"."POR_GRP_SHORT_NAME",
"A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_2",
"A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_3",
"A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_4",
"A_PFCHOLDINGS"."SEC_NAME",
"A_PFCHOLDINGS"."POR_CALC_HOLDINGS_REF"
FROM
"SCDAT"."SECURITIES" "SECURITIES" INNER JOIN (((("SCDAT"."A_PFCHOLDINGS" "A_PFCHOLDINGS" INNER JOIN "SCDAT"."A_SECS" "A_SECS" ON "A_PFCHOLDINGS"."SEC_REF"="A_SECS"."SEC_REF") INNER JOIN "SCDAT"."A_PFCKEYRATIOS" "A_PFCKEYRATIOS" ON "A_PFCHOLDINGS"."POR_CALC_HOLDINGS_REF"="A_PFCKEYRATIOS"."POR_CALC_HOLDINGS_REF") FULL OUTER JOIN "SCDAT"."A_SECS_BUSINESS_CLASS_TS" "A_SECS_BUSINESS_CLASS_TS" ON "A_SECS"."SEC_REF"="A_SECS_BUSINESS_CLASS_TS"."SEC_REF") FULL OUTER JOIN "SCDAT"."A_TRANSACTIONS" "A_TRANSACTIONS" ON "A_SECS"."SEC_REF"="A_TRANSACTIONS"."SEC_REF") ON "SECURITIES"."SECIK"="A_SECS"."SEC_REF"
WHERE
"A_PFCHOLDINGS"."POR_CALC_SHORT_NAME"='FI_PORSUM' AND
"A_TRANSACTIONS"."TRANSACTION_CANCELLATION_FLAG"='ACTIVE' AND
"A_PFCHOLDINGS"."POR"='C-EUR' AND
("A_PFCHOLDINGS"."TO_DATE">=TO_DATE ('21-05-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"A_PFCHOLDINGS"."TO_DATE"<TO_DATE ('22-05-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
Re: Ambigious Column Error [message #259091 is a reply to message #259089] Tue, 14 August 2007 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the link and repost.

Regards
Michel
Re: Ambigious Column Error [message #259095 is a reply to message #259091] Tue, 14 August 2007 06:49 Go to previous messageGo to next message
kdastageer
Messages: 28
Registered: January 2006
Location: Qatar
Junior Member
SELECT
"A_PFCHOLDINGS"."QUOTATION_CURRENCY",
"A_PFCHOLDINGS"."POR",
"A_TRANSACTIONS"."TRANSACTION_CANCELLATION_FLAG",
"A_PFCHOLDINGS"."POR_CALC_SHORT_NAME",
"A_SECS"."SEC_GROUP",
"A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_1",
"A_PFCHOLDINGS"."TO_DATE",
"A_PFCKEYRATIOS"."MODIFIED_DURATION_YC",
"A_PFCKEYRATIOS"."DIRTY_VALUE_QC",
"SECURITIES"."XI_PUTCALLS",
"A_PFCHOLDINGS"."POR_GRP_SHORT_NAME",
"A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_2",
"A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_3",
"A_SECS_BUSINESS_CLASS_TS"."BUSINESS_CLASS_LEVEL_4",
"A_PFCHOLDINGS"."SEC_NAME",
"A_PFCHOLDINGS"."POR_CALC_HOLDINGS_REF"
FROM
"SCDAT"."SECURITIES" "SECURITIES"
INNER JOIN (((("SCDAT"."A_PFCHOLDINGS" "A_PFCHOLDINGS"
INNER JOIN "SCDAT"."A_SECS" "A_SECS" ON "A_PFCHOLDINGS"."SEC_REF"="A_SECS"."SEC_REF")
INNER JOIN "SCDAT"."A_PFCKEYRATIOS" "A_PFCKEYRATIOS" ON "A_PFCHOLDINGS"."POR_CALC_HOLDINGS_REF"="A_PFCKEYRATIOS"."POR_CALC_HOLDINGS_REF")
FULL OUTER JOIN "SCDAT"."A_SECS_BUSINESS_CLASS_TS" "A_SECS_BUSINESS_CLASS_TS" ON "A_SECS"."SEC_REF"="A_SECS_BUSINESS_CLASS_TS"."SEC_REF")
FULL OUTER JOIN "SCDAT"."A_TRANSACTIONS" "A_TRANSACTIONS" ON "A_SECS"."SEC_REF"="A_TRANSACTIONS"."SEC_REF") ON "SECURITIES"."SECIK"="A_SECS"."SEC_REF"
WHERE
"A_PFCHOLDINGS"."POR_CALC_SHORT_NAME"='FI_PORSUM' AND
"A_TRANSACTIONS"."TRANSACTION_CANCELLATION_FLAG"='ACTIVE' AND
"A_PFCHOLDINGS"."POR"='C-EUR' AND
("A_PFCHOLDINGS"."TO_DATE">=TO_DATE ('21-05-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"A_PFCHOLDINGS"."TO_DATE"<TO_DATE ('22-05-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

Now this is readable i suspect pls accept.
Re: Ambigious Column Error [message #259100 is a reply to message #259091] Tue, 14 August 2007 06:55 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
To add to michael's point, by any chance did you read littlefoot's reply. Oracle will clearly point you which column is ambiguously defined. Also by the look of it, if i am not mistaken it is a generated query by some tool. Did you try to execute this query in your test and production environment. If so what are the output and errors ? Without this I don't think anybody will be able to help you (apart from making wild guesses) because
a) you have not given us the table definition.
b) No error message is been posted in your reply.

Sorry to tell you, This statement means nothing to me. Which columns ?
Quote:
I found the columns i used in this query on both schema is not same.

Next time when you are post something in any forum read the post again and ask yourself is it having enough information for anybody to understand it and then please post it. Turnaround time will be way quicker.

Something like this.
Quote:
Please Read the first two links. It will clearly explain you how to format your posts.

Regards

Raj

[Updated on: Tue, 14 August 2007 06:58]

Report message to a moderator

Re: Ambigious Column Error [message #259124 is a reply to message #259095] Tue, 14 August 2007 08:28 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
kdastageer wrote on Tue, 14 August 2007 07:49

[unformatted...]
SELECT
"A_PFCHOLDINGS"."QUOTATION_CURRENCY",
"A_PFCHOLDINGS"."POR",
"A_TRANSACTIONS"."TRANSACTION_CANCELLATION_FLAG",
"A_PFCHOLDINGS"."POR_CALC_SHORT_NAME",
"A_SECS"."SEC_GROUP",
etc.



Try again, reading this link first http://www.orafaq.com/forum/t/85036/66800/
Re: Ambigious Column Error [message #259310 is a reply to message #259124] Wed, 15 August 2007 00:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry to repeat my point, but if you get the error on one schema and not on the other, the it can only be caused by the fact that the two do not exactly match.
Littlefoot only explains the cause of the error itself, not why it could occur in only one of the two schemas.

[Updated on: Wed, 15 August 2007 00:16]

Report message to a moderator

Re: Ambigious Column Error [message #259359 is a reply to message #259095] Wed, 15 August 2007 03:58 Go to previous messageGo to next message
kdastageer
Messages: 28
Registered: January 2006
Location: Qatar
Junior Member
Waiting for the solution.
Anyone pls...

by
DAS
Re: Ambigious Column Error [message #259360 is a reply to message #259359] Wed, 15 August 2007 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you do YOURSELF?
Did you check Frank's point?
You didn't answer to Raj's question.
You didn't post an execution like LittleFoot did that shows on which column there is a problem.
You still didn't post a formatted query.

Why do you want all these people continue to work on your problem?


Regards
Michel
Re: Ambigious Column Error [message #259394 is a reply to message #258957] Wed, 15 August 2007 08:49 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Run the code in SqlPlus and it will tell you exactly where the error is.
Re: Ambigious Column Error [message #261633 is a reply to message #259394] Thu, 23 August 2007 03:14 Go to previous messageGo to next message
kdastageer
Messages: 28
Registered: January 2006
Location: Qatar
Junior Member
Hi,

I got the solution by own.Thanks all.

by
DAS
Re: Ambigious Column Error [message #261637 is a reply to message #261633] Thu, 23 August 2007 03:18 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the error was? And the solution is?

Regards
Michel
Previous Topic: How to decrypt data in Oracle (Merged)
Next Topic: how to get the individual execution time of operation in an execution plan
Goto Forum:
  


Current Time: Thu Dec 08 06:37:02 CST 2016

Total time taken to generate the page: 0.10168 seconds