T-SQL 2000 to T-SQL 2008

Hello!

We are updating the database platform from SQL 2000 to SQL 2008. I have problems with Query's that are not compatible and I am not an expert but I really want to learn. Following the example of the Upgrade Advisor interface, make this change:

T-SQL 2000:

select tUseRemi = (SELECT IsNull(rb.UOrgTSigla, IsNull(rb.UOrgTDescrip, '')) FROM oeimUnidOrg rb WHERE rb.UOrgCCod =* Movimiento.cUOgRCode)
FROM Documento, Movimiento

I try this: T-SQL2008

 select tUseRemi = (SELECT IsNull(rb.UOrgTSigla, IsNull(rb.UOrgTDescrip, '')) FROM oeimUnidOrg rb right join Movimiento ON
 rb.UOrgCCod = Movimiento.cUOgRCode)
 FROM Documento, Movimiento

when using the SQL 2008 query that I try to get, I get this error:

Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when...


The entire query is: (but I'm trying to put it in parts to better understand the logic)


CREATE PROCEDURE [dbo].[STDW_REP01_1]
  @nExpePeri Int,
  @nExpeNumb BigInt,
  @nDocuCode Int
As
Begin


 select
tNumeExpe = Right(Convert(Varchar(4), Documento.nExpePeri),2) + '-' + Right('000000' + Convert(Varchar(6), Documento.nExpeNumb),6),
         dDocuDate = Convert(Varchar(10), dbo.TraerFechaRegistroOrigen(Documento.nExpePeri, Documento.nExpeNumb, Documento.cDocuCode),103) + ' ' +
           CASE Left(Convert(Varchar, dbo.TraerFechaRegistroOrigen(Documento.nExpePeri, Documento.nExpeNumb, Documento.cDocuCode),108), 5)
           WHEN '00:00' THEN ''
           ELSE Left(Convert(Varchar, dbo.TraerFechaRegistroOrigen(Documento.nExpePeri, Documento.nExpeNumb, Documento.cDocuCode),108),5) END,
         Documento.cTemaCode,
         sTemaDesc = (SELECT Tema.sTemaDesc FROM Tema WHERE Tema.cTemaCode = Documento.cTemaCode),
         tTipoDocu = (SELECT Tipo_Documento.sDocuDesc FROM Tipo_Documento
                       WHERE Tipo_Documento.cDocuType = Documento.cDocuType),
         nDocuNumb, sDocuAsun, cExpeAntg,
        dRegiDate = Convert(varchar(10),Movimiento.dRecpDate,103), --Convert(varchar(10),Movimiento.dRegiDate,103), 01/12/2006
         tUseOrig = dbo.TraerUsuarioOrigen(Documento.nExpePeri, Documento.nExpeNumb, Documento.cDocuCode),
         tUseRemi = (CASE Movimiento.cInsRCode
                     WHEN '0001' THEN
                       (SELECT IsNull(rb.UOrgTSigla, IsNull(rb.UOrgTDescrip, '')) FROM oeimUnidOrg rb WHERE rb.UOrgCCod =* Movimiento.cUOgRCode) + '-' +
                       (SELECT IsNull(rc.PerTDescrip,'') FROM oeimPersonal rc WHERE rc.PerCCod =* Movimiento.cUseRCode) + '-' +
                       (SELECT IsNull(rg.CrgTDescrip, '') FROM oeimCargo rg WHERE rg.CrgCCod =* Movimiento.cCrgRCode)
                     WHEN '9999' THEN
                       (SELECT IsNull(rc.PerTDescrip, '') FROM oeimPersonal rc WHERE rc.PerCCod = Movimiento.cUseRCode)
                     ELSE
                                 --(SELECT IsNull(ra.InstTDescrip, '') FROM (oeimInstitucion ra LEFT OUTER JOIN MOVIMIENTO AS M ON ra.InstCCod = M.cInsRCode)) + '-' +
                                 (SELECT IsNull(ra.InstTDescrip, '') FROM oeimInstitucion ra WHERE ra.InstCCod =* Movimiento.cInsRCode) + '-' +
                       (SELECT IsNull(rb.UOrgTSigla, IsNull(rb.UOrgTDescrip, '')) FROM oeimUnidOrg rb WHERE rb.UorgCCod =* Movimiento.cUOgRCode) + '-' +                                 
                       (SELECT IsNull(rc.PerTDescrip,'') FROM oeimPersonal rc WHERE rc.PerCCod =* Movimiento.cUseRCode)
                     END),
         tUseRemS = (SELECT SubString(IsNull(PerTNombre,''), 1, 1) + '.' + IsNull(PerTApePat, IsNull(PerTApePat,''))
                      FROM oeimPersonal WHERE PerCCod = Movimiento.cUseRCode),
         tUseDest = (CASE Movimiento.cInsDCode WHEN '0001' THEN
                       (SELECT IsNull(db.UOrgTSigla, IsNull(db.UOrgTDescrip, '')) FROM oeimUnidOrg db WHERE db.UorgCCod =* Movimiento.cUOgDCode) + '-' +
                       (SELECT IsNull(dc.PerTDescrip,'') FROM oeimPersonal dc WHERE dc.PerCCod =* Movimiento.cUseDCode) + '-' +
                       (SELECT IsNull(dg.CrgTDescrip, '') FROM oeimCargo dg WHERE dg.CrgCCod =* Movimiento.cCrgDCode)
                     WHEN '9999' THEN
                      (SELECT IsNull(dc.PerTDescrip, '')
                         FROM oeimPersonal dc
                        WHERE dc.PerCCod = Movimiento.cUseDCode)
                     ELSE
                      (SELECT IsNull(da.InstTDescrip, '') FROM oeimInstitucion da WHERE da.InstCCod =* Movimiento.cInsDCode) + '-' +
                      (SELECT IsNull(db.UOrgTSigla, IsNull(db.UOrgTDescrip, '')) FROM oeimUnidOrg db WHERE db.UorgCCod =* Movimiento.cUOgDCode) + '-' +
                      (SELECT IsNull(dc.PerTDescrip,'') FROM oeimPersonal dc WHERE dc.PerCCod =* Movimiento.cUseDCode)
                     END),
         tUseDesS = (SELECT SubString(IsNull(PerTNombre,''), 1, 1) + '.' + IsNull(PerTApePat, IsNull(PerTApePat,''))
                      FROM oeimPersonal WHERE PerCCod = Movimiento.cUseDCode),
         sUseRDesc, sUseDDesc, Movimiento.cUseRCode, Movimiento.cUseDCode,
         tIndi = dbo.Concatena_Movimiento_Indicacion(Movimiento.nExpePeri, Movimiento.nExpeNumb, Movimiento.cDocuCode, Movimiento.cMoviCode, 0, 3),
         sMoviObse = Left(IsNull(Movimiento.sMoviObse,''), 95),
         tOperOrig = dbo.TraerOperadorOrigen(Documento.nExpePeri, Documento.nExpeNumb, Documento.cDocuCode)
    FROM Documento, Movimiento
   WHERE Documento.nExpePeri = @nExpePeri
     AND Documento.nExpePeri = Movimiento.nExpePeri
     AND Documento.nExpeNumb = @nExpeNumb
     AND Documento.nExpeNumb = Movimiento.nExpeNumb
     AND Documento.cDocuCode = @nDocuCode
     AND Documento.cDocuCode = Movimiento.cDocuCode
     AND Documento.cElimFlag = 0
     AND Movimiento.cElimFlag = 0
   ORDER BY Movimiento.cMoviCode

End

GO




I hope you can help me!
JnavarroMcIT ConsultantAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
(SELECT IsNull(rb.UOrgTSigla, IsNull(rb.UOrgTDescrip, '')) FROM oeimUnidOrg rb right join Movimiento ON
 rb.UOrgCCod = Movimiento.cUOgRCode)

That type of subquery is only allowed to return 1 record.  You're getting an error because it's returning multiple records matching Movimiento.cUOgRCode.  You need to modify it to ensure it returns 1 record only - at most. For example, by applying an aggregate OR using the TOP command.

the Upgrade Advisor interface, make this change:

I don't agree with the Advisor. If there's no match, the subquery will just return NULL, so just use a plain equals "". If needed, use IsNull on the result of the subquery.

                 
SELECT   ISNULL(   (   SELECT Column
                       FROM OtherTable t2
                       WHERE t2.Column = t1.Column
                    )  AS SomeResult
               , '') AS Result
FROM     FirstTable t1

Open in new window

               


Couple other things:

The existing code uses the old style ANSI 89 joins.  

       SELECT ....
       FROM TableA, TableB
       WHERE TableA.Column = TableB.Column

Change those to ANSI 92 style.

       SELECT ..
      FROM TableA INNER JOIN TableB ON TableB.Column = TableA.Column

Also, that seems like a LOT of subqueries. Depending on the relationships, you might be able convert some of those to regular JOIN's which would make it more readable and also help performance.
1
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Very good _agx_

well "=*" is a right join but right joins are more easily expressed as a left join.

X LEFT JOIN Y is the same as Y RIGHT JOIN X.

Now with an inline subquery, it can only ever return a single value - it is destined to become another column, and a column has to be a value, a single value.

You have a lot of inline subqueries and they seem to be coming from the same 3 or 4 tables. Which probably means they should be moved to the main 'FROM'

It will take a while to unravel, and prepared to have a look if you want.
1
 
_agx_Connect With a Mentor Commented:
@Mark Wills - Thanks!  

well "=*" is a right join but right joins are more easily expressed as a left join

Definitely.  Whenever I come across a RIGHT JOIN in old code, I immediately rewrite it as a LEFT JOIN ;-) Never really thought about it, but it's probably because the logic of LEFT JOIN reads more like English and other Latin based languages, ie left to right.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
JnavarroMcIT ConsultantAuthor Commented:
I did it!

