Error Order By Clause is invalid SQL Server

Question

Error Order By Clause is invalid SQL Server

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I would like you to help me with the error in the Order By, because it marks me the clause error

CREATE PROCEDURE SP_ExportExcelHistoryPRUEBA
-- ALL DATA DEFAULT(TODOS LOS DATOS)


AS BEGIN 

SET NOCOUNT ON 



DECLARE @TODAS VARCHAR(100)

-- CONSULT  ALL DATA
SET @TODAS = ( SELECT transportista.nombre, 
       registroembarque.transfer, 
       CASE 
         WHEN registroembarque.delivered = 1 THEN 'Delivered' 
         ELSE 
           CASE 
             WHEN registroembarque.nofactura IN (SELECT IM.[nofactura] 
                                                 FROM   imagenfactura IM) THEN 
             'Not Delivered' 
             ELSE 'Not Delivered / Missing Images' 
           END 
       END                                                          AS Delivered 
       , 
       Isnull(CONVERT(VARCHAR(12), registroembarque.fecharegistro, 101), 
       'InTransit') 
                                                                    AS Fecha, 
       registroembarque.nofactura                                   AS Factura, 
       registroembarque.pedimento, 
       registroembarque.ammex_usentry748, 
       chofer.nombre                                                AS Driver, 
       Isnull(contenedor.placascontenedor, '') 
       + Isnull(+ '/' + contenedornoeconomico.noeconomico, '')      AS 
       'Contenedor  (Placas/No.Eco)', 
       Isnull(RIGHT(CONVERT(VARCHAR(25), registroembarque.horamanifiestoslistos, 
                    100), 
              13), '')                                              AS 
       [Manifiestos Listos], 
       Isnull(RIGHT(CONVERT(VARCHAR(25), registroembarque.horacargayarda, 100), 
              13), '' 
       )                                                            AS 
       [Carga Yarda], 
       Isnull(RIGHT(CONVERT(VARCHAR(25), registroembarque.horainspeccion, 100), 
              13), '' 
       )                                                            AS 
       Inspeccion, 
       Isnull(resultadoverificacion.descripcion, N'')               AS 
       [Resultado Inspeccion], 
       Isnull(RIGHT(CONVERT(VARCHAR(25), 
              registroembarque.horamanifiestoslistos, 100), 13), '')AS 
       [Documentos Entregados], 
       Isnull(RIGHT(CONVERT(VARCHAR(25), 
                    registroembarque.horamoduclacionaduanamx, 100) 
              , 13), '')                                            AS [Mod MEX] 
       , 
       CASE 
         WHEN revisionmilitar = 0 THEN 'NO' 
         WHEN revisionmilitar IS NULL THEN '' 
         ELSE RIGHT(CONVERT(VARCHAR(25), 
              Isnull(registroembarque.horamoduclacionaduanamx, ''), 
              100), 13) 
              + '/' 
              + RIGHT(CONVERT(VARCHAR(25), 
              Isnull(registroembarque.horarevisionmilitarsalida, ''), 100), 13) 
       END                                                          AS 
       [Rev Militar MEX (Entrada/Salida)], 
       CASE 
         WHEN reconocimientous = 0 THEN 'NO' 
         WHEN reconocimientous IS NULL THEN '' 
         ELSE RIGHT(CONVERT(VARCHAR(25), 
              Isnull(registroembarque.reconocimientousentrada, ''), 
              100), 13) 
              + '/' 
              + RIGHT(CONVERT(VARCHAR(25), 
              Isnull(registroembarque.reconocimientoussalida, ''), 100), 13) 
       END                                                          AS 
       [ReconocimientoUS (Entrada/Salida)], 
       Isnull(Substring(registroembarque.notas, 0, 10), '')         AS 
       Observaciones, 
       Isnull (registroembarque.notas, '')                          AS 
       NotasCompletas, 
       Isnull(RIGHT(CONVERT(VARCHAR(25), 
                    registroembarque.horamodulacionaduanaus, 100), 
              13), '')                                              AS 
       HoraModulacionAduanaUS, 
       CASE 
         WHEN reconocimiento = 0 THEN 'NO' 
         WHEN reconocimiento IS NULL THEN '' 
         ELSE RIGHT(CONVERT(VARCHAR(25), 
              Isnull(registroembarque.horareconocimientoentrada, '' 
              ), 100), 13) 
              + '/' 
              + RIGHT(CONVERT(VARCHAR(25), 
              Isnull(registroembarque.horareconocimientosalida, ''), 100), 13) 
       END                                                          AS 
       [Reconocimiento (Entrada/Salida)], 
       resultadoverificacion.descripcion, 
       Isnull(RIGHT(CONVERT(VARCHAR(25), 
                    registroembarque.aar_fechahoracaptura, 100), 
              13), '') 
       [DocsRecvFromDO], 
       Isnull(RIGHT(CONVERT(VARCHAR(25), registroembarque.ammex_docsready, 108), 
              13), 
       '')                                                          AS 
       [AMMEXDocsReady], 
       Isnull(registroembarque.ammex_docstodriver, '')              AS 
       AMMEX_DocsToDriver, 
       Isnull(RIGHT(CONVERT(VARCHAR(25), 
                    registroembarque.horamoduclacionaduanamx, 100) 
              , 13), '')                                            [SATArrival] 
       , 
       Isnull(CASE 
                WHEN CONVERT(DATE, registroembarque.ammex_cbparrival) = 
                     '1900-01-01' 
              THEN '' 
                ELSE CONVERT(CHAR(10), registroembarque.ammex_cbparrival, 108) 
              END, '')                                              AS 
       [CBPArrival], 
       Isnull(CASE 
                WHEN CONVERT(DATE, registroembarque.ammex_cbprelease) = 
                     '1900-01-01' 
              THEN '' 
                ELSE CONVERT(CHAR(10), registroembarque.ammex_cbprelease, 108) 
              END, '')                                              AS 
       [CBPRelease] ,

       /*NEW DATA*/
       Obs.descripcion AS Observations,
       (SELECT u.descripcion + ',' 
        FROM   embarque_unloading eu 
               INNER JOIN unloading u 
                       ON u.idunloading = eu.idunloading 
        WHERE  eu.nofactura = RegistroEmbarque.nofactura 
        FOR xml path(''))                   AS Unloadings, 
       (SELECT u.descripcion + ',' 
        FROM   embarque_delay eu 
               INNER JOIN delays u 
                       ON u.iddelay = eu.iddelay 
        WHERE  eu.nofactura = RegistroEmbarque.NoFactura 
        FOR xml path(''))                   AS Delays, 
       incidencia 
       /*END NEW DATA*/

FROM   RegistroEmbarque 
       INNER JOIN chofer 
               ON registroembarque.cheferid = chofer.cheferid 
       INNER JOIN contenedor 
               ON registroembarque.contenedorid = contenedor.contenedorid 
       INNER JOIN contenedornoeconomico 
               ON registroembarque.contenedornoeconomicoid = 
                  contenedornoeconomico .contenedornoeconomicoid 
       INNER JOIN transportista 
               ON registroembarque.transportistaid = 
                  transportista.transportistaid 
       LEFT OUTER JOIN resultadoverificacion 
               ON registroembarque.resultadoverificacionid = 
                       resultadoverificacion.resultadoverificacionid 
        -- NEW DATA
        LEFT OUTER JOIN Observaciones Obs 
               ON Obs.idobservacion = RegistroEmbarque.AMMEX_IdObservation
               -- END NEW DATA
WHERE  registroembarque.estado = 2 
ORDER  BY NoFactura ASC )

