Hi: getting this error: ORA-00998: must name this expression with a column alias
A little background info:
This is an Oracle 21c database running on Windows 11 64-bit laptop.
As part of the migration from Oracle 11g to Oracle 21c, I changed several varchar2 columns to nvarchar2 to accommodate multi-language support. I also changed column definitions of varchar2(2000) and greater to clobs.
This is a view I am trying to create; when I run it as a simple "select" it returns the expected results but when I add the "Create view.." to it I get the above error.
The error points to this specific SQL:
JOIN s1_ContactPerson cp
ON cp.NameAndAddressId = naa.NameAndAddressId
AND cp.InactiveFlag = 'N'
JOIN s1_StateProvince -. this is the line that shows the "underlining", not the other lines shown here
ON s1_StateProvince.StateProvinceCode = naa.StateProvinceCode
AND s1_StateProvince.CountryCode = naa.CountryCode
The table definition for s1_StateProvince is:
CREATE TABLE s1_StateProvince
(
StateProvinceCode varchar2(3) not null,
CountryCode varchar2(3) not null,
Name nvarchar2(50) null,
CONSTRAINT PkStateProvince PRIMARY KEY (StateProvinceCode, CountryCode)
using index
tablespace agsoft_index
)
tablespace agsoft_data
/
The abbreviated table definition for the alias "naa" - s1_NameAndAddress is:
CREATE TABLE s1_NameAndAddress
(
NameAndAddressId varchar2(10) not null,
ShortName varchar2(10) null,
FullName nvarchar2(50) not null,
AddressLine1 nvarchar2(250) null,
AddressLine2 nvarchar2(250) null,
City nvarchar2(32) null,
StateProvinceCode varchar2(3) null,
CountryCode varchar2(3) null,
CONSTRAINT PkNameAndAddress PRIMARY KEY (NameAndAddressId)
using index
tablespace agsoft_index
)
tablespace agsoft_data
/
I don't understand the error; any assistance would be appreciated.
Thanks
Murray