|
|
|
SSC Rookie
 
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 6:01 PM
Points: 38,
Visits: 239
|
|
When I run the query with "group by" I get the error as noted below.
If I remove the "group by" and "COUNT(Soe.AssmtGrades.Id) AS CountLimited" from the Select clause it works fine.
OR
If I remove the first four fields in the "group by" clause, it works fine too.
Please note:
I am using OPENQUERY with the linked server DPROD10, and joining the oracle results with sql server tables.
I tried with OPENROWSET, same problem occurs.
SELECT
Soe.ProgramsCourses.[Course Number]
,Soe.AssmtIndex.AssmtCode
,Soe.AssmtDescr.AssmtDescr
,Soe.AssmtIndex.CFStdCode
,COUNT(Soe.AssmtGrades.Id) AS CountLimited
,Soe.ProgramsCourses.[Major/Minor]
,Soe.ProgramsCourses.Award
,coll.MAJORS.MAJ_DESC
,coll.TERMS.TERM_REPORTING_YEAR
FROM Soe.AssmtGrades
INNER JOIN Soe.AssmtIndex ON Soe.AssmtGrades.CrsName = Soe.AssmtIndex.CrsName
AND Soe.AssmtGrades.IndexNo = Soe.AssmtIndex.IndexNo
AND Soe.AssmtGrades.GradeAlpha IN ('C+', 'B-')
INNER JOIN Soe.AssmtDescr ON Soe.AssmtIndex.AssmtCode = Soe.AssmtDescr.AssmtCode
INNER JOIN Soe.ProgramsCourses ON Soe.AssmtGrades.CrsName = Soe.ProgramsCourses.[Course Number]
AND Soe.ProgramsCourses.[Major/Minor] = 'E101' AND Soe.ProgramsCourses.Award = 'MAT'
INNER JOIN coll.MAJORS ON Soe.ProgramsCourses.[Major/Minor] = coll.MAJORS.MAJORS_ID
INNER JOIN coll.TERMS ON Soe.AssmtGrades.Term = coll.TERMS.TERMS_ID AND coll.TERMS.TERM_REPORTING_YEAR = 2007
INNER JOIN OPENQUERY(DPROD10,
'select spgm.student_programs_id
,substr(spgm.student_programs_id,1,7) student_id
,apgm.acpg_acad_level
,apgmls.acpg_majors
,amaj.stpr_addnl_majors
,apgmls.acpg_ccds
,CASE
WHEN apgm.acpg_degree IS NULL THEN
apgmls.acpg_ccds
WHEN apgm.acpg_degree = '' '' THEN
apgmls.acpg_ccds
ELSE
apgm.acpg_degree
END award
,stprdt.stpr_end_date award_date
,apgm.acpg_degree
,stprdt.stpr_start_date matric_date
,sal.sta_class
,CASE
WHEN acpg_acad_level=''UG'' THEN
stpr_addnl_majors
WHEN acpg_acad_level=''GR'' THEN
acpg_majors
ELSE
''error''
END ed_major
,CASE
WHEN acpg_acad_level=''UG'' THEN
acpg_majors
WHEN acpg_acad_level=''GR'' THEN
'' ''
ELSE
''error''
END la_major
from coll_production.student_programs spgm
,coll_production.acad_programs apgm
,coll_production.acad_programs_ls apgmls
,coll_production.student_acad_levels sal
-- ,coll_production.student_advisement sadv
,coll_production.stpr_dates stprdt
,coll_production.stpr_major_list amaj
,coll_production.stpr_minor_list amin
Where substr(spgm.student_programs_id,INSTR(spgm.student_programs_id,''*'',1,1)+1) = apgm.acad_programs_id
and concat(concat(substr(spgm.student_programs_id,1,7),''*''),apgm.acpg_acad_level) = sal.student_acad_levels_id
and substr(spgm.student_programs_id,INSTR(spgm.student_programs_id,''*'',1,1)+1) = apgmls.acad_programs_id (+)
and (apgmls.pos=1 or apgmls.pos is null)
and spgm.student_programs_id = stprdt.student_programs_id (+)
and (stprdt.pos=1 or stprdt.pos is null)
and spgm.student_programs_id = amaj.student_programs_id (+)
and (amaj.pos=1 or amaj.pos is null)
and spgm.student_programs_id = amin.student_programs_id (+)
and (amin.pos=1 or amin.pos is null)
and apgmls.acpg_majors = ''E101''
and (apgmls.acpg_ccds = ''MAT'' or apgm.acpg_degree = ''MAT'')
order by 1') o ON o.student_id = Soe.AssmtGrades.Id
GROUP BY
Soe.ProgramsCourses.[Course Number]
,Soe.AssmtIndex.AssmtCode
,Soe.AssmtDescr.AssmtDescr
,Soe.AssmtIndex.CFStdCode
,Soe.ProgramsCourses.[Major/Minor]
,Soe.ProgramsCourses.Award
,coll.MAJORS.MAJ_DESC
,coll.TERMS.TERM_REPORTING_YEAR
OLE DB provider "OraOLEDB.Oracle" for linked server "DPROD10" returned message "ORA-01403: no data found".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "DPROD10".
Any help is appreciated (note, I posted this under working with Oracle also, but not many hits there).
Thnx
Sam
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 8,689,
Visits: 4,957
|
|
Load the data from the Oracle database into a temp table (the same stuff you're querying here, of course), then join to that temp table instead. See if that solves the issue.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 6:01 PM
Points: 38,
Visits: 239
|
|
| Thnx for the reply. I have been loading into a temp table and joining with sql server tables, to get around the problem, that works fine. I would prefer not to do that. Probably there is a bug in the driver "Oracle Provider for OLE DB" some where. I also tried with CTE, same problem.
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 8,689,
Visits: 4,957
|
|
Wouldn't know about a bug in the driver. I use temp tables for that kind of thing. It's not like I'm expecting SQL Server to manage transactions in that situation.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
