Reputation: 11
Can u tell me, why it wont work?:)
select *
from( select r.region_id, c.country_id
from countries c join regions r on r.region_id = c.region_id)
unpivot(
valuee for columnValue in (r.region_id))
ORA-01748: only simple column names allowed here 01748. 00000 - "only simple column names allowed here"
Upvotes: 0
Views: 288
Reputation: 191570
Your query is strange and it isn't really clear what you're trying to achieve; but following up on @VBoka's correction of your code, you don't actually need the join at all (unless you have countries with non-existent regions) - you can do:
select *
from (
select region_id, country_id
from countries
)
unpivot(valuee for columnValue in (region_id));
But you can get the same result without unpivoting; with the join if you have a real reason to include it:
select c.country_id, 'REGION_ID' as columnvalue, r.region_id as valuee
from countries c
join regions r on r.region_id = c.region_id;
or without the join:
select country_id, 'REGION_ID' as columnvalue, region_id as valuee
from countries;
Either way you get a result set with one row for every country.
Upvotes: 0
Reputation: 9083
With this part:
select *
You are selecting columns: region_id
and country_id
from your inner select. So you do not need r.region_id
in your UNPIVOT section, only region_id
.
This code is correct(without error):
select *
from(select r.region_id
, c.country_id
from countries c
join regions r on r.region_id = c.region_id)
unpivot(valuee for columnValue in (region_id));
Upvotes: 1