Home » Developer & Programmer » Reports & Discoverer » RANK sql doesnt work Properly (report builder 6i, window 7)
RANK sql doesnt work Properly [message #606946] Fri, 31 January 2014 01:10 Go to next message
kilimanjaro
Messages: 151
Registered: May 2009
Location: Tanzania
Senior Member
Hi Friends,

I'm facing an awkwardly problem .I'm trying to get student position in relation to subject.I wrote an SQL using both
Rank and Dense rank and everything worked fine in SQL PLus--I got what I wanted. The problem is, the same query doesn't give me the same results in Report builder .Below is the query.The student number is the data link.

SELECT DISTINCT
       STUDENTS_number,
       SUBJECT_NAME,
       EXAMINATION_DATE,
       :START_DATE,
       :END_DATE class_name,
       MAX_ARCH,
       SUBJECT_TEACHER,
       total_seated_candidates,
       notes,
       DENSE_RANK ()
          OVER (PARTITION BY class_name, SUBJECT_NAME ORDER BY MAX_ARCH DESC)
          RANK,
       DENSE_RANK ()
       OVER (PARTITION BY class_level, SUBJECT_NAME ORDER BY MAX_ARCH DESC)
          RANK
  FROM dmss_STUDENTS_REPORT
 WHERE     EXAMINATION_DATE BETWEEN :START_DATE AND :END_DATE
       AND EXAMINATION_DATE >= :START_DATE
       AND EXAMINATION_DAte <= :END_DATE
       AND EXAMINATION_TYPE = :EXAMINATION_TYPE
       AND STUDENTS_NUMBER = :STUDENT_NAME



[EDITED by LF: formatted code and applied [code] tags]

[Updated on: Fri, 31 January 2014 02:48] by Moderator

Report message to a moderator

Re: RANK sql doesnt work Properly [message #606950 is a reply to message #606946] Fri, 31 January 2014 02:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

We can't really help unless you tell us what the report is doing that is wrong.
Re: RANK sql doesnt work Properly [message #606951 is a reply to message #606950] Fri, 31 January 2014 02:49 Go to previous messageGo to next message
kilimanjaro
Messages: 151
Registered: May 2009
Location: Tanzania
Senior Member
OK.

Let me clarify.After removing the parameters ,I run the code in SQL plus and it gives me the result I want;Student position based on the subject. EG 1,2,4 to 34(the number of students set for the exam of that particular subject).

unfortunately,When I run the same query I posted in ,It sets the student position to Number 1 even if the student is number ten or fifteen position of the subject.That is my problem..I want it to reflect the reality.
Re: RANK sql doesnt work Properly [message #606952 is a reply to message #606950] Fri, 31 January 2014 02:52 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you sure that this code WORKS in Reports 6i? I believe that analytic functions weren't even invented when Developer 6i was released.

Consider creating a view which would use DENSE_RANK, and then write a query (in your report) which reads that view. Doing so, you'd avoid analytic function calls in Reports.
Previous Topic: How to implement Bar code in Oracle Apps Report
Next Topic: report 11g buge(hangs)
Goto Forum:
  


Current Time: Thu Mar 28 18:00:14 CDT 2024