T-SQL 2000:

select tUseRemi = (SELECT IsNull(rb.UOrgTSigla, IsNull(rb.UOrgTDescrip, '')) FROM oeimUnidOrg rb WHERE rb.UOrgCCod =* Movimiento.cUOgRCode)
FROM Documento, Movimiento

T-SQL 2008:

select tUseRemi = (SELECT IsNull(rb.UOrgTSigla, IsNull(rb.UOrgTDescrip, '')) FROM oeimUnidOrg rb WHERE rb.UOrgCCod = Movimiento.cUOgRCode)
FROM Documento, Movimiento

Works!

I also made this change:

   INNER JOIN Documento
   ON Documento.nExpePeri = Movimiento.nExpePeri
   AND Documento.nExpeNumb = Movimiento.nExpeNumb
   AND Documento.cDocuCode = Movimiento.cDocuCode
   AND Documento.nExpePeri = @nExpePeri
   AND Documento.nExpeNumb = @nExpeNumb
   AND Documento.cDocuCode = @nDocuCode
   AND Documento.cElimFlag = 0
   AND Movimiento.cElimFlag = 0
   ORDER BY Movimiento.cMoviCode
End

Is working and is faster!

I would appreciate knowing if it seems right for you!
2
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Well Done !!!

Yep, those are two of the big hurdles, was going to suggest
FROM Documento
INNER JOIN Movimiento on  Documento.nExpePeri = Movimiento.nExpePeri 
                      AND Documento.nExpeNumb = Movimiento.nExpeNumb
                      AND Documento.cDocuCode = Movimiento.cDocuCode
                      AND Documento.cElimFlag = Movimiento.cElimFlag 

