I am working on a report and have the following Issue
SCR table has CRS_NB(course number),CRS_VRSN_NB(course version number),STDNT_NB(student number),SCR_DT(score date) and I want to get the count of students for course for year 2002 year 2003 and year 2003 and having problems can any one help mw with the Issue.Create d the universe and cghecked the Integerity.
This is the expected result
Couse name nb vrsn nb year count
MAPS(1): Introduction to MAPS-W98 3 1 2000 16
MAPS(1): Introduction to MAPS-W98 3 1 2001 156
MAPS(1): Introduction to MAPS-W98 3 1 2002 101
But when I do the report
I get
Maps(1) Introduction… 9 3 6003 273
It’s adding all the records.
Queries from Report
SELECT B.CRS_NM,A.crs_nb,A.crs_vrsn_nb,Year(A.scr_Dt),Count(A.STDNT_NB) AS CountOfSTDNT_NB
FROM ZLDT180_SCR AS A ,ZLDT030_CRS AS B
WHERE a.crs_nb =b.crs_nb and a.CRS_VRSN_NB =b.crs_vrsn_nb
GROUP BY A.CRS_NB, A.CRS_VRSN_NB,B.CRS_NM,Year(A.scr_Dt) order by A.crs_nb,A.CRS_VRSN_NB,Year(A.scr_Dt);
Created report by selecting Free hand SQL and when i vIEW THE DATE i GET RIGHT BUT WHEN I RUN THE REPORT i GET WRONG.
MAPS(1): Introduction to MAPS-W98 3 1 2000 16
MAPS(1): Introduction to MAPS-W98 3 1 2001 156
MAPS(1): Introduction to MAPS-W98 3 1 2002 101
But when I do the report
I get
Maps(1) Introduction… 9 3 6003 273
It’s adding all the records.
Queries from Report
SELECT B.CRS_NM,A.crs_nb,A.crs_vrsn_nb,Year(A.scr_Dt),Count(A.STDNT_NB) AS CountOfSTDNT_NB
FROM ZLDT180_SCR AS A ,ZLDT030_CRS AS B
WHERE a.crs_nb =b.crs_nb and a.CRS_VRSN_NB =b.crs_vrsn_nb
GROUP BY A.CRS_NB, A.CRS_VRSN_NB,B.CRS_NM,Year(A.scr_Dt) order by A.crs_nb,A.CRS_VRSN_NB,Year(A.scr_Dt);
Created report by selecting Free hand SQL and when i vIEW THE DATE i GET RIGHT BUT WHEN I RUN THE REPORT i GET WRONG.
count(dbo.ZLDT180_SCR.STDNT_NB)
where condition
(dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= .85 OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= .85) AND (year(dbo.ZLDT180_SCR.SCR_DT) =2002)
and
Scores2003 as
count(dbo.ZLDT180_SCR.STDNT_NB)
where condition
(dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= .85 OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= .85) AND (year(dbo.ZLDT180_SCR.SCR_DT) =2003)
but cannot display data when I pull in scores2002 and scores2003 and get a message No data to fetch…
SQL
SELECT
ZLDT030_CRS.CRS_NM,
ZLDT030_CRS.CRS_NB,
ZLDT030_CRS.CRS_VRSN_NB,
count(ZLDT180_SCR.STDNT_NB),
count(ZLDT180_SCR.STDNT_NB)
FROM
ZLDT030_CRS,
ZLDT180_SCR
WHERE
( ZLDT030_CRS.CRS_NB=ZLDT180_SCR.CRS_NB and ZLDT030_CRS.CRS_VRSN_NB=ZLDT180_SCR.CRS_VRSN_NB )
AND ( ZLDT030_CRS.CRS_DLVRY_CD IN (1,2,4) )
AND ( (ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= .85 OR ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= .85) AND (year(ZLDT180_SCR.SCR_DT) =2002) )
AND ( (ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= .85 OR ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= .85) AND (year(ZLDT180_SCR.SCR_DT) =2003) )
GROUP BY
ZLDT030_CRS.CRS_NM,
ZLDT030_CRS.CRS_NB,
ZLDT030_CRS.CRS_VRSN_NB
Went to Data Provider ans sql and changed it but cannot save and when I run the query it changes back to AND.Is there a different way to deal with this…Please let me know…
count(dbo.ZLDT180_SCR.STDNT_NB)
where
(dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= .85 OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= .85) AND (year(dbo.ZLDT180_SCR.SCR_DT) =2002)
scores2003
count(dbo.ZLDT180_SCR.STDNT_NB)
where
(dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= .85 OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= .85) AND (year(dbo.ZLDT180_SCR.SCR_DT) =2003)
and pulled them to results objects…
Ia m doing something wrong here…Please help…
SELECT
dbo.ZLDT030_CRS.CRS_NM,
dbo.ZLDT030_CRS.CRS_NB,
dbo.ZLDT030_CRS.CRS_VRSN_NB,
count(dbo.ZLDT180_SCR.STDNT_NB),
count(dbo.ZLDT180_SCR.STDNT_NB)
FROM
dbo.ZLDT030_CRS,
dbo.ZLDT180_SCR
WHERE
( dbo.ZLDT030_CRS.CRS_NB=dbo.ZLDT180_SCR.CRS_NB and dbo.ZLDT030_CRS.CRS_VRSN_NB=dbo.ZLDT180_SCR.CRS_VRSN_NB )
AND ( dbo.ZLDT030_CRS.CRS_DLVRY_CD IN (1,2,4) )
AND ( (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= .85 OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= .85) AND (year(dbo.ZLDT180_SCR.SCR_DT) =2002) )
AND ( (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= .85 OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= .85) AND (year(dbo.ZLDT180_SCR.SCR_DT) =2003) )
GROUP BY
dbo.ZLDT030_CRS.CRS_NM,
dbo.ZLDT030_CRS.CRS_NB,
dbo.ZLDT030_CRS.CRS_VRSN_NB
You are making count(dbo.ZLDT180_SCR.STDNT_NB) a dimension.
This should be a measure.
You are having where clause set in the object definition.
Don’t do that. Instead add conditions in the conditions block in query panel while creating the report.
Make year(dbo.ZLDT180_SCR.SCR_DT) a dimension and use in condition block of query panel.
Select year(dbo.ZLDT180_SCR.SCR_DT) in your report also.
Your final SQL should look like this
SELECT
dbo.ZLDT030_CRS.CRS_NM,
dbo.ZLDT030_CRS.CRS_NB,
dbo.ZLDT030_CRS.CRS_VRSN_NB,
year(dbo.ZLDT180_SCR.SCR_DT),
count(dbo.ZLDT180_SCR.STDNT_NB),
count(dbo.ZLDT180_SCR.STDNT_NB)
FROM
dbo.ZLDT030_CRS,
dbo.ZLDT180_SCR
WHERE
( dbo.ZLDT030_CRS.CRS_NB=dbo.ZLDT180_SCR.CRS_NB and dbo.ZLDT030_CRS.CRS_VRSN_NB=dbo.ZLDT180_SCR.CRS_VRSN_NB )
AND ( dbo.ZLDT030_CRS.CRS_DLVRY_CD IN (1,2,4) )
AND ( year(dbo.ZLDT180_SCR.SCR_DT) in (2002,2003))
GROUP BY
dbo.ZLDT030_CRS.CRS_NM,
dbo.ZLDT030_CRS.CRS_NB,
dbo.ZLDT030_CRS.CRS_VRSN_NB
SELECT
dbo.ZLDT030_CRS.CRS_NM,
dbo.ZLDT030_CRS.CRS_NB,
dbo.ZLDT030_CRS.CRS_VRSN_NB,
year(dbo.ZLDT180_SCR.SCR_DT),
count(dbo.ZLDT180_SCR.STDNT_NB),
count(dbo.ZLDT180_SCR.STDNT_NB),
count(dbo.ZLDT180_SCR.STDNT_NB)
FROM
dbo.ZLDT030_CRS,
dbo.ZLDT180_SCR
WHERE
( dbo.ZLDT030_CRS.CRS_NB=dbo.ZLDT180_SCR.CRS_NB and dbo.ZLDT030_CRS.CRS_VRSN_NB=dbo.ZLDT180_SCR.CRS_VRSN_NB )
AND ( dbo.ZLDT030_CRS.CRS_DLVRY_CD IN (1,2,4) )
AND (
year(dbo.ZLDT180_SCR.SCR_DT) IN (2001, 2002)
AND dbo.ZLDT030_CRS.CRS_NB IN (3, 4, 5) AND (dbo.ZLDT030_CRS.CRS_DLVRY_CD IN (1,2,4))
)
GROUP BY
dbo.ZLDT030_CRS.CRS_NM,
dbo.ZLDT030_CRS.CRS_NB,
dbo.ZLDT030_CRS.CRS_VRSN_NB,
year(dbo.ZLDT180_SCR.SCR_DT)
and get
“Columns of the SELECT clause are different from the result object (QP0017)”
[Edited, used bbc to format SQL as Code, please try to format your posts so they are easier to read - Andreas]
This error occurs if you modify and BO generated SQL manually and number of objects in the Select statement of your modified Query differs from the the number of objects you originally selected in the query panel.