Msg 7346, Level 16, State 2

Question

Msg 7346, Level 16, State 2

samiam914

SSCommitted

Points: 1886
More actions
July 21, 2008 at 5:23 pm

#188463

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.
Thnx
Sam

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

"ORA-01403: no data found" means exactly that but you are missing a couple of errors that usually come along.

I would log into the target Oracle database via SQL*Plus, execute the same query and check what happens... hopefully you are gonna get ORA-01403 alongside with a couple of other error messages, those are the ones that would point you in the right direction.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.