Change MySQL query to work in SQL Server and Oracle

0

Would anyone know how to tell me how this MySQL query would look like in SQL Server and Oracle?

SELECT 
COUNT(*) AS TOTAL,
    SUM(EVENTO = 7 AND RESULTADO = 0) AS QTD_RX_OK,
    SUM(EVENTO = 7 AND RESULTADO <> 0) AS QTD_RX_ERR,
    SUM(EVENTO = 5 AND RESULTADO = 0) AS QTD_TX_OK,
    SUM(EVENTO = 5 AND RESULTADO <> 0) AS QTD_TX_ERR,
    SUM(EVENTO = 8 AND RESULTADO = 0) AS QTD_ROUTING_OK,
    SUM(EVENTO = 8 AND RESULTADO <> 0) AS QTD_ROUTING_ERR,
    SUM(EVENTO = 1 AND RESULTADO = 0) AS QTD_SESSION_IN_OK,
    SUM(EVENTO = 1 AND RESULTADO <> 0) AS QTD_SESSION_IN_ERR,
    SUM(EVENTO = 2 AND RESULTADO = 0) AS QTD_SESSION_OUT_OK,
    SUM(EVENTO = 2 AND RESULTADO <> 0) AS QTD_SESSION_OUT_ERR,
    SUM(CASE WHEN (EVENTO = 7 AND RESULTADO = 0) THEN TAMARQUIVO ELSE 0 END ) AS QTD_BYTES_RX_OK,
    SUM(CASE WHEN (EVENTO = 5 AND RESULTADO = 0) THEN TAMARQUIVO ELSE 0 END ) AS QTD_BYTES_TX_OK,
    SUM(CASE WHEN (EVENTO = 8 AND RESULTADO = 0) THEN TAMARQUIVO ELSE 0 END ) AS QTD_BYTES_ROUTING_OK,
    ROUND(SUM(CASE WHEN (EVENTO = 7 AND RESULTADO = 0) THEN TAMARQUIVO ELSE 0 END ) / SUM(EVENTO = 7 AND RESULTADO = 0)) AS QTD_MEDIA_RX_OK,
    ROUND(SUM(CASE WHEN (EVENTO = 5 AND RESULTADO = 0) THEN TAMARQUIVO ELSE 0 END ) / SUM(EVENTO = 5 AND RESULTADO = 0)) AS QTD_MEDIA_TX_OK,
    ROUND(SUM(CASE WHEN (EVENTO = 8 AND RESULTADO = 0) THEN TAMARQUIVO ELSE 0 END ) / SUM(EVENTO = 8 AND RESULTADO = 0)) AS QTD_MEDIA_ROUTING_OK
FROM TBLTRANSFERS 
WHERE DATAHORA BETWEEN '2017-03-14 09:40:02' AND '2017-03-14 09:50:02'
    AND (UPPER(SUBSTRING_INDEX(NOMEARQUIVO, '\\', -1)) LIKE '/Users/teste/tmp/teste/data/app/Relatorio.pdf')
    AND (INSTANCE = 'appl02' AND USUARIO = 'teste');

SQL SERVER

To create DB in SQL Server, follow the script:

CREATE TABLE TBLTRANSFERS
(
IDTRANSFER INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DATAHORA DATETIME NOT NULL,
INSTANCE VARCHAR(255) NOT NULL DEFAULT '*',
USUARIO VARCHAR(255) NOT NULL,
CONEXAO TEXT NULL,
DATAARQUIVO CHAR(8) NULL DEFAULT NULL,
EVENTO INT NOT NULL,
HORAARQUIVO CHAR(6) NULL DEFAULT NULL,
MAXTAMREG INT NULL DEFAULT NULL,
MENSAGEM VARCHAR(255) NULL DEFAULT NULL,
NOMEARQUIVO VARCHAR(255) NULL DEFAULT NULL,
PROCID INT NOT NULL,
REDE VARCHAR(16) NULL DEFAULT NULL,
RESULTADO INT NULL DEFAULT NULL,
TAMARQUIVO DECIMAL(11,0) NULL DEFAULT NULL,
THREADID INT NOT NULL,
TIPOREC CHAR(1) NULL DEFAULT NULL
);

CREATE INDEX DATAHORA
ON TBLTRANSFERS (DATAHORA);

CREATE INDEX DATAHORA_2
ON TBLTRANSFERS (DATAHORA, USUARIO);

For insertion into SQL Server:

INSERT INTO dbo.TBLTRANSFERS
(DATAHORA, INSTANCE, USUARIO, CONEXAO, DATAARQUIVO, EVENTO, HORAARQUIVO, 
MAXTAMREG, MENSAGEM, NOMEARQUIVO, PROCID, REDE, RESULTADO, TAMARQUIVO, 
THREADID, TIPOREC)
VALUES
('2017-03-14 09:45:02', 'testeapp', 'teste',  'testando texto',
'', 2, '', 0, 'Fim de sessao de entrada', '/Users/teste/tmp/data/river/teste/Relatorio.pdf', 99098, 'teste', 0, 13074, 3740130, 'U');

ORACLE

Oracle Creation

CREATE TABLE TBLTRANSFERS
(
IDTRANSFER NUMBER(10,0) NOT NULL PRIMARY KEY,
DATAHORA TIMESTAMP NOT NULL,
INSTANCE VARCHAR(255) DEFAULT '*' NOT NULL,
USUARIO VARCHAR(255) NOT NULL,
CONEXAO CLOB NULL,
DATAARQUIVO CHAR(8) DEFAULT NULL NULL,
EVENTO NUMBER(10,0) NOT NULL,
HORAARQUIVO CHAR(6) DEFAULT NULL NULL,
MAXTAMREG NUMBER(10,0) DEFAULT NULL NULL,
MENSAGEM VARCHAR(255) DEFAULT NULL NULL,
NOMEARQUIVO VARCHAR(255) DEFAULT NULL NULL,
PROCID NUMBER(10,0) NOT NULL,
REDE VARCHAR(16) DEFAULT NULL NULL,
RESULTADO NUMBER(10,0) DEFAULT NULL NULL,
TAMARQUIVO DECIMAL(11,0) DEFAULT NULL NULL,
THREADID NUMBER(10,0) NOT NULL,
TIPOREC CHAR(1) DEFAULT NULL NULL
);

CREATE INDEX DATAHORA_TBLTRANSF
ON TBLTRANSFERS (DATAHORA);

CREATE INDEX DATAHORA_2_TBLTRANSF
ON TBLTRANSFERS (DATAHORA, USUARIO);

CREATE SEQUENCE TBLTRANSFERS_SEQ
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 100;

Oracle Insertion:

INSERT INTO TBLTRANSFERS(IDTRANSFER, DATAHORA, INSTANCE, USUARIO, CONEXAO, 
DATAARQUIVO, EVENTO, HORAARQUIVO, MAXTAMREG, MENSAGEM, NOMEARQUIVO, PROCID, 
REDE, RESULTADO, TAMARQUIVO, THREADID, TIPOREC)
VALUES
(TBLTRANSFERS_SEQ.NEXTVAL, to_date('2017-03-14 09:45:02', 'YYYY-MM-DD 
HH24:MI:SS'), 'testeapp', 'river2',  'teste de texto',
'', 2, '', 0, 'Fim de sessao de entrada', '/Users/teste/tmp/data/teste/caixaEntrada/Relatorio.pdf', 99098, 'teste', 0, 13074, 3740130, 'U');
    
asked by anonymous 25.09.2017 / 16:48

0 answers