couldn\'t design query in IReport

[maanikmca]

 

Here i have posted my Table Structure..Here am extracting details for Faculty Invigilation from Database

SQL> desc roomallot;

 Name                         

             Null?    Type
 -----------------------------------------     --------     ----------------------------
 RDATE                                                  DATE
 RNO                                                      NUMBER(10)
 SUBCODE                                            VARCHAR2(15)
 SESS                                                   VARCHAR2(5)
 STNO                                                   VARCHAR2(20)
 ENNO                                                   VARCHAR2(20)
 FACULTY                                              VARCHAR2(25)
 SEM                                                     VARCHAR2(15)
 TRANSID                             NOT NULL  NUMBER(20)
 COUNTER                                             NUMBER(30)


This is the Query,which is giving me an Error,


Query
 
{ SELECT
     ROOMALLOT."FACULTY" AS ROOMALLOT_FACULTY,
     COUNT(*)  AS ROOMALLOT_COUNT   FROM
(SELECT DISTINCT  ROOMALLOT."FACULTY", ROOMALLOT."RDATE", ROOMALLOT."SESS"  FROM
 "MODULE4"."ROOMALLOT"  ROOMALLOT)  GROUP BY  ROOMALLOT."FACULTY" }
 
 
it gives me an Error.
 
 
Error: "ROOMALLOT"."FACULTY": invalid identifier

 

could you please sort out my as much as earlier..

 

advanc thanks..

[luciano_castro]

hi maanik777,

 

try this :

 

{ SELECT
     random_name."FACULTY" AS ROOMALLOT_FACULTY,
     COUNT(*)  AS ROOMALLOT_COUNT   FROM
( SELECT DISTINCT  ROOMALLOT."FACULTY", ROOMALLOT."RDATE", ROOMALLOT."SESS"  FROM
 "MODULE4"."ROOMALLOT"  ROOMALLOT ) random_name  GROUP BY  random_name."FACULTY" }

 

hope that helps

_o/

[mdahlman]

maanik777,

It's not really an iReport question. You might be better help on an Oracle forum.

Having said that... I can't help making a guess. You need to alias your subquery in Oracle. I forget if it requires the use of "AS" or requires that you do not use "AS". But something like this ought to work:

SELECT
     ROOMALLOT."FACULTY" AS ROOMALLOT_FACULTY,
     COUNT(*)  AS ROOMALLOT_COUNT   FROM
(SELECT DISTINCT  ROOMALLOT."FACULTY", ROOMALLOT."RDATE", ROOMALLOT."SESS"  FROM
 "MODULE4"."ROOMALLOT"  ROOMALLOT) AS MYSUBQUERY GROUP BY  ROOMALLOT."FACULTY"

Regards,
Matt

[maanikmca]

Mr.luciano_castro...your suggestion really works perfectly...thanks alot for your wonderful reply..and alloted time for my query...hats off...