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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 #229208 is a reply to message #181433] |
Fri, 06 April 2007 00:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
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
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Sep 11 02:50:59 CDT 2024
|