END 
GO
-- END ALL DATA

sql

asked by Daniel 08.02.2018 в 02:30

source

3 answers

FCM push notifications do not arrive with the app closed Problem connecting to CloudMqtt with variables

score 0 · Answer 1

The problem you have can be summarized conceptually in the following example

declare @name varchar(255)

select @name = (select  name
            from systypes
            order by name
        )

The error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

What does it mean?

You can not use a ORDER clause in a subquery, as simple as that. However, if you could do the following:

declare @name varchar(255)

select @name = name
    from systypes
    order by name

By taking the parentheses, you no longer have a subquery but you have a single query. Anyway in operations with variables, the ORDER BY only makes sense if we are looking to stay with the last of the records, a selection operation to a variable should normally bring us a single record, because a variable can not accommodate more than one (unless we concatenate but this is not the case). Another issue that I do not understand very well is that you are looking to return a subquery with several columns and try to assign it to a single variable. I suggest you do something like this anyway:

SELECT @TransportistaNombre = transportista.nombre, 
       @RegistroembarqueTransfer = registroembarque.transfer, 
       ...
       ORDER  BY NoFactura ASC

That is, each column with its respective variable, if we only need one data, we only leave that column. The query now does, without parentheses, and we use directly the SELECT and not the SET for the assignment. The ORDER BY only if the query returns more than one record and we want to finally assign the newest% per NoFactura .

Note: We should not abuse this technique to get the last of the records, imagine a query of millions of rows that we do only to obtain the last, there are more optimal ways to solve it from the logic SQL .

score 0 · Answer 2

Thank you very much everyone, I got a different way of doing it, I hope it serves you.

  Create Procedure Sp_Ejemplo

@Busqueda as varchar(20)
AS BEGIN
SET NOCOUNT ON

IF(@Busqueda = 'Todas')
BEGIN 
SELECT SELECT transportista.nombre, 
       registroembarque.transfer, 
       CASE .......

END 


IF(@Busqueda = 'MESES')
BEGIN 
SELECT SELECT transportista.nombre, 
       registroembarque.transfer, 
       CASE .......

END 

END GO

score 0 · Answer 3

According to the previous answer, it is correct that order by can not be used in views, functions, subconsultations, etc.

I see that you want to return the result of a query, for this you can do it with a view or function, and use the order by where it is called.

CREATE FUNCTION obtenerdatos (@storeid int)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT P.ProductID, P.Name
    FROM Production.Product AS P  
    WHERE C.StoreID = @storeid  
);

and then you can do one:

select * from obtenerdatos(25)

Or with a view.