Home » Developer & Programmer » Reports & Discoverer » query fine in sqlplus but gives Column ambiguously defined in reports
query fine in sqlplus but gives Column ambiguously defined in reports [message #181433] Mon, 10 July 2006 02:25 Go to next message
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20894
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.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
skooman
Messages: 912
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
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 Go to previous messageGo to next message
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:25Razz

regards,
shanth.

[Updated on: Fri, 06 April 2007 08:33]

Report message to a moderator

Re: query fine in sqlplus but gives Column ambiguously defined in reports [message #229465 is a reply to message #229285] Sun, 08 April 2007 10:39 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Didn't even notice that... Laughing
Re: query fine in sqlplus but gives Column ambiguously defined in reports [message #229474 is a reply to message #229465] Sun, 08 April 2007 14:15 Go to previous message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

it happens. Laughing


regards,
shanth.



Previous Topic: rep-1848 failed while trying to print.
Next Topic: Maximize the Report & user Parameter
Goto Forum:
  


Current Time: Mon Dec 05 12:36:05 CST 2016

Total time taken to generate the page: 0.15780 seconds