Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Adding column to Report(SQL help)
Adding column to Report [message #1032835] Wed, 03 April 2013 13:49
Greg Wacker is currently offline Greg WackerFriend
Messages: 15
Registered: March 2013
Junior Member
Hi-
Any help as to why Column I am adding will not work with this SQL w/b appreciated:

Column is ORG_ID(Underlined)-

select v1.USER_VALUE, v1.org_id, v1.dept_id, v1.dept_name,
sum(v1.CNT_NEEDS), sum(v1.CNT_CMPL)
from (select pu.USER_VALUE, s.org_id, pu1.USER_VALUE as dept_id,
pu2.USER_VALUE as dept_name,
count(s.STUD_ID) as CNT_NEEDS, 0 as CNT_CMPL
from PA_QUAL q, PA_STUDENT s, PA_STUD_USER pu, PA_STUD_USER pu1, PA_STUD_USER pu2,
PA_STUD_QUAL sq
where q.QUAL_ID(+) = sq.QUAL_ID
and s.STUD_ID = sq.STUD_ID
and s.STUD_ID = pu.STUD_ID
and s.STUD_ID = pu1.STUD_ID
and s.STUD_ID = pu2.STUD_ID
and pkg_student.get_stud_qual_status(sq.stud_id, sq.qual_id, sq.qual_id)='N'
and s.REGION_ID = ?
and q.QUAL_ID = ?
and pu.COL_NUM = 30
and pu1.COL_NUM = 40
and pu2.COL_NUM = 50

and s.notactive = 'N'
/** and [security:pa_student s]*/
group by pu.USER_VALUE, s.org_id, pu1.USER_VALUE, pu2.USER_VALUE,
q.QUAL_ID, q.QUAL_TITLE

UNION ALL

select pu.USER_VALUE, v1.org_id, pu1.USER_VALUE as dept_id, pu2.USER_VALUE as dept_name,
0 as CNT_NEEDS, count(s.STUD_ID) as CNT_CMPL
from PA_QUAL q, PA_STUDENT s, PA_STUD_USER pu, PA_STUD_USER pu1, PA_STUD_USER pu2,
PA_STUD_QUAL sq
where q.QUAL_ID(+) = sq.QUAL_ID
and s.STUD_ID = sq.STUD_ID
and s.STUD_ID = pu.STUD_ID
and s.STUD_ID = pu1.STUD_ID
and s.STUD_ID = pu2.STUD_ID
and pkg_student.get_stud_qual_status(sq.stud_id, sq.qual_id, sq.qual_id)='Y'
and s.REGION_ID = ?
and q.QUAL_ID = ?
and pu.COL_NUM = 30
and pu1.COL_NUM = 40
and pu2.COL_NUM = 50

and s.notactive = 'N'
/** and [security:pa_student s]*/
group by pu.USER_VALUE, s.org_id, pu1.USER_VALUE, pu2.USER_VALUE,
q.QUAL_ID, q.QUAL_TITLE
) v1
group by v1.USER_VALUE, v1.org_id, v1.dept_id, v1.dept_name
order by v1.USER_VALUE, v1.org_id, v1.dept_id, v1.dept_name


Table Layout is attached File
Previous Topic:Get BIRT reports through the web service BIRT
Next Topic:Building BIRT 4.2.x
Goto Forum:
  


Current Time: Tue Dec 23 02:22:45 GMT 2014

Powered by FUDForum. Page generated in 0.07009 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software