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

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  August 1, 2008 at 6:07 am

  #851808

  "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.