| logic Required [message #612874] |
Sat, 26 April 2014 13:58  |
 |
jason346
Messages: 4
Registered: April 2014
Location: india
|
Junior Member |
|
|
The below table is a student table, I am unable to bring the value GPA = 9.0(3rd Row) where the data criteria should be match for second condition(in case statement).
It need to check the student whose GPA should meet the range were the students are screened, if this condition not qualify, then it need to check the next statement where student were PASSED with that GPA range value with stu_join_date should be less then or equal to '02-APR-2014'. I am trying to frame the logic with the simple DML query, but unable to get the resultset which is GPA= 9.0 single row record to be displayed ... any suggestion pls.. like this i have million of records with the same logic to be brough out.
SELECT DISTINCT
studentid,
stud_name,
stu_join_date,
exam_type,
gpa,
case
When GPA between 9.0 and 10.0 and exam_type ='SCREEN'
then GPA
When GPA between 9.0 and 10.0 and exam_type ='PASSED'
and stu_join_date <= '02-APR-2014'
then GPA
else
''
FROM student
Regards
Jason
[Updated on: Sat, 26 April 2014 13:59] Report message to a moderator |
|
|
|
|
|
|
|
| Re: logic Required [message #612884 is a reply to message #612874] |
Sat, 26 April 2014 14:50  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
SELECT DISTINCT studentid,
stud_name,
stu_join_date,
exam_type,
/* gpa */
CASE
WHEN gpa BETWEEN 9.0 AND 10.0
AND exam_type = 'SCREEN' THEN gpa
WHEN gpa BETWEEN 9.0 AND 10.0
AND exam_type = 'PASSED'
AND stu_join_date <= to_date('02/04/2014','dd/mm/yyyy') THEN gpa
ELSE NULL
END gpa
FROM student
Is this what you want to display?
If you want to filter out the rows then you have to specify the filter condition with WHERE condition not SELECT.
However, the data provided is appears in the table to be logically incorrect.
Request you to provide working test case as already suggested.
ED: corrected date comparison as it was overlooked
[Updated on: Sat, 26 April 2014 15:10] Report message to a moderator |
|
|
|
| Re: logic Required [message #612965 is a reply to message #612884] |
Mon, 28 April 2014 06:23 |
|
jason346
Messages: 4
Registered: April 2014
Location: india
|
Junior Member |
|
|
Here is the test case with table and insert values.
create table students (
name varchar2(25 BYTE),
joined_date DATE,
exam_type VARCHAR2(25 BYTE),
SCORE NUMBER(2,1),
CUT_OFF_DATE DATE
);
insert into students values('john','26-mar-14','SCREEN',7.6,'10-apr-14');
insert into students values('john','10-Apr-14','RETEST',8.0,'10-apr-14');
I am trying to return the unique record with the score of that student name. I use CASE, but it really do not act as 'IF-THEN-else' like using a control statement.
select name,
joined_date,
case
---something like IF statment if below[b] statement 1[/b] --is true then it
--should return below statement
when exam_type ='SCREEN' and score between 8.0 and 10.0
then score
----[b]statement 2[/b] --if above expr fails, the below to be executed
when exam_type ='RETEST' and score between 8.0 and 10.0
and joined_date >= cut_off_date
then score
else null
end result
from students;
output
in the above result set, I need only the score '8' and 'John' as a unique records only? like this there are more then millions of records to be computed.
hope this clear.
i am not good in PLSQL.
Thanks
jason
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
