Jump to content

My oracle query does not work


grecorromano
 Share

Recommended Posts

Hi, i'm trying to execute this query in oracle but it keeps saying "ORA-0094: "GANADAS": identifier not valid"SELECT P.tituloOriginal,(SELECT COUNT(Pos.id) FROM Postulada Pos Where Pos.id_pelicula=P.id) AS "Nominaciones",(SELECT COUNT(Pos.id) FROM Postulada Pos Where ganadora='si' and Pos.id_pelicula=P.id) AS "Ganadas" FROM Pelicula PWHERE Nominaciones>=4 AND Nominaciones=Ganadas;I want to show all the movies that have won all their nominations if they have more than 4.here are the tables:CREATE TABLE POSTULADA( id NUMBER(4) , fecha DATE NOT NULL, nominada VARCHAR2(2) NOT NULL, ganadora VARCHAR2(2) NOT NULL, premiadoPost RECIBIDO_NT , rechazado VARCHAR2(2) , id_premio NUMBER(4) NOT NULL, id_pelicula NUMBER (4) NOT NULL, CONSTRAINT postuladanominada CHECK(nominada IN ('si','no')), CONSTRAINT postuladaganadora CHECK(ganadora IN ('si','no')), CONSTRAINT postuladarechazado CHECK(rechazado IN ('si','no')), CONSTRAINT PK_postulada PRIMARY KEY (id))NESTED TABLE premiadoPost STORE AS premiados_TA;CREATE TABLE PELICULA( id NUMBER(4) , tituloOriginal VARCHAR2(100) NOT NULL, traducido TRADUCIDO_VA , estreno DATE NOT NULL, duracion NUMBER(3) NOT NULL, censura VARCHAR2(10) NOT NULL, idioma VARCHAR2(45) NOT NULL, estado VARCHAR2(30) NOT NULL, sinopsis VARCHAR2(1000) NOT NULL, imagen BFILE , parteSecuela NUMBER(2) , id_lugar NUMBER(4) , id_genero NUMBER(4) NOT NULL, id_pelicula NUMBER(4) , CONSTRAINT estadopelicula CHECK(estado IN ('produccion','postproduccion','finalizada')), CONSTRAINT PK_pelicula PRIMARY KEY (id));Hope you can help me. Sorry the tables and the atributes are in spanish.thanks.

Link to comment
Share on other sites

Revision in Bold. Does this fix the issue?SELECT P.tituloOriginal, (SELECT COUNT(Pos.id) FROM Postulada Pos Where Pos.id_pelicula=P.id) AS "Nominaciones", (SELECT COUNT(Pos.id) FROM Postulada Pos Where Pos.ganadora='si' and Pos.id_pelicula=P.id) AS "Ganadas"

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...