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
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: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
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
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
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.
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.
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: 7880
Registered: March 2000
Senior Member
Describe your view v_month
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: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use the
tags or the {...} button to post your code and test.
There it is unreadable.

Regards
Michel
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: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry, means <code></code> tags replacing the <> with [] in your post.
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: 7880
Registered: March 2000
Senior Member
@Michel Cadot: You can use the [notag] tags to show bb-code tags.
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
Previous Topic: Need help in optimizing a query
Next Topic: Outer Join Syntax and Performance?
Goto Forum:
  


Current Time: Sun Dec 04 20:33:34 CST 2016

Total time taken to generate the page: 0.05049 seconds