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