Home » SQL & PL/SQL » SQL & PL/SQL » no output
no output [message #218121] Tue, 06 February 2007 22:31 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Morning All
I have a simple select query which gives the desired output.
But when I format the query as matrix there is no data at the intersection...

both queries are as given below..
plz help me ...


Query with output

  1  select substr(reg_no,1,4) as class,
  2  round((sum(gradenumericvalue))/(count(substr(reg_no,1,4))),2) as avrg,src.tempcolfac as fac
  3  from src,grades_rt
  4  where src.gradecharvalue=grades_rt.gradecharvalue
  5  and semester_code=3 and semester_year=2005
  6  and src.gradecharvalue not in('W','I')
  7  group by substr(reg_no,1,4),src.tempcolfac
  8* order by 1 desc
EX > /

CLAS       AVRG FAC
---- ---------- ------
2005       2.34 CS
2005       2.19 CSW
2005       2.94 EE
2005       2.49 ES
2005       2.64 ME
2005       2.16 MM
2004       2.74 CS
2004       2.74 CSW
2004       3.15 EE
2004       2.45 ES
2004       2.79 ME
2004       2.78 MM
2003       2.85 CS
2003       2.98 CSW
2003       3.29 EE
2003       3.02 ES
2003       2.59 ME
2003       2.82 MM
2002       2.66 CS
2002       3.26 EE
2002       3.15 ES
2002       2.72 ME
2002       2.83 MM
2001       2.84 CS
2001       1.93 EE
2001       1.62 ES
2001       2.22 ME
2001       1.38 MM
2000       1.54 CS
2000       1.17 EE
2000          0 ES
2000        1.5 ME

32 rows selected.

EX > 



The same query with no output
select class,
sum(decode(fac,'CS',avrg)) as cs,
sum(decode(fac,'CSW',avrg)) as csw,
sum(decode(fac,'EE',avrg)) as ee,
sum(decode(fac,'ME',avrg)) as me,
sum(decode(fac,'MM',avrg)) as mm,
sum(decode(fac,'ES',avrg)) as es
 from
(
select substr(reg_no,1,4) as class,
round((sum(gradenumericvalue))/(count(substr(reg_no,1,4))),2) as avrg,src.tempcolfac as fac
from src,grades_rt
where src.gradecharvalue=grades_rt.gradecharvalue
and semester_code=3 and semester_year=2005
and src.gradecharvalue not in('W','I')
group by substr(reg_no,1,4),src.tempcolfac
order by 1 desc
)
group by class
/


EX > /

CLAS         CS        CSW         EE         ME         MM         ES
---- ---------- ---------- ---------- ---------- ---------- ----------
2000
2001
2002
2003
2004
2005

6 rows selected.





Rzkhan
Re: no output [message #218164 is a reply to message #218121] Wed, 07 February 2007 01:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Check for trailing spaces on src.tempcolfac. Is it a CHAR or a VARCHAR2?

Try:

sum(decode(TRIM(fac),'CS',avrg)) as cs,


Ross Leishman
Re: no output [message #218166 is a reply to message #218164] Wed, 07 February 2007 01:14 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thank you Ross Leishman

Now it works fine....

RzKhan
Previous Topic: Getting Error when using to_char function
Next Topic: Getting error in the package body execution
Goto Forum:
  


Current Time: Sat Dec 03 18:11:45 CST 2016

Total time taken to generate the page: 0.23719 seconds