OraFAQ Forum: Reports & Discoverer » query fine in sqlplus but gives Column ambiguously defined in reports

Home » Developer & Programmer » Reports & Discoverer » query fine in sqlplus but gives Column ambiguously defined in reports

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query fine in sqlplus but gives Column ambiguously defined in reports [message #181433]

Mon, 10 July 2006 02:25

laksha
Messages: 42
Registered: June 2006

Member

Hi,
I can run the following query in sqlplus.
But when i paste the same query in oracle report Builder 6 I get the error
ORA-00918: column ambiguously defined.
What could be the problem ?

select main.asset_number,main.description,fin.date_placed_in_service,fin.book_type_code,fin.cost ,
fin.old_adjusted_cost,fin.recoverable_cost,main.attribute_category_code,main.category_type
from
(SELECT asset_id,asset_number,Description,attribute_category_code,category_type
FROM fa_additions_v
) main,
(SELECT bc.book_class, b.transaction_header_id_in, b.transaction_header_id_out,
b.book_type_code, b.asset_id, b.date_ineffective,b.adjusted_cost, b.COST,
b.original_cost, b.ceiling_name, b.salvage_value, b.reval_ceiling,b.basic_rate,b.adjusted_rate,
b.recoverable_cost,b.unrevalued_cost,b.adjusted_recoverable_cost,b.old_adjusted_cost,b.cip_cost,
b.itc_basis, b.itc_amount_id, b.date_placed_in_service,
b.depreciate_flag, b.prorate_convention_code, b.prorate_date,
b.deprn_method_code, b.life_in_months, b.basic_rate, b.adjusted_rate,
b.bonus_rule, b.unit_of_measure, b.production_capacity,
b.disabled_flag
FROM fa_books b, fa_book_controls bc
WHERE b.book_type_code = bc.book_type_code ) fin,
(select a.UNITS_ASSIGNED,a.ASSET_ID,a.BOOK_TYPE_CODE,a.DISTRIBUTION_ID,a.ASSIGNED_TO,a.TRANSACTION_UNITS,
a.CODE_COMBINATION_ID,a.LOCATION_ID,a.DATE_EFFECTIVE,a.DATE_INEFFECTIVE,a.TRANSACTION_HEADER_ID_IN,
a.TRANSACTION_HEADER_ID_OUT,a.RETIREMENT_ID,b.segment1,b.segment2
from fa_distribution_history a,fa_locations b
where a.location_id=b.location_id ) loc
where
main.asset_id=fin.asset_id
and main.asset_id=loc.asset_id

Report message to a moderator

Re: query fine in sqlplus but gives Column ambiguously defined in reports [message #181485 is a reply to message #181433]

Mon, 10 July 2006 05:36

aciolac
Messages: 242
Registered: February 2006

Senior Member

Error "ORA-00918: column ambiguously defined" means that in your select there are column names which are in more than one table, indicated in FROM clause. You must precise column name by synonyms. For example:

SQL> select a1.col_name, a2.col_name
from your_table a1,your_table a2

Report message to a moderator

Re: query fine in sqlplus but gives Column ambiguously defined in reports [message #181493 is a reply to message #181485]

Mon, 10 July 2006 05:52

Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Yes, this is the explanation. But, Laksha's query is properly written and all columns have table aliases.

Could it be that Reports 6i don't support such a syntax? Perhaps inline views can not be used there? You could try to rewrite this query; first create "ordinary" views (MAIN, FIN, LOC) and use them as such in the FROM clause. Perhaps it'll help.

Report message to a moderator

Re: query fine in sqlplus but gives Column ambiguously defined in reports [message #229208 is a reply to message #181433]

Fri, 06 April 2007 00:53

shakti_goyal
Messages: 60
Registered: November 2006
Location: India

Member

Hello dear,
I have solved your problem .. This is your query I got in your question ..
You don't need to do anything just paste this query in Query
Dialogue in Report Builder .. It will never give u error..
I have already checked it in Report Builder also

Cheer

Regards
Shakti Goyal

select
main.g2, --main.asset_number,
main.g3, --main.description,
fin.f21, --fin.date_placed_in_service,
fin.f4, --fin.book_type_code,
fin.f8, --fin.cost ,
fin.f18, --fin.old_adjusted_cost,
fin.f15, --fin.recoverable_cost,
main.g4, --main.attribute_category_code,
main.g5 --main.category_type
from
(
select
z.asset_id g1,
z.asset_number g2,
z.Description g3,
z.attribute_category_code g4,
z.category_type g5
from
fa_additions_v z
) main,
(
select
bc.book_class f1,
b.transaction_header_id_in f2,
b.transaction_header_id_out f3,
b.book_type_code f4,
b.asset_id f5,
b.date_ineffective f6,
b.adjusted_cost f7,
b.COST f8,
b.original_cost f9,
b.ceiling_name f10,
b.salvage_value f11,
b.reval_ceiling f12,
b.basic_rate f13,
b.adjusted_rate f14,
b.recoverable_cost f15,
b.unrevalued_cost f16,
b.adjusted_recoverable_cost f17,
b.old_adjusted_cost f18,
--b.cost,
b.itc_basis f19,
b.itc_amount_id f20,
b.date_placed_in_service f21,
b.depreciate_flag f22,
b.prorate_convention_code f23,
b.prorate_date f24,
b.deprn_method_code f25,
b.life_in_months f26,
b.basic_rate f27,
b.adjusted_rate f28,
b.bonus_rule f29,
b.unit_of_measure f30,
b.production_capacity f31
--b.disabled_flag
from
fa_books b,
fa_book_controls bc
where
b.book_type_code = bc.book_type_code) fin,
--b.book_type_code = bc.book_type_code ) fin,
(select
a.UNITS_ASSIGNED q1,
a.ASSET_ID q2,
a.BOOK_TYPE_CODE q3,
a.DISTRIBUTION_ID q4,
a.ASSIGNED_TO q5,
a.TRANSACTION_UNITS q7,
a.CODE_COMBINATION_ID q8,
a.LOCATION_ID q9,
a.DATE_EFFECTIVE q10,
a.DATE_INEFFECTIVE q11,
a.TRANSACTION_HEADER_ID_IN q12,
a.TRANSACTION_HEADER_ID_OUT q13,
a.RETIREMENT_ID q14,
b.segment1 q15,
b.segment2 q16
from
fa_distribution_history a,
fa_locations b
where
a.location_id = b.location_id ) loc
--a.location_id=b.location_id ) loc
where
main.g1 = fin.f5
and main.g1 = loc.q2

--main.asset_id=fin.asset_id
--and main.asset_id=loc.asset_id

[Updated on: Fri, 06 April 2007 00:59]

Report message to a moderator

Re: query fine in sqlplus but gives Column ambiguously defined in reports [message #229278 is a reply to message #229208]

Fri, 06 April 2007 07:16

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Shakti,

Although the OP is probably grateful to you, this forum is to learn from eachother, not some fancy system where you can type in specs and get code back!

Now OP will never find out why his initial statement didn't work, so next time the same problem will occur.

Regards,
Sabine

Report message to a moderator

Re: query fine in sqlplus but gives Column ambiguously defined in reports [message #229285 is a reply to message #229278]

Fri, 06 April 2007 08:31

shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai

Senior Member

hi,

And i also seriously doubt that the OP would have been waiting this long for the solution,because it was on this date the question was asked. Mon, 10 July 2006 02:25 Razz