Home » SQL & PL/SQL » SQL & PL/SQL » Need the solution for the following SQL giving 00918 error(Merged)
Need the solution for the following SQL giving 00918 error(Merged) [message #225230] Mon, 19 March 2007 04:27 Go to next message
udaykiran3003
Messages: 5
Registered: March 2007
Junior Member
SELECT
NVL(A.CUSTOMER_SK, B.BCUSTOMER_SK) ACUSTOMER_SK,
NVL(A.INVOICE_DT_SK,B.BPOS_DT_SK) ADATE_SK,
NVL(A.PRODUCT_SK,B.BPRODUCT_SK) APRODUCT_SK,
B.BDOOR_SK ADOOR_SK,
NVL(B.BAMOUNT,0) AAMOUNT,
NVL(B.BQUANTITY,0) AQUANTITY,
NVL(B.BSUG_RET_PRICE,0) ASUG_RET_PRICE
FROM
DATE_TIME D,INVOICE_FACT A
FULL OUTER JOIN
(SELECT
POS.CUSTOMER_SK BCUSTOMER_SK,
POS.DOOR_SK BDOOR_SK,
P.PRODUCT_SK BPRODUCT_SK,
POS.SALES_DT_SK BPOS_DT_SK,
VM.MONTH_NUMBER_IN_MONTH BMNM,
SUM(POS.AMOUNT) BAMOUNT,
SUM(POS.QUANTITY) BQUANTITY,
MAX(P.SUG_RET_PRICE) BSUG_RET_PRICE
FROM
V_MONTH VM,DATE_TIME DT, PRODUCT_MASTER P, POS_FACT_CN POS
WHERE
P.PRODUCT_SK=POS.PRODUCT_SK AND
DT.CALENDAR_SK=POS.SALES_DT_SK AND
DT.CALENDAR_YEAR=VM.CALENDAR_YEAR
GROUP BY POS.CUSTOMER_SK, POS.DOOR_SK , POS.PRODUCT_SK ,POS.SALES_DT_SK,VM.MONTH_NUMBER_IN_MONTH) B ON
A.CUSTOMER_SK = B.BCUSTOMER_SK AND A.PRODUCT_SK = B.BPRODUCT_SK
WHERE
D.MONTH_NUMBER_IN_MONTH=B.BMNM



Is my query and i am getting 00918 error plz help me

Report message to a moderator

Re: Need the solution for the following SQL giving 00918 error [message #225231 is a reply to message #225230] Mon, 19 March 2007 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

1/ This is not an expert question
2/ SQL*Plus points you to line and column where the column is ambiguous, use them.

Regards
Michel

Report message to a moderator

Re: Need the solution for the following SQL giving 00918 error [message #225233 is a reply to message #225231] Mon, 19 March 2007 04:32 Go to previous messageGo to next message
udaykiran3003
Messages: 5
Registered: March 2007
Junior Member


SQL> SELECT
2 NVL(A.CUSTOMER_SK, B.BCUSTOMER_SK) ACUSTOMER_SK,
3 NVL(A.INVOICE_DT_SK,B.BPOS_DT_SK) ADATE_SK,
4 NVL(A.PRODUCT_SK,B.BPRODUCT_SK) APRODUCT_SK,
5 B.BDOOR_SK ADOOR_SK,
6 NVL(B.BAMOUNT,0) AAMOUNT,
7 NVL(B.BQUANTITY,0) AQUANTITY,
8 NVL(B.BSUG_RET_PRICE,0) ASUG_RET_PRICE
9 FROM
10 DATE_TIME D,INVOICE_FACT A
11 FULL OUTER JOIN
12 (SELECT
13 POS.CUSTOMER_SK BCUSTOMER_SK,
14 POS.DOOR_SK BDOOR_SK,
15 P.PRODUCT_SK BPRODUCT_SK,
16 POS.SALES_DT_SK BPOS_DT_SK,
17 VM.MONTH_NUMBER_IN_MONTH BMNM,
18 SUM(POS.AMOUNT) BAMOUNT,
19 SUM(POS.QUANTITY) BQUANTITY,
20 MAX(P.SUG_RET_PRICE) BSUG_RET_PRICE
21 FROM
22 V_MONTH VM,DATE_TIME DT, PRODUCT_MASTER P, POS_FACT_CN POS
23 WHERE
24 P.PRODUCT_SK=POS.PRODUCT_SK AND
25 DT.CALENDAR_SK=POS.SALES_DT_SK AND
26 DT.CALENDAR_YEAR=VM.CALENDAR_YEAR
27 GROUP BY POS.CUSTOMER_SK, POS.DOOR_SK , POS.PRODUCT_SK ,POS.SALES_DT_SK,VM.MONTH_NUMBER_IN_MON
TH) B ON
28 A.CUSTOMER_SK = B.BCUSTOMER_SK AND A.PRODUCT_SK = B.BPRODUCT_SK
29 WHERE
30 D.MONTH_NUMBER_IN_MONTH=B.BMNM
31 /
DT.CALENDAR_YEAR=VM.CALENDAR_YEAR
*
ERROR at line 26:
ORA-00918: column ambiguously defined

Report message to a moderator

Need solution for the following problem [message #225234 is a reply to message #225230] Mon, 19 March 2007 04:33 Go to previous messageGo to next message
udaykiran3003
Messages: 5
Registered: March 2007
Junior Member


SQL> SELECT
2 NVL(A.CUSTOMER_SK, B.BCUSTOMER_SK) ACUSTOMER_SK,
3 NVL(A.INVOICE_DT_SK,B.BPOS_DT_SK) ADATE_SK,
4 NVL(A.PRODUCT_SK,B.BPRODUCT_SK) APRODUCT_SK,
5 B.BDOOR_SK ADOOR_SK,
6 NVL(B.BAMOUNT,0) AAMOUNT,
7 NVL(B.BQUANTITY,0) AQUANTITY,
8 NVL(B.BSUG_RET_PRICE,0) ASUG_RET_PRICE
9 FROM
10 DATE_TIME D,INVOICE_FACT A
11 FULL OUTER JOIN
12 (SELECT
13 POS.CUSTOMER_SK BCUSTOMER_SK,
14 POS.DOOR_SK BDOOR_SK,
15 P.PRODUCT_SK BPRODUCT_SK,
16 POS.SALES_DT_SK BPOS_DT_SK,
17 VM.MONTH_NUMBER_IN_MONTH BMNM,
18 SUM(POS.AMOUNT) BAMOUNT,
19 SUM(POS.QUANTITY) BQUANTITY,
20 MAX(P.SUG_RET_PRICE) BSUG_RET_PRICE
21 FROM
22 V_MONTH VM,DATE_TIME DT, PRODUCT_MASTER P, POS_FACT_CN POS
23 WHERE
24 P.PRODUCT_SK=POS.PRODUCT_SK AND
25 DT.CALENDAR_SK=POS.SALES_DT_SK AND
26 DT.CALENDAR_YEAR=VM.CALENDAR_YEAR
27 GROUP BY POS.CUSTOMER_SK, POS.DOOR_SK , POS.PRODUCT_SK ,POS.SALES_DT_SK,VM.MONTH_NUMBER_IN_MON
TH) B ON
28 A.CUSTOMER_SK = B.BCUSTOMER_SK AND A.PRODUCT_SK = B.BPRODUCT_SK
29 WHERE
30 D.MONTH_NUMBER_IN_MONTH=B.BMNM
31 /
DT.CALENDAR_YEAR=VM.CALENDAR_YEAR
*
ERROR at line 26:
ORA-00918: column ambiguously defined

Report message to a moderator

Re: Need solution for the following problem [message #225239 is a reply to message #225234] Mon, 19 March 2007 04:40 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member
Check your code

ORA-00918:column ambiguously defined

Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

Report message to a moderator

Re: Need solution for the following problem [message #225240 is a reply to message #225239] Mon, 19 March 2007 04:43 Go to previous messageGo to next message
udaykiran3003
Messages: 5
Registered: March 2007
Junior Member
That is wat i have found in all the websites. Can u find out which is causing that error in my code.

Report message to a moderator

Re: Need solution for the following problem [message #225242 is a reply to message #225240] Mon, 19 March 2007 05:10 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Describe your view v_month

Report message to a moderator

Re: Need the solution for the following SQL giving 00918 error [message #225243 is a reply to message #225233] Mon, 19 March 2007 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Use the 
 tags or the {...} button to post your code and test.

There it is unreadable.



Regards

Michel


		
		
		
Report message to a moderator





	
		
	






	
		

		

			Re: Need the solution for the following SQL giving 00918 error [message #225244 is a reply to message #225243]
			Mon, 19 March 2007 05:13 Go to previous messageGo to next message
		

		

			
				

				

				
					
						
						Michel Cadot
						
Messages: 68718
Registered: March 2007 
Location: Saint-Maur, France, https...
						
							
					
Senior Member
Account Moderator

					
		

		

			




	
		

Sorry, means <code></code> tags replacing the <> with [] in your post.


		
		
		
Report message to a moderator





	
		
	






	
		

		

			Re: Need the solution for the following SQL giving 00918 error [message #225245 is a reply to message #225244]
			Mon, 19 March 2007 05:14 Go to previous messageGo to next message
		

		

			
				

				

				
					
						
						Frank
						
Messages: 7901
Registered: March 2000 
						
							
					
Senior Member

					
		

		

			




	
		@Michel Cadot: You can use the [notag] tags to show bb-code tags.
		
		
		
Report message to a moderator





	
		
	






	
		

		

			Re: Need the solution for the following SQL giving 00918 error(Merged) [message #225278 is a reply to message #225230]
			Mon, 19 March 2007 08:49 Go to previous message
		

		

			
				

				

				
					
						
						seraphstream
						
Messages: 8
Registered: March 2007 
						
							
					
Junior Member

					
		

		

			




	
		A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN.



see:





ORACLE ORA-00918 ERRORS Column ambiguously defined





bye



 Razz 
		
		
		
Report message to a moderator





	
		
	












	

	Previous Topic:
	Need help in optimizing a query


	

	Next Topic:
	Outer Join Syntax and Performance?





Goto Forum:


	
	
	  
	





[ Syndicate this forum (XML) ] [ RSS ] 


Current Time: Thu Dec 05 00:10:29 CST 2024









	.::
	Forum Home ::
	Blogger Home ::
	Wiki Home ::
	Contact ::
	Privacy
	::.