grecorromano Posted June 5, 2010 Share Posted June 5, 2010 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 More sharing options...
justsomeguy Posted June 6, 2010 Share Posted June 6, 2010 I'm not sure about Oracle specifically, but generally in SQL you don't quote the name of the alias. Link to comment Share on other sites More sharing options...
optimus203 Posted June 6, 2010 Share Posted June 6, 2010 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 More sharing options...
grecorromano Posted June 7, 2010 Author Share Posted June 7, 2010 No worries i used a PL/SQL Function to fix the problem. thanks anyway Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.