WHERE Documento.nExpePeri = @nExpePeri
AND   Documento.nExpeNumb = @nExpeNumb
AND   Documento.cDocuCode = @nDocuCode
AND   Documento.cElimFlag = 0

ORDER BY Movimiento.cMoviCode

Open in new window

And working though
      sTemaDesc = (SELECT Tema.sTemaDesc FROM Tema WHERE Tema.cTemaCode = Documento.cTemaCode),
         tTipoDocu = (SELECT Tipo_Documento.sDocuDesc FROM Tipo_Documento WHERE Tipo_Documento.cDocuType = Documento.cDocuType),



                       (SELECT IsNull(rb.UOrgTSigla, IsNull(rb.UOrgTDescrip, '')) FROM oeimUnidOrg rb WHERE rb.UOrgCCod =* Movimiento.cUOgRCode) + '-' +
                       (SELECT IsNull(rc.PerTDescrip,'') FROM oeimPersonal rc WHERE rc.PerCCod =* Movimiento.cUseRCode) + '-' + 
                       (SELECT IsNull(rg.CrgTDescrip, '') FROM oeimCargo rg WHERE rg.CrgCCod =* Movimiento.cCrgRCode)
                     WHEN '9999' THEN
                       (SELECT IsNull(rc.PerTDescrip, '') FROM oeimPersonal rc WHERE rc.PerCCod = Movimiento.cUseRCode)
                     ELSE 
                                 --(SELECT IsNull(ra.InstTDescrip, '') FROM (oeimInstitucion ra LEFT OUTER JOIN MOVIMIENTO AS M ON ra.InstCCod = M.cInsRCode)) + '-' +
                                 (SELECT IsNull(ra.InstTDescrip, '') FROM oeimInstitucion ra WHERE ra.InstCCod =* Movimiento.cInsRCode) + '-' +
                       (SELECT IsNull(rb.UOrgTSigla, IsNull(rb.UOrgTDescrip, '')) FROM oeimUnidOrg rb WHERE rb.UorgCCod =* Movimiento.cUOgRCode) + '-' +                                 
                       (SELECT IsNull(rc.PerTDescrip,'') FROM oeimPersonal rc WHERE rc.PerCCod =* Movimiento.cUseRCode)
                     END),
         tUseRemS = (SELECT SubString(IsNull(PerTNombre,''), 1, 1) + '.' + IsNull(PerTApePat, IsNull(PerTApePat,''))
                      FROM oeimPersonal WHERE PerCCod = Movimiento.cUseRCode),
         tUseDest = (CASE Movimiento.cInsDCode WHEN '0001' THEN
                       (SELECT IsNull(db.UOrgTSigla, IsNull(db.UOrgTDescrip, '')) FROM oeimUnidOrg db WHERE db.UorgCCod =* Movimiento.cUOgDCode) + '-' +
                       (SELECT IsNull(dc.PerTDescrip,'') FROM oeimPersonal dc WHERE dc.PerCCod =* Movimiento.cUseDCode) + '-' + 
                       (SELECT IsNull(dg.CrgTDescrip, '') FROM oeimCargo dg WHERE dg.CrgCCod =* Movimiento.cCrgDCode)
                     WHEN '9999' THEN
                      (SELECT IsNull(dc.PerTDescrip, '')
                         FROM oeimPersonal dc
                        WHERE dc.PerCCod = Movimiento.cUseDCode)
                     ELSE
                      (SELECT IsNull(da.InstTDescrip, '') FROM oeimInstitucion da WHERE da.InstCCod =* Movimiento.cInsDCode) + '-' +
                      (SELECT IsNull(db.UOrgTSigla, IsNull(db.UOrgTDescrip, '')) FROM oeimUnidOrg db WHERE db.UorgCCod =* Movimiento.cUOgDCode) + '-' +
                      (SELECT IsNull(dc.PerTDescrip,'') FROM oeimPersonal dc WHERE dc.PerCCod =* Movimiento.cUseDCode)
                     END),
         tUseDesS = (SELECT SubString(IsNull(PerTNombre,''), 1, 1) + '.' + IsNull(PerTApePat, IsNull(PerTApePat,''))
                      FROM oeimPersonal WHERE PerCCod = Movimiento.cUseDCode), 

Open in new window

that is, if you would like more input....
0
 
JnavarroMcIT ConsultantAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.