Query Transact Sql - SubSelect

Navigation
1

I have an sql query that I can not determine, 

 (
    SELECT  (
        SELECT e.nombre
            FROM [Control_Documental].[dbo].[Etapa] AS e 
            WHERE e.idEtapa = etd.idEtapa
        )AS etapa
        FROM [Control_Documental].[dbo].[Registro_Etapa_Documento] AS red
        INNER JOIN [Control_Documental].[dbo].[Etapa_Tipo_Documento] AS etd ON etd.[idEtapaTipoDocumento] = red.[idEtapaTipoDocumento]
        INNER JOIN [Control_Documental].[dbo].[Etapa] AS e ON e.[idEtapa] = etd.[idEtapa]
        INNER JOIN [Control_Documental].[dbo].[Registro_Documento] AS rd ON rd.[idRegistroDocumento] = red.[idRegistroDocumento]
        INNER JOIN [Control_Documental].[dbo].[Factura] AS fa ON fa.[idFactura] = rd.[idDocumento]
        INNER JOIN [Control_Documental].[dbo].[Factura_Orden_Compra] AS fo ON fo.[idFactura] = fa.[idFactura]
        INNER JOIN [Control_Documental].[dbo].[Tipo_Documento] AS td ON td.[idTipoDocumento] = rd.[idTipoDocumento]
        WHERE CONVERT(CHAR(13), fo.[orden_compra]) = oc.[numero_documento]
            AND td.[nombre] = 'Factura'
            AND red.recepcionado = 1
            AND red.idEtapaTipoDocumento=(select max(red.idEtapaTipoDocumento) 
                                              FROM [Control_Documental].[dbo].[Registro_Etapa_Documento] AS red
                                              where red.recepcionado='1' )
)AS estado

my problem is in the 3rd AND of the 2nd subselect AND red.idEtapaTipoDocumento= here is where I can not get the status of the stage. here should bring me the most updated state of the tb_registro_etapa_documento according to the previous query. but it just brings me the data in NULL. if to this AND you will assign a value of the stage, for example: 

AND red.idEtapaTipoDocumento='9'

brings me the status for all those who do comply (but it is not the idea). since I need you to bring me the status of the stage for all the data.

Something fails me in this 3rd subselect, if someone could help me I would appreciate it.

I hope you can understand me and help me.

    
asked by Francisco Acevedo 23.09.2017 в 00:33
source

2 answers

0

I managed to bring the state with the following query:

( SELECT e.nombre FROM [Control_Documental].[dbo].[Registro_Etapa_Documento] AS red INNER JOIN [Control_Documental].[dbo].[Etapa_Tipo_Documento] AS etd ON etd.[idEtapaTipoDocumento] = red.[idEtapaTipoDocumento] INNER JOIN [Control_Documental].[dbo].[Etapa] AS e ON e.[idEtapa] = etd.[idEtapa] INNER JOIN [Control_Documental].[dbo].[Registro_Documento] AS rd ON rd.[idRegistroDocumento] = red.[idRegistroDocumento] INNER JOIN [Control_Documental].[dbo].[Factura] AS fa ON fa.[idFactura] = rd.[idDocumento] INNER JOIN [Control_Documental].[dbo].[Factura_Orden_Compra] AS fo ON fo.[idFactura] = fa.[idFactura] INNER JOIN [Control_Documental].[dbo].[Tipo_Documento] AS td ON td.[idTipoDocumento] = rd.[idTipoDocumento] WHERE CONVERT(CHAR(13), fo.[orden_compra]) = oc.[numero_documento] AND red.idEtapaTipoDocumento = (SELECT MAX(red.idEtapaTipoDocumento) FROM [Control_Documental].[dbo].[Registro_Etapa_Documento] AS red INNER JOIN [Control_Documental].[dbo].[Etapa_Tipo_Documento] AS etd ON etd.[idEtapaTipoDocumento] = red.[idEtapaTipoDocumento] INNER JOIN [Control_Documental].[dbo].[Etapa] AS e ON e.[idEtapa] = etd.[idEtapa] INNER JOIN [Control_Documental].[dbo].[Registro_Documento] AS rd ON rd.[idRegistroDocumento] = red.[idRegistroDocumento] INNER JOIN [Control_Documental].[dbo].[Factura] AS fa ON fa.[idFactura] = rd.[idDocumento] INNER JOIN [Control_Documental].[dbo].[Factura_Orden_Compra] AS fo ON fo.[idFactura] = fa.[idFactura] INNER JOIN [Control_Documental].[dbo].[Tipo_Documento] AS td ON td.[idTipoDocumento] = rd.[idTipoDocumento] WHERE CONVERT(CHAR(13), fo.[orden_compra]) = oc.[numero_documento] and red.recepcionado='1' ) ) as estado

    
answered by 25.09.2017 / 04:10
source
0

