Home » SQL & PL/SQL » SQL & PL/SQL » Query contain MODEL cause "column not allowed here" in select statement
Query contain MODEL cause "column not allowed here" in select statement [message #667481] Mon, 01 January 2018 21:37 Go to next message
vincenttic
Messages: 3
Registered: January 2018
Junior Member
/foru/forum/fa/13729/0/

I cannot find a formal document describe this issue: Cannot add table prefix or table alias in select statement if the Query include MODEL statement.
But the test show that it is true, any one can confirm this or give me a work around for it ?
Thanks in advance for the reply.

Vincent
  • Attachment: model.png
    (Size: 113.95KB, Downloaded 193 times)
Re: Query contain MODEL cause "column not allowed here" in select statement [message #667482 is a reply to message #667481] Mon, 01 January 2018 22:04 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Query contain MODEL cause "column not allowed here" in select statement [message #667483 is a reply to message #667482] Mon, 01 January 2018 23:46 Go to previous messageGo to next message
vincenttic
Messages: 3
Registered: January 2018
Junior Member
I supplement the detail sql here for reference,Thanks for the reply.

1) This sql runs successfully
 
select 
address_id, 
customer_id,
sex,
substr(contact_id,2,1) contact_id 
from testoracle.contact
where customer_id in ('5','6')
model
return updated rows
partition by (customer_id)
dimension by (sex,contact_id)
measures (address_id)
rules
(
address_id['F',9] = 1,
address_id['M',9] = 2,
address_id['F',1] = 3,
address_id['M',1] = 4)
order by address_id,customer_id,sex; 

2) If we add a table alias in select statement - "c.address_id", the sql runs failed, the error output is "Error at line 2: ORA-00984: column not allowed here"
select 
c.address_id, 
customer_id,
sex,
substr(contact_id,2,1) contact_id 
from testoracle.contact c
where customer_id in ('5','6')
model
return updated rows
partition by (customer_id)
dimension by (sex,contact_id)
measures (address_id)
rules
(
address_id['F',9] = 1,
address_id['M',9] = 2,
address_id['F',1] = 3,
address_id['M',1] = 4)
order by address_id,customer_id,sex; 

3)If use table prefix directly in select statement - "testoracle.contact.address_id", it also failed, the error is the same "Error at line 2: ORA-00984: column not allowed here"
select 
testoracle.contact.address_id, 
customer_id,
sex,
substr(contact_id,2,1) contact_id 
from testoracle.contact
where customer_id in ('5','6')
model
return updated rows
partition by (customer_id)
dimension by (sex,contact_id)
measures (address_id)
rules
(
address_id['F',9] = 1,
address_id['M',9] = 2,
address_id['F',1] = 3,
address_id['M',1] = 4)
order by address_id,customer_id,sex;
Re: Query contain MODEL cause "column not allowed here" in select statement [message #667499 is a reply to message #667483] Tue, 02 January 2018 07:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
First Oracle fetches rows from testoracle.contact table. Then it applied model which returns nothing but partition values, dimension values and measure values while you are trying to add column to select list. That's why you get " ORA-00984: column not allowed here".

SY.
Re: Query contain MODEL cause "column not allowed here" in select statement [message #667506 is a reply to message #667499] Tue, 02 January 2018 21:00 Go to previous message
vincenttic
Messages: 3
Registered: January 2018
Junior Member
Thanks Solomon, it is the correct answer we are looking for.
Previous Topic: Function or filter in hierarchy query
Next Topic: Why does Oracle rename the bind variable?
Goto Forum:
  


Current Time: Fri Oct 19 02:50:35 CDT 2018