ORA-00918: column ambiguously defined-CodePudding

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.

CodePudding user response：

You need aliases for the nulls. At least in your first query, so that Oracle knows how to call the columns in the result:

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
...

CodePudding user response：

In addition to @Thorsten 's answer you need to declare the datatype of your nulls using a CAST() function. Something like,

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