Home » SQL & PL/SQL » SQL & PL/SQL » invalid identifier error (oracle 10.1.2)
invalid identifier error [message #341857] Wed, 20 August 2008 10:40 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi
When i run my query i'm getting an error as invlaid identifier, can any one help me why its like that

SELECT CALC_TYPE,

DAYS,
CONTRACT_NUMBER,
NTPACT,
SELECTION_DATE,
AMENDREQ,
RECPROP,
AMEND_STATUS_CODE,
AMENDMENT_NUMBER,
AMENDREQ AS PARAM_DATE,
TO_CHAR(AMENDREQ,'yy"Q"q') QUARTER
FROM (SELECT DISTINCT 'Amendments' CALC_TYPE,
TRUNC(NTPACT-AMENDREQ)DAYS,
NVL(DAYS,MAX(DAYS)) DAYS1,

CONTRACT_NUMBER,
NTPACT,
SELECTION_DATE,
AMENDREQ,
RECPROP,
AMEND_STATUS_CODE,
AMENDMENT_NUMBER,
AMENDREQ AS PARAM_DATE

FROM CONTRACT_VW2
WHERE AMENDMENT_NUMBER > 0
AND AMEND_STATUS_CODE != 34)


Thanks
Re: invalid identifier error [message #341858 is a reply to message #341857] Wed, 20 August 2008 10:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


Error? What error? I don't see any error!
Re: invalid identifier error [message #341860 is a reply to message #341858] Wed, 20 August 2008 10:46 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
When i run the query, i got the following error:
"DAYS" INVALID IDENTIFIER
Thanks
anacedent wrote on Wed, 20 August 2008 10:42
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


Error? What error? I don't see any error!

Re: invalid identifier error [message #341862 is a reply to message #341857] Wed, 20 August 2008 10:51 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


Error? What error? I don't see any error!
Re: invalid identifier error [message #341863 is a reply to message #341862] Wed, 20 August 2008 10:56 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Sir ,
I'm not IDIOT to waste your time and my time if i'm not getting an error, when i run the query i get the error saying "DAYS INVALID IDENTIFIER"

Thanks
anacedent wrote on Wed, 20 August 2008 10:51
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


Error? What error? I don't see any error!


Re: invalid identifier error [message #341864 is a reply to message #341860] Wed, 20 August 2008 11:00 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You are completely missing the point. Don't you think it avoids ambiguity if could copy and paste the actual error instead of typing the error.
Quote:
TRUNC(NTPACT-AMENDREQ)DAYS,
NVL(DAYS,MAX(DAYS)) DAYS1,

In oracle you cannot reference a column alias in the same level of the query. It can be referenced only in the next higher level. Hope it makes sense.

Regards

Raj

[Edit:] Typo corrected

[Updated on: Wed, 20 August 2008 11:01]

Report message to a moderator

Re: invalid identifier error [message #341865 is a reply to message #341864] Wed, 20 August 2008 11:05 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Thank You
But as a novice can you please explain me what should i do, where should i decalre both the aliases?

Thanks
S.Rajaram wrote on Wed, 20 August 2008 11:00
You are completely missing the point. Don't you think it avoids ambiguity if could copy and paste the actual error instead of typing the error.
Quote:
TRUNC(NTPACT-AMENDREQ)DAYS,
NVL(DAYS,MAX(DAYS)) DAYS1,

In oracle you cannot reference a column alias in the same level of the query. It can be referenced only in the next higher level. Hope it makes sense.

Regards

Raj

[Edit:] Typo corrected

Re: invalid identifier error [message #341867 is a reply to message #341865] Wed, 20 August 2008 11:13 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
NVL(DAYS,MAX(DAYS)) DAYS1,

Well, to be honest with you I don't know how to replace the above expression because I am not able to see any group by clause. You can mix aggregate function and non-aggregated columns in the same select statement.
So what I would request you to do is post a test case, expected output and a brief explanation. All these information are already mentioned in the forum guidelines. So if you would spend some of your precious time reading it by this time you would have solved the problem. Better late than never from now onwards if you could stick to this point it will be lot more easier for you and also for co-forum members who are willing to help you.

Regards

Raj

[Edit:] Again a typo

[Updated on: Wed, 20 August 2008 11:15]

Report message to a moderator

Previous Topic: AVERAGE
Next Topic: problem with trigger
Goto Forum:
  


Current Time: Sun Dec 04 10:55:44 CST 2016

Total time taken to generate the page: 0.10983 seconds