Condemn WHERE with a Subselect - SQL

0

I can not show the requisitions that have their status in NULL.

Query principal that brings requisitions associated with "X" data:

SELECT
re.[codigo] AS codigo_requisicion
FROM [Control_Documental].[dbo].[Requisicion] AS re 
LEFT JOIN [Control_Documental].[dbo].[Distribucion_Requisicion] AS dr 
ON dr.[idRequisicion] = re.[idRequisicion]
LEFT JOIN [MSAXPRODG4].[dbo].[PURCHTABLE] as ptNu 
ON ptNu.REQATTENTION = re.[codigo]
LEFT JOIN [Control_Documental].[dbo].[Orden_Compra] as oc 
ON oc.[numero_documento] = ptNu.PURCHID
LEFT JOIN [Control_Documental].[dbo].[Documento_Orden_Compra] as doc 
ON doc.[idOrdenCompra] = oc.[idOrdenCompra]
order by re.[fecha_emision] desc

until here everything is fine.

Then I want to show the estado in which the requisition is found, I apply a subselect to bring the state and it would be this way:

SELECT
re.[codigo] AS codigo_requisicion,
(
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
FROM [Control_Documental].[dbo].[Requisicion] AS re 
LEFT JOIN [Control_Documental].[dbo].[Distribucion_Requisicion] AS dr 
ON dr.[idRequisicion] = re.[idRequisicion]
LEFT JOIN [MSAXPRODG4].[dbo].[PURCHTABLE] as ptNu 
ON ptNu.REQATTENTION = re.[codigo]
LEFT JOIN [Control_Documental].[dbo].[Orden_Compra] as oc 
ON oc.[numero_documento] = ptNu.PURCHID
LEFT JOIN [Control_Documental].[dbo].[Documento_Orden_Compra] as doc 
ON doc.[idOrdenCompra] = oc.[idOrdenCompra]
ORDER BY re.[fecha_emision] desc

Until here everything is fine, it shows me the requisition and its status, it is worth mentioning that there are requisitions that do not have an associated state, therefore it shows this data in null or empty.

My problem is as follows, now I need to bring the requisitions that do not have an associated status, or that their status is in NULL , should show only this:

Then when conditioning my main Query: WHERE estado IS NULL I throw error invalid column name or if I condition by WHERE e.[nombre] IS NULL throw me error the multi part identifier e.[nombre] could not be found

    
asked by Francisco Acevedo 28.09.2017 в 15:39
source

1 answer

1

Well, first of all, I must say that you should find a simpler way to look up the status of a requisition.

Now, to achieve what you want (and without intervening your current code) you should simply use a derived table:

SELECT *
FROM ("poner tu consulta actual acá") t
WHERE estado IS NULL;
    
answered by 28.09.2017 / 15:43
source