OraFAQ Forum: SQL & PL/SQL » Query contain MODEL cause "column not allowed here" in select statement

Home » SQL & PL/SQL » SQL & PL/SQL » Query contain MODEL cause "column not allowed here" in select statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query contain MODEL cause "column not allowed here" in select statement [message #667481]

Mon, 01 January 2018 21:37

vincenttic
Messages: 3
Registered: January 2018

Junior Member

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 783 times)

Report message to a moderator

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

BlackSwan
Messages: 26766
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

Report message to a moderator

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

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;

Report message to a moderator

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

Solomon Yakobson
Messages: 3244
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.

Report message to a moderator

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

vincenttic
Messages: 3
Registered: January 2018

Junior Member

Thanks Solomon, it is the correct answer we are looking for.

Report message to a moderator

Previous Topic:	Function or filter in hierarchy query
Next Topic:	Why does Oracle rename the bind variable?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 26 03:22:46 CDT 2023