Problem when running a View in SQL Server

Question

Problem when running a View in SQL Server

Navigation

#1 by (1 votes)

0

I have a problem executing a view statement in SQL Server. The problem is that when I make a relation with another table to add another field to the query, it does not return anything to me. With this statement, it returns all fields except one:

SELECT     CASE WHEN _CPRManufacturingOrder_Custom.Mano = 1 THEN 'I' WHEN _CPRManufacturingOrder_Custom.Mano = 2 THEN 'D' ELSE '' END AS datvar1, 
                      dbo.FACOrderLineSL.Quantity AS envact, dbo._CPRManufacturingOrder_Custom.Ancho AS datvar4, dbo._CPRManufacturingOrder_Custom.Alto AS datvar5, 
                      dbo.FACOrderLineSL.CodCompany AS codemp, dbo.FACInvoicingTypeSL.CodInvoicingType AS tipcont, dbo.FACOrderSL.CodOrder AS numped, 
                      dbo.FACOrderLineSL.IDOrderLine AS numlin, dbo.CPRManufacturingOrder.InternalCode AS numalb, dbo._CPRManufacturingOrder_Custom.Fondo AS datvar6, 
                      YEAR(dbo.CPRManufacturingOrder.PlannedEndDate) AS AÑO, dbo._CPRManufacturingOrder_Custom.ZonaEnvio AS ENVIO, 
                      dbo._CPRManufacturingOrder_Custom.FE AS datvar3, dbo._CPRManufacturingOrder_Custom.TA, dbo._CPRManufacturingOrder_Custom.FFI, 
                      dbo._CPRManufacturingOrder_Custom.FFD, dbo._CPRManufacturingOrder_Custom.LI, dbo._CPRManufacturingOrder_Custom.LD, 
                      dbo._CPRManufacturingOrder_Custom.B
FROM         dbo.FACOrderLineSL INNER JOIN
                      dbo.FACOrderSL ON dbo.FACOrderLineSL.IDOrder = dbo.FACOrderSL.IDOrder INNER JOIN
                      dbo.CPRManufacturingOrder ON dbo.FACOrderLineSL.IDManufacturingOrder = dbo.CPRManufacturingOrder.IDManufacturingOrder INNER JOIN
                      dbo._CPRManufacturingOrder_Custom ON dbo.FACOrderLineSL.IDManufacturingOrder = dbo._CPRManufacturingOrder_Custom.IDManufacturingOrder INNER JOIN
                      dbo.FACInvoicingTypeSL ON dbo.FACOrderSL.IDInvoicingType = dbo.FACInvoicingTypeSL.IDInvoicingType INNER JOIN
                      dbo._FACOrderSL_Custom ON dbo.FACOrderLineSL.IDOrder = dbo._FACOrderSL_Custom.IDOrder INNER JOIN
                      dbo._FACOrderLineSL_Custom ON dbo.FACOrderLineSL.IDOrderLine = dbo._FACOrderLineSL_Custom.IDOrderLine
WHERE     (YEAR(dbo.CPRManufacturingOrder.PlannedEndDate) > 2007)

But with this other query, adding the table "_Decorados" and putting a "Case" no longer returns anything. I have also tried without the "Case" and simply saying to show me a data of the table, but nothing. If I add the table "_Decorated" it no longer returns anything.

SELECT     CASE WHEN _CPRManufacturingOrder_Custom.Mano = 1 THEN 'I' WHEN _CPRManufacturingOrder_Custom.Mano = 2 THEN 'D' ELSE '' END AS datvar1, 
                      dbo.FACOrderLineSL.Quantity AS envact, dbo._CPRManufacturingOrder_Custom.Ancho AS datvar4, dbo._CPRManufacturingOrder_Custom.Alto AS datvar5, 
                      dbo.FACOrderLineSL.CodCompany AS codemp, dbo.FACInvoicingTypeSL.CodInvoicingType AS tipcont, dbo.FACOrderSL.CodOrder AS numped, 
                      dbo.FACOrderLineSL.IDOrderLine AS numlin, dbo.CPRManufacturingOrder.InternalCode AS numalb, 
                      CASE WHEN _Decorados.CodDecorados = ' ' THEN _Decorados.Description ELSE '' END AS datvar2, dbo._CPRManufacturingOrder_Custom.Fondo AS datvar6, 
                      YEAR(dbo.CPRManufacturingOrder.PlannedEndDate) AS AÑO, dbo._CPRManufacturingOrder_Custom.ZonaEnvio AS ENVIO, 
                      dbo._CPRManufacturingOrder_Custom.FE AS datvar3, dbo._CPRManufacturingOrder_Custom.TA, dbo._CPRManufacturingOrder_Custom.FFI, 
                      dbo._CPRManufacturingOrder_Custom.FFD, dbo._CPRManufacturingOrder_Custom.LI, dbo._CPRManufacturingOrder_Custom.LD, 
                      dbo._CPRManufacturingOrder_Custom.B
FROM         dbo.FACOrderLineSL INNER JOIN
                      dbo.FACOrderSL ON dbo.FACOrderLineSL.IDOrder = dbo.FACOrderSL.IDOrder LEFT OUTER JOIN
                      dbo.CPRManufacturingOrder ON dbo.FACOrderLineSL.IDManufacturingOrder = dbo.CPRManufacturingOrder.IDManufacturingOrder INNER JOIN
                      dbo._CPRManufacturingOrder_Custom ON dbo.FACOrderLineSL.IDManufacturingOrder = dbo._CPRManufacturingOrder_Custom.IDManufacturingOrder INNER JOIN
                      dbo.FACInvoicingTypeSL ON dbo.FACOrderSL.IDInvoicingType = dbo.FACInvoicingTypeSL.IDInvoicingType LEFT OUTER JOIN
                      dbo._FACOrderSL_Custom ON dbo.FACOrderLineSL.IDOrder = dbo._FACOrderSL_Custom.IDOrder LEFT OUTER JOIN
                      dbo._FACOrderLineSL_Custom ON dbo.FACOrderLineSL.IDOrderLine = dbo._FACOrderLineSL_Custom.IDOrderLine LEFT OUTER JOIN
                      dbo._Decorados ON dbo._FACOrderLineSL_Custom.IDDecorados = dbo._Decorados.IDDecorados
WHERE     (YEAR(dbo.CPRManufacturingOrder.PlannedEndDate) > 2007)

sql server

asked by Urtx 18.04.2018 в 10:40

source

1 answer

Apache POI read cell format time 00:00:00 MVC C # create file and save it

score 1 · Answer 1

When using LEFT OUTER JOIN it brings some information because this instruction what it does is to return all the fields of the table on the left and bring from the table on the right only those that match the fields on the left.

Check if you are missing information in one of the tables you want to make the JOIN . You can find more information here about the use of combinations of tables in SQLSERVER .