Home » Developer & Programmer » Forms » ORA-00918: column ambiguously defined
icon4.gif  ORA-00918: column ambiguously defined [message #231471] Tue, 17 April 2007 03:55 Go to next message
suhaiza
Messages: 3
Registered: April 2007
Junior Member
strSQL = "SELECT * FROM ("
strSQL = strSQL & "SELECT a." & ApprovingLandingData.APPROVING_LANDING_PAGE_ID & ","
strSQL = strSQL & " a." & ApprovingLandingData.APPROVING_LANDING_PAGE_TITLE & ","
strSQL = strSQL & " a." & ApprovingLandingData.APPROVING_LANDING_DATA_STATUS & ","
strSQL = strSQL & " a." & ApprovingLandingData.APPROVING_LANDING_SCOPE_TYPE_ID & ","
strSQL = strSQL & " a." & ApprovingLandingData.APPROVING_LANDING_UPDATE_BY & ","
strSQL = strSQL & " b." & SysLookupData.SYSLOOKUP_DESC & " AS " & sortType
strSQL = strSQL & " FROM " & ApprovingLandingData.APPROVING_LANDING_TABLE & " a," & SysLookupData.SYSLOOKUP_TABLE & " b"
strSQL = strSQL & " WHERE a." & ApprovingLandingData.APPROVING_LANDING_SUBMIT_BY & "='" & userID & "'"
strSQL = strSQL & " AND (a." & ApprovingLandingData.APPROVING_LANDING_DATA_STATUS & "='REJ'"
strSQL = strSQL & " OR a." & ApprovingLandingData.APPROVING_LANDING_DATA_STATUS & "='APP'"
strSQL = strSQL & " OR a." & ApprovingLandingData.APPROVING_LANDING_DATA_STATUS & " LIKE 'P%'"
strSQL = strSQL & " OR a." & ApprovingLandingData.APPROVING_LANDING_DATA_STATUS & "='EDT')"
strSQL = strSQL & " AND b." & SysLookupData.SYSLOOKUP_CODE & "=a." & ApprovingLandingData.APPROVING_LANDING_SCOPE_TYPE_ID
strSQL = strSQL & " UNION "
strSQL = strSQL & "SELECT c." & LandingData.LANDING_PAGE_ID & ","
strSQL = strSQL & "c." & LandingData.LANDING_PAGE_TITLE & ","
strSQL = strSQL & "c." & LandingData.LANDING_DATA_STATUS & ","
strSQL = strSQL & "c." & LandingData.LANDING_SCOPE_TYPE_ID & ","
strSQL = strSQL & "c." & LandingData.LANDING_UPDATE_BY & ","
strSQL = strSQL & "d." & SysLookupData.SYSLOOKUP_DESC
strSQL = strSQL & " FROM " & LandingData.LANDING_TABLE & " c, " & SysLookupData.SYSLOOKUP_TABLE & " d"
strSQL = strSQL & " WHERE c." & LandingData.LANDING_PAGE_ID & " NOT IN ("
strSQL = strSQL & " SELECT " & ApprovingLandingData.APPROVING_LANDING_PAGE_ID
strSQL = strSQL & " FROM " & ApprovingLandingData.APPROVING_LANDING_TABLE
strSQL = strSQL & " WHERE " & ApprovingLandingData.APPROVING_LANDING_DATA_STATUS & " LIKE 'P%'"
strSQL = strSQL & " OR " & ApprovingLandingData.APPROVING_LANDING_DATA_STATUS & "='REJ'"
strSQL = strSQL & " OR " & ApprovingLandingData.APPROVING_LANDING_DATA_STATUS & "='OFF'"
strSQL = strSQL & " OR " & ApprovingLandingData.APPROVING_LANDING_DATA_STATUS & "='APP'"
strSQL = strSQL & " OR " & ApprovingLandingData.APPROVING_LANDING_DATA_STATUS & "='EDT')"
strSQL = strSQL & ") ORDER BY " & sortType


::I tried to get data using this sql, but i get those kind of error msg.But when i tried run the sql using toad, i manage to get the data, wihout problem.someone please help me.

Report message to a moderator

Re: ORA-00918: column ambiguously defined [message #231477 is a reply to message #231471] Tue, 17 April 2007 04:19 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Where does this come from? Java? VB.NET?

Can you do a print from the statement after it is compiled? What does strSQL look like after the concatenation?

MHE

Report message to a moderator

Re: ORA-00918: column ambiguously defined [message #231479 is a reply to message #231471] Tue, 17 April 2007 04:26 Go to previous messageGo to next message
magnetic
Messages: 324
Registered: January 2003
Senior Member
hi there,

be sure that the datatype of each selected column in sections before and after the union are of the same type.
plus, i should use equal column aliases in both sections

greetz.

Report message to a moderator

Re: ORA-00918: column ambiguously defined [message #231483 is a reply to message #231477] Tue, 17 April 2007 04:32 Go to previous messageGo to next message
suhaiza
Messages: 3
Registered: April 2007
Junior Member
Im using asp.net.

The page will show the interface as it should be, only that it got an oracle error msg at the bottom of the page

'Re: ORA-00918: column ambiguously defined'

and the interface won't show any data.

/forum/fa/2355/0/
  • Attachment: error2.JPG
    (Size: 79.59KB, Downloaded 3171 times)

Report message to a moderator

Re: ORA-00918: column ambiguously defined [message #231484 is a reply to message #231479] Tue, 17 April 2007 04:40 Go to previous messageGo to next message
suhaiza
Messages: 3
Registered: April 2007
Junior Member
But why when i tried run the query and change the column name using the actual column name, i got no problem.

SELECT * FROM (SELECT a.PAGE_ID,a.PAGE_TITLE,a.DATA_STATUS,a.SCOPE_TYPE_ID,a.UPDATE_BY,
b.LOOKUP_DESC AS sortType FROM GWC_APPROVING_LANDING_PAGE a,GWC_SYS_LOOKUP b
WHERE a.SUBMIT_BY ='admin1'
AND (a.DATA_STATUS ='REJ'
OR a.DATA_STATUS ='APP'
OR a.DATA_STATUS LIKE 'P%'
OR a.DATA_STATUS ='EDT')
AND b.LOOKUP_CODE = a.SCOPE_TYPE_ID
UNION
SELECT c.PAGE_ID ,c.PAGE_TITLE,c.DATA_STATUS,c.SCOPE_TYPE_ID,c.UPDATE_BY,d.LOOKUP_DESC
FROM GWC_LANDING_PAGE c, GWC_SYS_LOOKUP d
WHERE c.PAGE_ID NOT IN (SELECT PAGE_ID FROM GWC_LANDING_PAGE
WHERE DATA_STATUS LIKE 'P%'
OR DATA_STATUS ='REJ'
OR DATA_STATUS ='OFF'
OR DATA_STATUS ='APP'
OR DATA_STATUS ='EDT')) ORDER BY sortType

Report message to a moderator

Re: ORA-00918: column ambiguously defined [message #231485 is a reply to message #231477] Tue, 17 April 2007 04:42 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Allow me to repeat myself:
Maaher wrote on Tue, 17 April 2007 11:19
Can you do a print from the statement after it is compiled? What does strSQL look like after the concatenation?
Print it to the screen.

MHE

Report message to a moderator

Re: ORA-00918: column ambiguously defined [message #232228 is a reply to message #231485] Thu, 19 April 2007 19:33 Go to previous message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
What are the datatypes of:
b." & SysLookupData.SYSLOOKUP_CODE & "=a."
and
ApprovingLandingData.APPROVING_LANDING_SCOPE_TYPE_ID

I guest that the latter is numeric.

David

Report message to a moderator

Previous Topic: can not retrieve data
Next Topic: how to invoke color and font window.?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Mar 10 05:54:20 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.