Home » Archived » BIRT » Adding column to Report(SQL help)
| Adding column to Report [message #1032835] |
Wed, 03 April 2013 13:49 |
 Greg Wacker
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
|
|
|
Goto Forum:
Current Time: Fri Jun 09 19:07:14 GMT 2023
Powered by FUDForum. Page generated in 0.02046 seconds |
] 
Search
Help
Register
Login
Home
