oracle ORA-00918:列定义不明确

omvjsjqw  于 2022-12-11  发布在  Oracle
关注(0)|答案(2)|浏览(136)

I'm learning SQL Oracle and tried to create a view, but I keep getting this error. I know is because of that NULL, but still don't know how to fix it. Any advice is helpful. Thanks.

CREATE VIEW Produse_HP (model, categorie, viteza, ram, hd, ecran, culoare, tip, pret)
AS
SELECT * FROM
(SELECT model, categorie, viteza, ram, hd, NULL, NULL, NULL, pret
FROM Produs NATURAL JOIN PC
WHERE fabricant = 'HP' UNION
SELECT model, categorie, viteza, ram, hd, ecran, NULL, NULL, pret
FROM Produs NATURAL JOIN Laptop
WHERE fabricant = 'HP' UNION
SELECT model, categorie, NULL, NULL, NULL, NULL, culoare, tip, pret
FROM Produs NATURAL JOIN Imprimanta
WHERE fabricant = 'HP');

It suppose to show those collumns with SELECT [model, categorie, viteza, ram, hd, ecran, culoare, tip, pret]. I need it this way because I need it in a instead-of trigger, to insert values through this view.

gt0wga4j

gt0wga4j1#

您需要空值的别名。至少在第一个查询中是这样,以便Oracle知道如何在结果中调用列:

SELECT
  model, categorie, viteza, ram, hd,
  NULL AS ecran, NULL AS culoare, NULL AS tip, pret
FROM Produs NATURAL JOIN PC
WHERE fabricant = 'HP'
UNION
...
5gfr0r5j

5gfr0r5j2#

除了@Thorsten的答案之外,您还需要使用CAST()函数声明空值的数据类型,

CAST(Null as Varchar) as ecran, CAST(Null as Date) as culoare...

相关问题