Home » Developer & Programmer » Forms » Problem with a problem in SELECT statement (merged by LF)
Problem with a problem in SELECT statement (merged by LF) [message #261618] Thu, 23 August 2007 02:29 Go to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i m making a report to count attendance percentage of a student in a given month.But getting an error on subquery

ORA-00936: missing expression

i m writing this code:

SELECT ALL (COUNT(STUDENT_ATTENDANCE.ATTENDANCE_MODE))/

(SELECT
COUNT(DISTINCT STUDENT_ATTENDANCE.ATTENDANCE_DATE)

FROM STUDENT_ATTENDANCE

WHERE TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON')='jul') * 100,

STUDENT.REGESTRATION_NO

FROM STUDENT_ATTENDANCE,STUDENT

WHERE STUDENT_ATTENDANCE.ATTENDANCE_MODE = 'present'

AND TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL'


AND (STUDENT_ATTENDANCE.REGESTRATION_NO = STUDENT.REGESTRATION_NO)
GROUP BY STUDENT.REGESTRATION_NO


plz tell me any solution.
thnks
sara
Re: strange problem with subquery in select statment [message #261647 is a reply to message #261618] Thu, 23 August 2007 03:47 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Check your syntax. It is a syntax error.
By the way what you want to make with this query.

Cheers
Sanka
Re: strange problem with subquery in select statment [message #261665 is a reply to message #261618] Thu, 23 August 2007 05:13 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Instead of playing around with colors, you'd rather properly format the code. This is un-re-a-da-ble. Also, we don't know how those tables look like and what is stored in there; therefore, CREATE TABLE along with INSERT INTO sample data plus expected result set might help.
Re: strange problem with subquery in select statment [message #262389 is a reply to message #261665] Sun, 26 August 2007 22:36 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i m making a report to count attendance percentage of a student in a given month.But getting an error on subquery

ORA-00936: missing expression


i m writing this code:

SELECT ALL (COUNT(STUDENT_ATTENDANCE.ATTENDANCE_MODE))/

(SELECT COUNT(DISTINCT STUDENT_ATTENDANCE.ATTENDANCE_DATE)
FROM STUDENT_ATTENDANCE
WHERE TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON')='jul') * 100,

STUDENT_attendance.REGESTRATION_NO
FROM STUDENT_ATTENDANCE
WHERE STUDENT_ATTENDANCE.ATTENDANCE_MODE = 'present'

AND TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL'
AND (STUDENT_ATTENDANCE.REGESTRATION_NO = STUDENT.REGESTRATION_NO)
GROUP BY STUDENT.REGESTRATION_NO

description of table used(student_attendance) :
SQL> describe student_attendance;
Name Null? Type
------------------------------- -------- ----
REGESTRATION_NO VARCHAR2(20)
SUBJECT_ID NUMBER(12)
LOGIN_ID VARCHAR2(20)
ATTENDANCE_MODE VARCHAR2(20)
ATTENDANCE_DATE DATE

sample data in table:


REGESTRATION_NO SUBJECT_ID LOGIN_ID ATTENDANCE_MODE ATTENDANC
-------------------- ---------- -------------------- -------------------- ---------
r11 111 1 present 25-JUL-07
r12 111 1 present 25-JUL-07
r13 111 1 absent 25-JUL-07
r14 111 1 presesnt 25-JUL-07
r15 111 1 present 25-JUL-07
r40 112 2 present 25-JUL-07
Re: strange problem with subquery in select statment [message #262432 is a reply to message #261665] Mon, 27 August 2007 00:50 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Littlefoot
You'd rather properly format the code. CREATE TABLE along with INSERT INTO sample data plus expected result set might help.

Do you really expect us (OK, me) to type all that instead of you?

When I said "format the code", I really meant you should format code you post (using the [code] tags).
When I said CREATE TABLE, I really meant CREATE TABLE, not result of DESC SQL*Plus command.
When I said INSERT INTO sample data, I really meant INSERT INTO statements, not result of SELECT * FROM statement.
When I asked for expected result set, I really meant it. I don't see any.
Re: strange problem with subquery in select statment [message #262461 is a reply to message #262432] Mon, 27 August 2007 02:08 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i m making a report to count attendance percentage of a student in a given month.But getting an error on subquery

ORA-00936: missing expression

i m writing this code:

SELECT ALL (COUNT(STUDENT_ATTENDANCE.ATTENDANCE_MODE))/

      (SELECT COUNT(DISTINCT STUDENT_ATTENDANCE.ATTENDANCE_DATE)
      FROM STUDENT_ATTENDANCE
      WHERE TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON')='jul') * 100, 

 STUDENT_attendance.REGESTRATION_NO
 FROM STUDENT_ATTENDANCE
 WHERE STUDENT_ATTENDANCE.ATTENDANCE_MODE = 'present'

 AND TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL' 
 AND (STUDENT_ATTENDANCE.REGESTRATION_NO = STUDENT.REGESTRATION_NO)
 GROUP BY STUDENT.REGESTRATION_NO


TABLE:
create table Student_attendance(registration_no varchar2(30) ,
login_id varchar2(20),subject_id number(*),att_mode varchar2(30),
date date,
constraint fk_Student_attendance_login_id foreignkey(login_id) references Login(login_id),
constraint fk_Student_attendance_registration_no foreignkey(registration_no) references Student(registration_no),
constraint fk_Student_attendance_subject_id foreignkey(subject_id) references Subject(subject_id));


insert into student_attendance(regestration_no,subject_id,login_id,attendance_mode,attendance_date)
values (r11,111,1,'present','30-JUL-2007');


Report should show attendance percentage of each student in each subject

Attendance percentage= total number of 'presents'/ total number of lectures * 100

Lectures=all number of days of a month in which attendance is entered


thnks


[Updated on: Mon, 27 August 2007 02:09]

Report message to a moderator

strange problem with subquerry [message #263387 is a reply to message #261618] Wed, 29 August 2007 22:50 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
I want to calculate attendance percentage (total no of present in a month / total no of lectures in given month)

when i write this code following error occurs:

error :
ORA-00936: missing expression

The code is:

SELECT ALL (COUNT(STUDENT_ATTENDANCE.ATTENDANCE_MODE))/
(SELECT
COUNT(DISTINCT STUDENT_ATTENDANCE.ATTENDANCE_DATE)
FROM STUDENT_ATTENDANCE
WHERE  TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL' ) * 100, 
STUDENT.REGESTRATION_NO
FROM STUDENT_ATTENDANCE,STUDENT 
WHERE STUDENT_ATTENDANCE.ATTENDANCE_MODE = 'present'
 AND TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL' 
AND (STUDENT_ATTENDANCE.REGESTRATION_NO = STUDENT.REGESTRATION_NO)
GROUP BY STUDENT.REGESTRATION_N


when i run this code(hard code) in parts then it gives correct result
eg first part:
SELECT ALL (COUNT(STUDENT_ATTENDANCE.ATTENDANCE_MODE))/6 * 100,
STUDENT.REGESTRATION_NO
FROM STUDENT_ATTENDANCE,STUDENT 
WHERE STUDENT_ATTENDANCE.ATTENDANCE_MODE = 'present'
 AND TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL' 
AND (STUDENT_ATTENDANCE.REGESTRATION_NO = STUDENT.REGESTRATION_NO)
GROUP BY STUDENT.REGESTRATION_No


AND also subquerry part also gives right result
SELECT
 COUNT(DISTINCT STUDENT_ATTENDANCE.ATTENDANCE_DATE)
 FROM STUDENT_ATTENDANCE
 WHERE  TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL' 


i don't why it is giving error when i run this code in together by subquerry

PLZ help me
it is very urgent
thanks


And also
Re: strange problem with subquerry [message #263400 is a reply to message #263387] Wed, 29 August 2007 23:45 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
You have two ')' after 'ATTENDANCE_MODE' on the first line, try moving the second one AFTER the '100'. Alternatively, use 'to_number(to_char(' around the two 'count' statement to make sure that they are numeric.

David
Re: strange problem with subquerry [message #263409 is a reply to message #263400] Thu, 30 August 2007 00:08 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i have tried both suggestions but didn't work.
plz tell me any other solution if u have any idea!
Re: strange problem with subquerry [message #263417 is a reply to message #263409] Thu, 30 August 2007 00:23 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please be more specific!! What messages did you get?

David
Re: strange problem with subquerry [message #263740 is a reply to message #263417] Thu, 30 August 2007 22:27 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i had tried your both suggestions but same error occur:
Quote:
error :
ORA-00936: missing expression



I m trying to solve this problem from many days but i m not able to find solution.

please give me suggestion to solve this problem

thanks Cool
Re: strange problem with subquerry [message #263750 is a reply to message #263740] Thu, 30 August 2007 23:38 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Thanks. What version of Oracle Database are you running?

David
Re: strange problem with subquerry [message #263756 is a reply to message #263750] Thu, 30 August 2007 23:54 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
I m using:

Quote:
oracle8i and developer6i


thanks Cool
Re: strange problem with subquerry [message #263762 is a reply to message #263756] Fri, 31 August 2007 00:01 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Try specifying a column alias for the derived fields.

David
icon9.gif  Re: strange problem with subquerry [message #263789 is a reply to message #263762] Fri, 31 August 2007 01:04 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i have created alias but it didn't work....
geting same error message

plz tell me any other solution.i m very worried

[Updated on: Fri, 31 August 2007 01:07]

Report message to a moderator

Re: strange problem with subquerry [message #263792 is a reply to message #263789] Fri, 31 August 2007 01:24 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
So am I.

Please post the creation script for your table and use a tool to extract 'insert' statements for some (or all) of your data. I will install in one of my test databases and see what I can find.

I can test on Oracle 7, 9 and 10 but I don't have access to 8.

David
Re: strange problem with subquery in select statment [message #264121 is a reply to message #262461] Fri, 31 August 2007 23:20 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
LITTLEFOOT
Re: strange problem with subquerry [message #264125 is a reply to message #263792] Fri, 31 August 2007 23:56 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i m getting an error:
ORA-00936: missing expression

i m writing this code to calculate the attendance percentage of each student.

SELECT ALL (COUNT(STUDENT_ATTENDANCE.ATTENDANCE_MODE))/
(SELECT
COUNT(DISTINCT STUDENT_ATTENDANCE.ATTENDANCE_DATE)
FROM STUDENT_ATTENDANCE
WHERE  TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL' ) * 100,regestration_no
FROM STUDENT_ATTENDANCE
WHERE STUDENT_ATTENDANCE.ATTENDANCE_MODE = 'present'
 AND TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL' 
GROUP BY REGESTRATION_No;

Report should show attendance percentage of each student

Attendance percentage= total number of 'presents'/ total number of lectures * 100
Lectures=all number of days of a month in which attendance is entered

CREATE TABLE STATMENT

create table Student_attendance(regestration_no varchar2(30) ,
subject_id number(*),login_id  varchar2(20),att_mode varchar2(30),
date date,
constraint fk_Student_attendance_login_id foreign key(login_id)
    references Login(login_id),
constraint fk_Student_attendance_regestration_no foreign key(regestration_no)
    references Student(registration_no),
constraint fk_Student_attendance_subject_id foreign key(subject_id)
    references Subject(subject_id));

INSERT STATMENT (sample data)
insert into student_attendance
  values ('r11',111,1,'present','28-JUL-2007');
insert into student_attendance
  values ('r12',111,1,'present','28-JUL-2007');
insert into student_attendance
  values ('r11',111,1,'absent','29-JUL-2007');
insert into student_attendance
  values ('r12',111,1,'absent','29-JUL-2007');
insert into student_attendance
  values ('r11',111,1,'present','30-JUL-2007');
insert into student_attendance
  values ('r12',111,1,'absent','30-JUL-2007');

using oracle8i and developer6i

plz tell me whats the problem with this code.
thnks

[Updated on: Tue, 11 September 2007 01:45] by Moderator

Report message to a moderator

Re: strange problem with subquery in select statment [message #264155 is a reply to message #264121] Sat, 01 September 2007 03:30 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes? Are you reminding me to answer this question? I'm still unable to. Obviously, you didn't quite understand what I asked you to do (TWICE!), and your script is useless. If you can do something with information provided, please, be my guest.
SQL> create table Student_attendance(registration_no varchar2(30) ,
  2  login_id varchar2(20),subject_id number(*),att_mode varchar2(30),
  3  date date,
  4  constraint fk_Student_attendance_login_id foreignkey(login_id) references L
ogin(login_id),
  5  constraint fk_Student_attendance_registration_no foreignkey(registration_no
) references Student(registration_no),
  6  constraint fk_Student_attendance_subject_id foreignkey(subject_id) referenc
es Subject(subject_id));
date date,
*
ERROR at line 3:
ORA-00904: : invalid identifier


SQL> insert into student_attendance(regestration_no,subject_id,login_id,attendan
ce_mode,attendance_date)
  2  values (r11,111,1,'present','30-JUL-2007');
insert into student_attendance(regestration_no,subject_id,login_id,attendance_mo
de,attendance_date)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
Re: strange problem with subquery in select statment [message #264300 is a reply to message #264155] Sun, 02 September 2007 23:26 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i am again sending you the code and this time it is correct..

but i m getting an error:
ORA-00936: missing expression

i m writing this code to calculate the attendance percentage of each student.

SELECT ALL (COUNT(STUDENT_ATTENDANCE.ATTENDANCE_MODE))/
(SELECT
COUNT(DISTINCT STUDENT_ATTENDANCE.ATTENDANCE_DATE)
FROM STUDENT_ATTENDANCE
WHERE  TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL' ) * 100,regestration_no
FROM STUDENT_ATTENDANCE
WHERE STUDENT_ATTENDANCE.ATTENDANCE_MODE = 'present'
 AND TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL' 
GROUP BY REGESTRATION_No;

Report should show attendance percentage of each student

Attendance percentage= total number of 'presents'/ total number of lectures * 100
Lectures=all number of days of a month in which attendance is entered

CREATE TABLE STATMENT
create table Student_attendance(regestration_no varchar2(30) ,
subject_id number(*),login_id  varchar2(20),att_mode varchar2(30),
attendance_date date ,
constraint fk_sa_login_id foreign key(login_id) references Login(login_id),
constraint fk_sa_reg_no foreign key(regestration_no) references Student(regestration_no),
constraint fk_Sa_sub_id foreign key(subject_id) references Subject(subject_id));

INSERT STATMENT (sample data)
insert into student_attendance
values ('r11',111,1,'present','28-JUL-2007');
insert into student_attendance
values ('r12',111,1,'present','28-JUL-2007');
insert into student_attendance
values ('r11',111,1,'absent','29-JUL-2007');
insert into student_attendance
values ('r12',111,1,'absent','29-JUL-2007');
insert into student_attendance
values ('r11',111,1,'present','30-JUL-2007');

using oracle8i and developer6i

plz tell me whats the problem with this code.
thnks

[Updated on: Tue, 11 September 2007 01:50] by Moderator

Report message to a moderator

Re: strange problem with subquery in select statment [message #264339 is a reply to message #264300] Mon, 03 September 2007 01:20 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
And I'm telling you again that CREATE TABLE statement - as you've written it - will never work on any existing Oracle database.

I have that strange feeling that this conversation is as if you were deaf and I was dumb.
Re: strange problem with subquerry [message #264356 is a reply to message #264125] Mon, 03 September 2007 01:48 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please review your table definition and sample data. You have a five field table with data types of varchar2, number, varchar2, varchar2, and date. But you have the name of the date field as 'date'. This is not legal. You have sample data that has character, number, number, character, and date. This is different to the data types you have defined in your table. The constraint names are too long, 'fk_Student_attendance_regestration_no' is 37 characters.

Please correct these things, especially the 'date' 'date' field, and get back to us.

David
Re: strange problem with subquerry [message #264890 is a reply to message #264356] Tue, 04 September 2007 23:08 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i am again sending you the code and this time it is correct..

but i m getting an error:
ORA-00936: missing expression

i m writing this code to calculate the attendance percentage of each student.
plz tell me whats the problem with this code.

SELECT ALL (COUNT(STUDENT_ATTENDANCE.ATTENDANCE_MODE))/
(SELECT
COUNT(DISTINCT STUDENT_ATTENDANCE.ATTENDANCE_DATE)
FROM STUDENT_ATTENDANCE
WHERE  TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL' ) * 100,regestration_no
FROM STUDENT_ATTENDANCE
WHERE STUDENT_ATTENDANCE.ATTENDANCE_MODE = 'present'
 AND TO_CHAR (STUDENT_ATTENDANCE.ATTENDANCE_DATE, 'MON' ) ='JUL' 
GROUP BY REGESTRATION_No;



Attendance percentage= total number of 'presents'/ total number of lectures * 100

Lectures=all number of days of a month in which attendance is entered


These "create table" statments and "insert into" are working correctly.

CREATE TABLE STATMENT

create table Student_attendance(regestration_no varchar2(30) ,
subject_id number(*),login_id  varchar2(20),att_mode varchar2(30),
attendance_date date ,
constraint fk_sa_login foreign key(login_id) references Login(login_id),
constraint fk_sa_reg foreign key(regestration_no) references Student(regestration_no),
constraint fk_Sa_sub foreign key(subject_id) references Subject(subject_id));


INSERT STATMENT (sample data)
insert into student_attendance
values ('r11',111,1,'present','28-JUL-2007');


insert into student_attendance
values ('r12',111,1,'present','28-JUL-2007');


insert into student_attendance
values ('r11',111,1,'absent','29-JUL-2007');


insert into student_attendance
values ('r12',111,1,'absent','29-JUL-2007');

insert into student_attendance
values ('r11',111,1,'present','30-JUL-2007');


thnks

[Updated on: Tue, 04 September 2007 23:08]

Report message to a moderator

Re: strange problem with subquerry [message #264930 is a reply to message #264890] Wed, 05 September 2007 01:13 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This CREATE TABLE statement probably does run in your schema and database, but it doesn't in mine. Why?
  • I don't have 'login', 'student' and 'subject' tables so all foreign key constraints fail (and, consequentially, CREATE TABLE fails as well)
  • you are inserting a string '28-JUL-2007' into a DATE datatype column. It does look like a date (to a human), but for Oracle it is a string. You'd need to use TO_DATE function in order to make sure it will ALWAYS be inserted. Moreover, 'JUL' is nonexistent month in my country; our abbreviation is 'SRP' (comes from 'srpanj' = 'july'). And finally, you are relying on current NLS_DATE_FORMAT settings which are / may be different in different databases.
  • 'login_id' column is a character, but you are inserting a NUMBER into it

I'd rewrite it as follows:
SQL> CREATE TABLE Student_attendance
  2    (regestration_no   VARCHAR2(30) ,
  3     subject_id        NUMBER(*),
  4     login_id          VARCHAR2(20),
  5     att_mode          VARCHAR2(30),
  6     attendance_date   DATE
  7    );

Table created.

SQL> INSERT INTO student_attendance
  2  VALUES ('r11', 111, '1', 'present', TO_DATE('28-srp-2007', 'dd-mon-yyyy'));


1 row created.

SQL> INSERT INTO student_attendance
  2  VALUES ('r12', 111, '1', 'present', TO_DATE('28-srp-2007', 'dd-mon-yyyy'));


1 row created.

SQL> INSERT INTO student_attendance
  2  VALUES ('r11', 111, '1', 'absent',  TO_DATE('29-srp-2007', 'dd-mon-yyyy'));


1 row created.

SQL> INSERT INTO student_attendance
  2  VALUES ('r12', 111, '1', 'absent',  TO_DATE('29-srp-2007', 'dd-mon-yyyy'));


1 row created.

SQL> INSERT INTO student_attendance
  2  VALUES ('r11', 111, '1', 'present', TO_DATE('30-srp-2007', 'dd-mon-yyyy'));


1 row created.

SQL> select * from student_attendance;

REGESTRATION_NO SUBJECT_ID LOGIN_ID   ATT_MODE   ATTENDAN
--------------- ---------- ---------- ---------- --------
r11                    111 1          present    28.07.07
r12                    111 1          present    28.07.07
r11                    111 1          absent     29.07.07
r12                    111 1          absent     29.07.07
r11                    111 1          present    30.07.07

OK, now we have created a working environment.

I've rewritten and fixed errors in your query (removed table anme to improve readability; changed 'attendance_mode' into 'att_mode' as you've said it should be a column name), executed it and got the result. No 'missing expression' here (on 10g):
SQL> SELECT ALL   (COUNT (att_mode))
  2             / (SELECT COUNT (DISTINCT attendance_date)
  3                  FROM student_attendance
  4                 WHERE TO_CHAR (attendance_date, 'MON') = 'SRP'
  5               )
  6             * 100 result,
  7             regestration_no
  8        FROM student_attendance
  9       WHERE att_mode = 'present'
 10         AND TO_CHAR (attendance_date, 'MON') = 'SRP'
 11    GROUP BY regestration_no;

    RESULT REGESTRATION_NO
---------- ---------------
66.6666667 r11
33.3333333 r12

SQL>
Is this the result you expected?
Re: strange problem with subquery in select statment [message #265192 is a reply to message #264300] Wed, 05 September 2007 13:02 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
sams wrote on Sun, 02 September 2007 23:56
this time it is correct..



Well, almost...
If we remove the reference parts of the create table statement and change attendance_mode to att_mode in the select statement, it works:
SQL> SELECT ALL (COUNT(student_attendance.att_mode)) /
  2             (SELECT COUNT(DISTINCT student_attendance.attendance_date)
  3              FROM   student_attendance
  4              WHERE  to_char(student_attendance.attendance_date
  5                            ,'MON') = 'JUL') * 100
  6            ,regestration_no
  7  FROM   student_attendance
  8  WHERE  student_attendance.att_mode = 'present'
  9  AND    to_char(student_attendance.attendance_date
 10                ,'MON') = 'JUL'
 11  GROUP  BY regestration_no;

(COUNT(STUDENT_ATTENDANCE.ATT_ REGESTRATION_NO
------------------------------ ------------------------------
              66,6666666666667 r11
              33,3333333333333 r12

SQL>

So, where did you get this ORA-00936?
Re: strange problem with subquerry [message #265268 is a reply to message #264930] Wed, 05 September 2007 23:23 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
thnks .i have made all changes.i have made new table and insert values properly.but...getting the same error in oracle 8i:
ORA-00936: missing expression
dont know why

  1  SELECT ALL   (COUNT (att_mode))
  2  / (SELECT COUNT (DISTINCT attendance_date)
  3  FROM st
  4  WHERE TO_CHAR (attendance_date, 'MON') = 'jul')* 100 result,
  5  regestration_no
  6  FROM st
  7  WHERE (att_mode = 'present')
  8  AND (TO_CHAR (attendance_date, 'MON') = 'jul')
  9* GROUP BY regestration_no
 10  ;

[CODE/ (SELECT COUNT (DISTINCT attendance_date)
   *
ERROR at line 2:
ORA-00936: missing expression[/COLOR][/CODE]

[Updated on: Wed, 05 September 2007 23:26]

Report message to a moderator

Re: strange problem with subquery in select statment [message #265269 is a reply to message #265192] Wed, 05 September 2007 23:28 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i m still getting this error:
ORA-00936: missing expression
Re: strange problem with subquery in select statment [message #265308 is a reply to message #265269] Thu, 06 September 2007 02:01 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could it be that Oracle 8i doesn't support such a syntax?

My previous post was based on Oracle 10g and, as you've seen, query doesn't return an error.

Could someone try it on Oracle 8i (the version Sams is using)?
Re: strange problem with subquery in select statment [message #265472 is a reply to message #265308] Thu, 06 September 2007 07:08 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
Could someone try it on Oracle 8i ?

plz
thanks
Re: strange problem with subquery in select statment [message #265997 is a reply to message #265472] Sat, 08 September 2007 14:29 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
please tell me why getting this error in oracle8i.
Re: strange problem with subquery in select statment [message #266539 is a reply to message #265997] Tue, 11 September 2007 01:58 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
This works for me in Oracle 9 but not in Oracle 7.

I think you will have to move to a higher level of database if you wish to use this 'select' structure.

Have you considered using a function to give you the answer that you need?

David
Re: strange problem with subquery in select statment [message #266582 is a reply to message #266539] Tue, 11 September 2007 03:09 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
yes.
Re: strange problem with subquery in select statment [message #266885 is a reply to message #266539] Tue, 11 September 2007 22:39 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i have found its solution in oracle 8i.now i m getting output according to my requirment.

what i have done is that i have declared a global variable and store the result of second "select" statment in it.Then i divide first "select" statment with that global variable (instead of dividing by second select statment).now i m getting correct output in oracle8i.

but now i have to use this code in report builder6i to produce a report.but there global variable is not acceptable.report builder6i is not accepting global variable.

now plz tell me any solution if any idea!
thnks
Re: strange problem with subquery in select statment [message #266931 is a reply to message #266885] Wed, 12 September 2007 01:00 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is what you might try to do:
  • create a formula column; SELECT this value into a locally declared variable and use it in another SELECT and return this value as a result.
  • Create a package and declare a variable in there. Set its value in one of PL/SQL units avilable (either create your own function/procedure or use one of report's triggers if possible).
Re: strange problem with subquery in select statment [message #266963 is a reply to message #266931] Wed, 12 September 2007 02:15 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i have made a function in program units (in report builder6i)

FUNCTION SUB (lectures out number)RETURN NUMBER IS
BEGIN
select count(distinct st.attendance_date) into lectures
from student_attendance st
where st.attendance_date between '25-jul-2007' and '30-jul-2007';
return lectures;
end;


how to call this function it in SQL query(in report builder6i).

SELECT ALL COUNT(ST.ATTENDANCE_MODE)/ (HERE I WANT TO CALL FUNCTION) *100,
  ST.REGESTRATION_NO
  FROM STUDENT_ATTENDANCE ST
  WHERE (ST.ATTENDANCE_MODE = 'present'

  AND ST.ATTENDANCE_DATE  BETWEEN '25-JUL-2007' AND '30-JUL-2007')
   GROUP BY ST.REGESTRATION_NO


plz tell how to do it.
reply
thnks

[Updated on: Wed, 12 September 2007 02:16]

Report message to a moderator

Re: strange problem with subquery in select statment [message #266978 is a reply to message #266963] Wed, 12 September 2007 02:51 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
'lectures' is user-defined function.

SYSDATE is built-in Oracle function.

How do you call SYSDATE in a SELECT statement? Like this:
SELECT SYSDATE FROM dual;
You'd use your functions the same way - just put its name into the SELECT column list.
SQL> create or replace function lectures return number
  2  is
  3  begin
  4    return (1000);
  5  end;
  6  /

Function created.

SQL> select 1000 / lectures from dual;

1000/LECTURES
-------------
            1

SQL>
Re: strange problem with subquery in select statment [message #266984 is a reply to message #266978] Wed, 12 September 2007 03:22 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
thnks it is running correctly in oracle8i.But i want to run it in report builder6i(SQL query).It will not run there.

Kindly tell me how to run it there.i have made a
function:

FUNCTION SUB RETURN NUMBER IS
var_1 number;
BEGIN
select count(distinct st.attendance_date) into var_1
from student_attendance st
where st.attendance_date between '25-jul-2007' and '30-jul-2007';
return var_1;
end;



It is running correctly in oracle8i but how to run it in report builder6i.how to call it in any query writteen in report builder6i.

please reply
thnks

[Updated on: Wed, 12 September 2007 03:24]

Report message to a moderator

Re: strange problem with subquery in select statment [message #266992 is a reply to message #266984] Wed, 12 September 2007 03:40 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try a FORMULA COLUMN?

Create a query.
In its default group, create a formula column which would look like
  l_first_result number;
  l_second_result number;
begin
  select count(*) 
    into l_first_result
    from ...
    where ...

  select something / l_first_result
    into l_second_result
    from ...
    where ...

  return (l_second_result);
end;

Create a field in Paper Layout; let it's source to be this newly created formula column.
Re: strange problem with subquery in select statment [message #267359 is a reply to message #266992] Thu, 13 September 2007 01:52 Go to previous messageGo to next message
alijeyan
Messages: 180
Registered: January 2007
Location: IRAN
Senior Member
Hi ALL

I test in ORACle 8 and Oracle 9i .

in Oracle 8 error ocourd and in Oacle9i work .

i have this problem and change My DATABASE Version !!!!

ALI JEYAN

Re: strange problem with subquery in select statment [message #267783 is a reply to message #267359] Fri, 14 September 2007 22:50 Go to previous message
sams
Messages: 100
Registered: August 2007
Senior Member
thnks everybody i have solved my problem.
Previous Topic: problem during saving the form
Next Topic: how to eliminate unwanted mesages
Goto Forum:
  


Current Time: Sun Dec 04 18:57:49 CST 2016

Total time taken to generate the page: 0.08440 seconds