Question Problem with Crystal Report Adapter Query

Adijo

New member
Joined
Jan 7, 2009
Messages
1
Programming Experience
Beginner
Hey guys,

I'm using Oracle 10 XE along with VB .NET 2003.
I'm trying to generate a dynamic report for my project.

I have created an OracleDataAdapter with the query :

SELECT
TRUNC(A.FDATE) AS EXPR1,
T.TEACHER_NAME,
S.SUBJECT_NAME,
Q.QUESTION_ID,
Q.QUESTION_DESC,
Q.OPT1,
Q.OPT2,
Q.OPT3,
Q.OPT4,
COUNT(A.OPT1) AS EXPR2,
COUNT(A.OPT2) AS EXPR3,
COUNT(A.OPT3) AS EXPR4,
COUNT(A.OPT4) AS EXPR5
FROM
FEEDBACK F,
TEACHER T,
ANSWERS A,
QUESTIONS Q,
SUBJECTS S
WHERE
F.TEACHER_ID = T.TEACHER_ID AND
F.FEEDBACK_ID = A.FEEDBACK_ID AND
A.QUESTION_ID = Q.QUESTION_ID AND
F.SUBJECT_ID = S.SUBJECT_ID
GROUP BY
T.TEACHER_NAME,
S.SUBJECT_NAME,
Q.QUESTION_ID,
Q.QUESTION_DESC,
Q.OPT1,
Q.OPT2,
Q.OPT3,
Q.OPT4,
TRUNC(A.FDATE)

The select command was generated successfully.

When I tried to generate the report using this adapter, the "EXPR" fields [ trunc(a.fdate),count(a.opt1),count(a.opt2),count(a.opt3),count(a.opt4) ] are blank when I run the report :(

The query that I used in the CrystalReport is :

select
trunc(a.fdate),
t.teacher_name,
s.subject_name,
q.question_id,
q.question_desc,
q.opt1,
q.opt2,
q.opt3,
q.opt4,
count(a.opt1),
count(a.opt2),
count(a.opt3),
count(a.opt4)
from
feedback f,
answers a,
teacher t,
subjects s,
questions q
where
A.FEEDBACK_ID = '" & fid & "' AND
upper(trunc(fdate)) like upper('" & dte & "') and
a.question_id=q.question_id and
t.teacher_id=f.teacher_id and
f.feedback_id=a.feedback_id and
f.subject_id=s.subject_id
group by
t.teacher_name,
s.subject_name,
q.question_id,
q.question_desc,
q.opt1,
q.opt2,
q.opt3,
q.opt4,
trunc(a.fdate)
order by
q.question_id

This is a project based on Feedback Forms, and I have to summarise the feedbacks given to the teachers.

The values "fid" and "dte" are coming fine from the other form.

I've used the trunc() function, as I was not getting the desired output if I didn't use it.

Thanks a lot!

Regards,

Adijo.
 
Last edited:
Top Bottom