Friend, first of all you do not need those subqueries, only JOIN's

the second subquery retrieves the name of the stage, which you already have in the third subquery, you could eliminate the first 2 like this: 

    SELECT e.nombre
    FROM [Control_Documental].[dbo].[Registro_Etapa_Documento] AS red
    INNER JOIN [Control_Documental].[dbo].[Etapa_Tipo_Documento] AS etd 
      ON etd.[idEtapaTipoDocumento] = red.[idEtapaTipoDocumento]
    INNER JOIN [Control_Documental].[dbo].[Etapa] AS e 
      ON e.[idEtapa] = etd.[idEtapa]
    INNER JOIN [Control_Documental].[dbo].[Registro_Documento] AS rd 
      ON rd.[idRegistroDocumento] = red.[idRegistroDocumento]
    INNER JOIN [Control_Documental].[dbo].[Factura] AS fa 
      ON fa.[idFactura] = rd.[idDocumento]
    INNER JOIN [Control_Documental].[dbo].[Factura_Orden_Compra] AS fo 
      ON fo.[idFactura] = fa.[idFactura]
    INNER JOIN [Control_Documental].[dbo].[Tipo_Documento] AS td 
      ON td.[idTipoDocumento] = rd.[idTipoDocumento]
    WHERE CONVERT(CHAR(13), fo.[orden_compra]) = oc.[numero_documento]
      AND td.[nombre] = 'Factura'
      AND red.recepcionado = 1
      AND red.idEtapaTipoDocumento=
      (
        select max(red.idEtapaTipoDocumento) 
        FROM [Control_Documental].[dbo].[Registro_Etapa_Documento] AS red
        where red.recepcionado='1' 
      )
    ) AS estado

With that you delete the nested subqueries.

Now to recover the maximum id of the stage that seems to be numeric, you should create a function to which you send the identifier of the object to which the record of the stage belongs and return the maximum. Where you replace it with the name of the field that uniquely identifies the object for which you want the maximum status. 

    SELECT e.nombre, dbo.registroEtapaEstadoMaximo(<identificador>) as maximoRegistroEstado  
    FROM [Control_Documental].[dbo].[Registro_Etapa_Documento] AS red
    INNER JOIN [Control_Documental].[dbo].[Etapa_Tipo_Documento] AS etd 
      ON etd.[idEtapaTipoDocumento] = red.[idEtapaTipoDocumento]
    INNER JOIN [Control_Documental].[dbo].[Etapa] AS e 
      ON e.[idEtapa] = etd.[idEtapa]
    INNER JOIN [Control_Documental].[dbo].[Registro_Documento] AS rd 
      ON rd.[idRegistroDocumento] = red.[idRegistroDocumento]
    INNER JOIN [Control_Documental].[dbo].[Factura] AS fa 
      ON fa.[idFactura] = rd.[idDocumento]
    INNER JOIN [Control_Documental].[dbo].[Factura_Orden_Compra] AS fo 
      ON fo.[idFactura] = fa.[idFactura]
    INNER JOIN [Control_Documental].[dbo].[Tipo_Documento] AS td 
      ON td.[idTipoDocumento] = rd.[idTipoDocumento]
    WHERE CONVERT(CHAR(13), fo.[orden_compra]) = oc.[numero_documento]
      AND td.[nombre] = 'Factura'
      AND red.recepcionado = 1

Search in google how to implement functions in SQL Server TSQL

Additionally, I give you a tip to make you better as a database programmer.

Always be clear about the data model (The concept of relationships between tables), where is the information you need ?, Create the queries with the join that you require. The criteria or filters that you require and finally if it is necessary to group data.

According to your question, you affirm that you need to bring the stage to all the data, but you are only bringing the information of the stage and the maximum state, where the "data" are.

I hope I have helped you.

    
answered by 23.09.2017 в 01:27
Problems in Report Viewer in Visual Studio 2017 Error sending signed xml to DGI ws