necessário repetir a operação anterior no local de nulo

votos
1

Meu procedimento de armazenamento:

SELECT B.ETADATE,
  a.NAME,
  a.CATEGORY ,
  a.TYPE,
  a.STOCK
FROM
  (SELECT TO_CHAR(TO_DATE(FROM_DATE,'DD-MM-YYYY HH24:MI:SS')) AS FROM_DATE,
    NAME                                                      AS NAME,
    CATEGORY                                                  AS CATEGORY,
    TYPE                                                      AS TYPE,
    BALANCE                                                   AS BALANCE
  FROM VW_NET_STOCK_POSITION
  ) a,
  (SELECT dt + LEVEL AS ETADate
  FROM
    (SELECT TRUNC (TO_DATE ('01-09-2018 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 'MM') - 1 AS dt
    FROM DUAL
    ) D
    CONNECT BY LEVEL <= sysdate - dt
  ) B
WHERE a.FROM_DATE(+) = B.ETADATE
ORDER BY ETADate;

e minha saída é:

digite

mas quero que, onde 'nulo', então não deve ser substituída com a transação anterior.

Como isso,

quer uma saída como esta :

digite

Publicado 20/09/2018 em 04:11
fonte usuário
Em outras línguas...                            


2 respostas

votos
0

Não use +para as junções externas! A solução para o seu problema é LAG(. . . IGNORE NULLS). Mas a consulta pode ser limpo.

Eu sugeriria escrevendo isso como:

WITH dates as (
      SELECT (dt + level - 1) as etadate
      FROM (SELECT TRUNC(DATE '2018-09-01', 'MM') - 1 AS dt
            FROM DUAL
           ) d
      CONNECT BY LEVEL <= sysdate - dt + 1  -- to get today's date
  ) 
SELECT d.etadate,
       COALESCE(nsp.name, LAG(nsp.name IGNORE NULLS) OVER (ORDER BY d.etadate)) as name,
       COALESCE(nsp.category, LAG(nsp.category IGNORE NULLS) OVER (ORDER BY d.etadate)) as category,
       COALESCE(LAG(nsp.type IGNORE NULLS) OVER (ORDER BY d.etadate), type) as type,
       COALESCE(LAG(nsp.stock IGNORE NULLS) OVER (ORDER BY d.etadate), stock) as stock
FROM dates d LEFT JOIN
     VW_NET_STOCK_POSITION nsp
     ON d.etadate = TRUNC(nsp.from_date)
ORDER BY d.ETADate;
Respondeu 20/09/2018 em 11:35
fonte usuário

votos
0

Você pode usar LAST_VALUEa função analítica com IGNORE NULLSopção de fazer o SQL da seguinte forma:

SELECT ETADATE,
       LAST_VALUE(NAME)  IGNORE NULLS OVER (ORDER BY etadate) as NAME, 
       LAST_VALUE(CATEGORY) IGNORE NULLS OVER (ORDER BY etadate) as CATEGORY,
       LAST_VALUE(TYPE) IGNORE NULLS OVER (ORDER BY etadate ) as TYPE,
       LAST_VALUE(BALANCE) IGNORE NULLS OVER (ORDER BY etadate ) as STOCK
  FROM 
    (SELECT TO_CHAR(TO_DATE(FROM_DATE,'DD-MM-YYYY HH24:MI:SS')) AS "FROM_DATE",
      NAME                                                      AS "NAME",
      CATEGORY                                                  AS "CATEGORY",
      TYPE                                                      AS "TYPE",
      BALANCE                                                   AS "BALANCE"
    FROM VW_NET_STOCK_POSITION
    ) a LEFT OUTER JOIN
    (SELECT dt + LEVEL AS ETADate
       FROM
      (SELECT TRUNC (TO_DATE ('01-09-2018 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 'MM') - 2 
           AS dt
      FROM DUAL
      ) D
      CONNECT BY LEVEL <= trunc(sysdate) - dt
    ) B
   ON ( a.FROM_DATE = B.ETADATE )
ORDER BY ETADate;

e preferem usar ANSI JOINpadrão.

SQL Fiddle Demonstração

Respondeu 20/09/2018 em 05:21
fonte usuário

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more