Select with fields without values

Question

Select with fields without values

Navigation

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

1

I am making a SELECT where together two tables: a stock balance table in stock and another table with purchase order.

I'm doing a comparison between balance of products in stock and balance in purchase orders, but the way I'm doing, SELECT is only bringing products that have open purchase order balances in the system.

I need the SELECT to bring not only the products that have balance in the purchase order, but also all products in the independent products table if you have a purchase order placed or not. Products that have no purchase order placed and SELECT have a value of 0 in the column.

SELECT      DISTINCT SB1.B1_DESC            AS PRODUTO
        ,   SUM(SB8.B8_SALDO)               AS SALDO
        ,   (SC7.C7_QUANT - SC7.C7_QUJE)    AS [SALDO DE COMPRAS]
        ,   SC7.C7_NUM                      AS PEDIDO
        ,   SB1.B1_EMIN                     AS [SALDO MINIMO EM ESTOQUE]
FROM        SB1010 AS SB1
INNER JOIN  SB8010 AS SB8 WITH(NOLOCK) ON SB8.B8_PRODUTO = SB1.B1_COD
INNER JOIN  SC7010 AS SC7 WITH(NOLOCK) ON SC7.C7_PRODUTO = SB1.B1_COD
WHERE       ISB8.B8_LOCAL   =  '01'
        AND SC7.C7_ENCER    <> 'E'
        AND SB1.D_E_L_E_T_  =  ''
        AND SB8.D_E_L_E_T_  =  ''
        AND SC7.D_E_L_E_T_  =  ''
GROUP BY    SB1.B1_DESC
        ,   SB1.B1_EMIN
        ,   SC7.C7_QUANT
        ,   SC7.C7_QUJE
        ,   SC7.C7_NUM

OBS : In this SELECT I have a filter SC7.C7_ENCER which means the purchase orders already completed.

sql

asked by anonymous 05.07.2018 / 00:30

2 answers

0

Switch INNER JOIN to LEFT JOIN:

SELECT DISTINCT
SB1.B1_DESC       AS PRODUTO,
SUM(SB8.B8_SALDO) AS SALDO,
(SC7.C7_QUANT - SC7.C7_QUJE) AS [SALDO DE COMPRAS],
SC7.C7_NUM AS PEDIDO,
SB1.B1_EMIN AS [SALDO MINIMO EM ESTOQUE]
FROM SB1010 AS SB1
LEFT JOIN SB8010 AS SB8 WITH(NOLOCK) ON SB8.B8_PRODUTO = SB1.B1_COD
LEFT JOIN SC7010 AS SC7 WITH(NOLOCK) ON SC7.C7_PRODUTO = SB1.B1_COD
WHERE SB8.B8_LOCAL = '01'
AND SC7.C7_ENCER <> 'E'
AND SB1.D_E_L_E_T_ = ''
AND SB8.D_E_L_E_T_ = ''
AND SC7.D_E_L_E_T_ = ''
GROUP BY SB1.B1_DESC, SB1.B1_EMIN, SC7.C7_QUANT, SC7.C7_QUJE, SC7.C7_NUM

05.07.2018 / 01:44

How to create a panel that displays / Hides fields using Html / Bootstap / JavaScript How to check if a file has been selected after it is still in the directory in JavaScript

score 1 · Accepted Answer

You should change the INNER JOIN to LEFT JOIN , thus avoiding that the code of the SB1 table exists in the other two tables.

One aspect that may have an impact is the fact that you do not validate the NULL in the WHERE clause, which almost certainly prevents you from obtaining another result.

I think this should work:

SELECT      DISTINCT SB1.B1_DESC                    AS PRODUTO
        ,   SUM(ISNULL(SB8.B8_SALDO, 0))            AS SALDO
        ,   ISNULL((SC7.C7_QUANT - SC7.C7_QUJE, 0)) AS [SALDO DE COMPRAS]
            -- não sei o tipo da coluna, por isso coloquei "0"
            -- se for VARCHAR colocar '' ou outro valor por omissão em caso de NULL
        ,   ISNULL(SC7.C7_NUM, 0)                   AS PEDIDO
        ,   ISNULL(SB1.B1_EMIN, 0)                  AS [SALDO MINIMO EM ESTOQUE]
FROM        SB1010 AS SB1
LEFT JOIN   SB8010 AS SB8 (NOLOCK) ON SB8.B8_PRODUTO = SB1.B1_COD
LEFT JOIN   SC7010 AS SC7 (NOLOCK) ON SC7.C7_PRODUTO = SB1.B1_COD
WHERE       ISNULL(SB8.B8_LOCAL, '')    =  '01'
        AND ISNULL(SC7.C7_ENCER, '')    <> 'E'
        AND SB1.D_E_L_E_T_              =  ''
        AND ISNULL(SB8.D_E_L_E_T_, '')  =  ''
        AND ISNULL(SC7.D_E_L_E_T_, '')  =  ''
GROUP BY    SB1.B1_DESC
        ,   SB1.B1_EMIN
        ,   ISNULL(SC7.C7_QUANT, 0)
        ,   ISNULL(SC7.C7_QUJE, 0)
        ,   ISNULL(SC7.C7_NUM, 0)