Gerar E3Browser/E3Chart a partir de um SQL

Nay · 22 October 2024 16:16

Boa tarde,

Preciso criar um gráfico com a curva média dos intervalos 1 e 2 conforme script abaixo.

WITH Intervalo1 AS (
SELECT
ROW_NUMBER() OVER (ORDER BY [TagTimeStamp]) as NumId,[TagName], [TagTimeStamp],[TagValue]
FROM [SinterHistoryA].[dbo].[TagHistory]
WHERE
TagTimeStamp >= ‘2024-05-10 10:14:43.997’
AND TagTimeStamp <= ‘2024-05-10 11:07:17.997’
AND TagName = ‘PIT19’
),
Intervalo2 AS (
SELECT
ROW_NUMBER() OVER (ORDER BY [TagTimeStamp]) as NumId,[TagName], [TagTimeStamp],[TagValue]
FROM [SinterHistoryA].[dbo].[TagHistory]
WHERE
TagTimeStamp >= ‘2024-05-10 14:01:38.997’
AND TagTimeStamp <= ‘2024-05-10 14:51:08.997’
AND TagName = ‘PIT19’
)
– Consulta principal usando os CTEs
SELECT
i1.NumId AS Intervalo1_NumId,
i1.TagName AS Intervalo1_TagName,
i1.TagTimeStamp AS Intervalo1_TagTimeStamp,
i1.TagValue AS Intervalo1_TagValue,
i2.NumId AS Intervalo2_NumId,
i2.TagName AS Intervalo2_TagName,
i2.TagTimeStamp AS Intervalo2_TagTimeStamp,
i2.TagValue AS Intervalo2_TagValue,
CASE
WHEN I1.TagValue IS NOT NULL AND I2.TagValue IS NOT NULL THEN (I1.TagValue + I2.TagValue) / 2.0
ELSE NULL
END AS media_valor

FROM Intervalo1 i1
FULL OUTER JOIN Intervalo2 i2
ON I1.NumId = I2.NumId

Este script SQL foi criado na aba SQL do objeto de consulta do Elipse E3 e na aba visualizar é possível visualizar a consulta com os resultados, porém não fica disponível para eu selecionar o campo da coluna para o item na aba do Eixo Vertical e Eixo Horizontal.

Tem como eu adicionar este campos para ser possível adicionar nos campos do eixo horizontal e vertical?

Ao clicar no OK da janela de consulta apresenta um erro: Ocorreu um erro na geração desta consulta. Verifique a configuração do objeto E3Query em uso.

Alguém já precisou fazer algo parecido e poderia me ajudar?

leandrobpedro · 24 October 2024 11:48

Bom dia. Acredito que a função ROW_NUMBER() não seja suportada pelo Jet SQL. Neste caso, se você esitver utilizando um banco SQL Server, crie um Stored Procedure para lidar com isso em T-SQL.

Criar procedure no banco de dados.

CREATE PROCEDURE sp_ObterMediaIntervaloPIT19
    @DataInicio1 DATETIME,
    @DataFim1 DATETIME,
    @DataInicio2 DATETIME,
    @DataFim2 DATETIME
AS
BEGIN
    -- Intervalo 1 com numeração sequencial usando ROW_NUMBER
    WITH Intervalo1 AS (
        SELECT
            ROW_NUMBER() OVER (ORDER BY [TagTimeStamp]) AS NumId,
            [TagName], 
            [TagTimeStamp], 
            [TagValue]
        FROM 
            [SinterHistoryA].[dbo].[TagHistory]
        WHERE
            [TagTimeStamp] >= @DataInicio1
            AND [TagTimeStamp] <= @DataFim1
            AND [TagName] = 'PIT19'
    ),
    
    -- Intervalo 2 com numeração sequencial usando ROW_NUMBER
    Intervalo2 AS (
        SELECT
            ROW_NUMBER() OVER (ORDER BY [TagTimeStamp]) AS NumId,
            [TagName], 
            [TagTimeStamp], 
            [TagValue]
        FROM 
            [SinterHistoryA].[dbo].[TagHistory]
        WHERE
            [TagTimeStamp] >= @DataInicio2
            AND [TagTimeStamp] <= @DataFim2
            AND [TagName] = 'PIT19'
    )

    -- Consulta principal usando os CTEs
    SELECT
        i1.NumId AS Intervalo1_NumId,
        i1.TagName AS Intervalo1_TagName,
        i1.TagTimeStamp AS Intervalo1_TagTimeStamp,
        i1.TagValue AS Intervalo1_TagValue,
        i2.NumId AS Intervalo2_NumId,
        i2.TagName AS Intervalo2_TagName,
        i2.TagTimeStamp AS Intervalo2_TagTimeStamp,
        i2.TagValue AS Intervalo2_TagValue,
        CASE
            WHEN i1.TagValue IS NOT NULL AND i2.TagValue IS NOT NULL THEN 
                (i1.TagValue + i2.TagValue) / 2.0
            ELSE 
                NULL
        END AS media_valor
    FROM 
        Intervalo1 i1
    FULL OUTER JOIN 
        Intervalo2 i2
    ON 
        i1.NumId = i2.NumId;
END;

Consultar pelo Elipse

EXEC sp_ObterMediaIntervaloPIT19 
    '2024-05-10 10:14:43.997', 
    '2024-05-10 11:07:17.997', 
    '2024-05-10 14:01:38.997', 
    '2024-05-10 14:51:08.997';

Nay · 26 October 2024 14:49

Consegui entrar em contato com a Elipse e por uma uma interpretação do software era necessário inserir um espaço a mais entre o ORDER BY, portanto consegui plotar os gráficos na tela porém na hora de passar para o relatório o meu E3Chart4 não plota o gráfico.

O código está conforme abaixo:

Sub OnBeforePrint
Dim IQ, Ficha, connect, SQL, rs, dbconn
Dim hrInicialHora, hrFinalHora, DATA
Dim Data_Ini(3), Data_Fim(3)
Dim contador
Dim SQLIntervalos, SQLConsulta, SQLMedia, SQLOn, SQLCompleta

selectedOS = Application.GetObject("Dados.AplicacaoExecutavel.Menu.OS").Value
selectedIQ = Application.GetObject("Dados.AplicacaoExecutavel.Menu.IQ").Value

' Configuração do E3Chart1 - Gráfico de vazão na Caixa de vento
Set Chart1 = Report.Sections("Detail").Controls("E3Chart1")
Set Consulta1 = Chart1.Queries.Item("Consulta1")

Set Chart1 = Report.Sections("Detail").Controls("E3Chart1")
Consulta1.SetVariableValue "selectedOS", selectedOS
Consulta1.SetVariableValue "selectedIQ", selectedIQ

Consulta1.SQL = _
"SELECT '1' AS seq, 'T1' AS ponto, UMID_REAL_JPU_T1 AS umidade, " & _
"VAZAO_CX_VENTO_JPU_T1 AS vazao FROM SINTER_IQ " & _
"WHERE OS = '" & selectedOS & "' AND IQ = '" & selectedIQ & "' " & _
"UNION ALL " & _
"SELECT '2' AS seq, 'T2' AS ponto, UMID_REAL_JPU_T2 AS umidade, " & _
"VAZAO_CX_VENTO_JPU_T2 AS vazao FROM SINTER_IQ " & _
"WHERE OS = '" & selectedOS & "' AND IQ = '" & selectedIQ & "' " & _
"UNION ALL " & _
"SELECT '3' AS seq, 'T3' AS ponto, UMID_REAL_JPU_T3 AS umidade, " & _
"VAZAO_CX_VENTO_JPU_T3 AS vazao FROM SINTER_IQ " & _
"WHERE OS = '" & selectedOS & "' AND IQ = '" & selectedIQ & "' " & _
"UNION ALL " & _
"SELECT '4' AS seq, 'T4' AS ponto, UMID_REAL_JPU_T4 AS umidade, " & _
"VAZAO_CX_VENTO_JPU_T4 AS vazao FROM SINTER_IQ " & _
"WHERE OS = '" & selectedOS & "' AND IQ = '" & selectedIQ & "' " & _
"ORDER BY seq;"

Consulta1.Execute()
Chart1.LoadData()

' Configuração do E3Chart2 - Gráfico de JPU/Umidades
Set Chart2 = Report.Sections("Detail").Controls("E3Chart2")
Set Consulta2 = Chart2.Queries.Item("Consulta1")

Set Chart2 = Report.Sections("Detail").Controls("E3Chart2")
Consulta2.SetVariableValue "selectedOS", selectedOS
Consulta2.SetVariableValue "selectedIQ", selectedIQ

Consulta2.SQL = _
"SELECT '1' AS seq, 'T1' AS ponto, UMID_REAL_JPU_T1 AS umidade, " & _
"JPU_T1 AS jpu FROM SINTER_IQ " & _
"WHERE OS = '" & selectedOS & "' AND IQ = '" & selectedIQ & "' " & _
"UNION ALL " & _
"SELECT '2' AS seq, 'T2' AS ponto, UMID_REAL_JPU_T2 AS umidade, " & _
"JPU_T2 AS jpu FROM SINTER_IQ " & _
"WHERE OS = '" & selectedOS & "' AND IQ = '" & selectedIQ & "' " & _
"UNION ALL " & _
"SELECT '3' AS seq, 'T3' AS ponto, UMID_REAL_JPU_T3 AS umidade, " & _
"JPU_T3 AS jpu FROM SINTER_IQ " & _
"WHERE OS = '" & selectedOS & "' AND IQ = '" & selectedIQ & "' " & _
"UNION ALL " & _
"SELECT '4' AS seq, 'T4' AS ponto, UMID_REAL_JPU_T4 AS umidade, " & _
"JPU_T4 AS jpu FROM SINTER_IQ " & _
"WHERE OS = '" & selectedOS & "' AND IQ = '" & selectedIQ & "' " & _
"ORDER BY seq;"

Consulta2.Execute()
Chart2.LoadData()

Dim GRAFICOS (1)

'Definindo nome Controles Gráficos no Relatório

GRAFICOS(0) = "E3Chart3"
GRAFICOS(1) = "E3Chart4"

' ---------------------------------- DECLARAÇÃO INCLUÍDA-------------------------------------------
' Pega os valores de IQ e Ficha da interface
IQ = Application.GetObject("Dados.AplicacaoExecutavel.Menu.IQ").Value
Ficha = Application.GetObject("Dados.AplicacaoExecutavel.Menu.IDFicha").Value

' Conexão ao banco de dados
connect = Application.GetObject("Dados.Geral.str_con_SinterDB").Value
Set dbconn = CreateObject("ADODB.Connection")

idxGrafico = 1

For Each grafico In GRAFICOS
	' ---------------------------------- FOR INCLUÍDO-------------------------------------------
		'Definindo as variáveis por gráfico - Grafico 1
		If idxGrafico = 1 Then
				Dim VARIAVEIS(8)
				' Populando o vetor do 1
				VARIAVEIS(0) = "FIT20C"
				VARIAVEIS(1) = "FIT20N"
				VARIAVEIS(2) = "FIT20N_JPU"
				VARIAVEIS(3) = "PIT19"
				VARIAVEIS(4) = "TIT01"
				VARIAVEIS(5) = "TIT02A"
				VARIAVEIS(6) = "TIT02B"
				VARIAVEIS(7) = "TIT02C"
				VARIAVEIS(8) = "TIT03"	
		End If

		'Definindo as variáveis por gráfico - Grafico 1
		If idxGrafico = 2 Then
				' Populando o vetor do 1
				VARIAVEIS(0) = "NO"
				VARIAVEIS(1) = "SO2"
				VARIAVEIS(2) = "CO"
				VARIAVEIS(3) = "CO2"
				VARIAVEIS(4) = "O2"	
				VARIAVEIS(5) = ""
				VARIAVEIS(6) = ""
				VARIAVEIS(7) = ""
				VARIAVEIS(8) = ""	

		End If	
   numConsulta = 1
   
		For Each variavel In VARIAVEIS
		
		'Valores Iniciais
		contador = 1
	
			dbconn.Open connect

			' Consulta SQL para obter NQ, DATA, HR_INICIAL, HR_FINAL
			SQL = "SELECT NQ, FORMAT(HR_INICIAL, 'HH:mm:ss') AS HR_INICIAL_HORA, FORMAT(HR_FINAL, 'HH:mm:ss') AS HR_FINAL_HORA, " & _
				  "CONVERT(VARCHAR(10), DATA, 103) AS DATA " & _
				  "FROM SINTER_NQ WHERE IQ = '" & IQ & "' AND ID_FICHA = '" & Ficha & "'"

			' Executa a consulta
			Set rs = dbconn.Execute(SQL)

			' Verifica se retornou resultados
			If Not rs.EOF Then

				' Loop para processar os dados
				Do While Not rs.EOF
					hrInicialHora = rs("HR_INICIAL_HORA")
					hrFinalHora = rs("HR_FINAL_HORA")
					DATA = rs("DATA")

					Datainicial = "datainicial" & contador
					Datafinal = "datafinal" & contador

					' Concatena data e hora e ajusta a hora inicial (subtrai 3 minutos)
					Application.GetObject("Dados.AplicacaoExecutavel.Resumo_Queimas.HoraInicial").Value = DATA & " " & hrInicialHora
					Application.GetObject("Dados.AplicacaoExecutavel.Resumo_Queimas.HoraFinal").Value = DATA & " " & hrFinalHora

					' Converte as strings de data e hora para objetos de data
					Datainicial = CDate(Application.GetObject("Dados.AplicacaoExecutavel.Resumo_Queimas.HoraInicial").Value)
					Datafinal = CDate(Application.GetObject("Dados.AplicacaoExecutavel.Resumo_Queimas.HoraFinal").Value)

					' Converter e armazenar DataInicial e DataFinal no formato desejado
					Data_Ini(contador) = FormatDateTime(Datainicial, 0)  ' Formato padrão (geral)
					Data_Fim(contador) = FormatDateTime(Datafinal, 0)    ' Formato padrão (geral)

					' Ajustar para o formato específico yyyy/dd/MM HH:mm:ss
					Data_Ini(contador) = Year(Datainicial) & "-" & _
										 Right("0" & Month(Datainicial), 2) & "-" & _
										 Right("0" & Day(Datainicial), 2) & " " & _
										 Right("0" & Hour(Datainicial), 2) & ":" & _
										 Right("0" & Minute(Datainicial), 2) & ":" & _
										 Right("0" & Second(Datainicial), 2)

					Data_Fim(contador) = Year(Datafinal) & "-" & _
										 Right("0" & Month(Datafinal), 2) & "-" & _
										 Right("0" & Day(Datafinal), 2) & " " & _
										 Right("0" & Hour(Datafinal), 2) & ":" & _
										 Right("0" & Minute(Datafinal), 2) & ":" & _
										 Right("0" & Second(Datafinal), 2)

					contador = contador + 1
					rs.MoveNext
				Loop
			End If
		
			'Adequando valor contador
			contador = contador - 1

			' Fecha o Recordset e a conexão
			rs.Close
			dbconn.Close
			
	If variavel <> "" Then
			If contador >= 2 Then
				'Definição Intervalos
				SQLIntervalos = "WITH Intervalo1 AS ( " & _
								" SELECT " & _
								" ROW_NUMBER() OVER (ORDER  BY [TagTimeStamp]) as NumId ,[TagName], [TagTimeStamp],[TagValue] " & _
								" FROM [SinterHistoryA].[dbo].[TagHistory] " & _
								" WHERE " & _
								" TagTimeStamp >= '" & Data_Ini(1) & _
								"' AND TagTimeStamp <= '" & Data_Fim(1) & _
								"' AND TagName = '" & variavel & "')"

				For index = 2 To contador
					SQLIntervalos = SQLIntervalos & ", Intervalo" & index & " AS (" & _
								   " SELECT " & _
								   " ROW_NUMBER() OVER (ORDER  BY [TagTimeStamp]) as NumId,[TagName], [TagTimeStamp],[TagValue] " & _
								   " FROM [SinterHistoryA].[dbo].[TagHistory] " & _
								   " WHERE " & _
								   " TagTimeStamp >= '" & Data_Ini(index) & _
								   "' AND TagTimeStamp <= '" & Data_Fim(index) & _
								   "' AND TagName = '" & variavel & "')"
				Next

				'Definição Consulta
				SQLConsulta = " SELECT " & _
							  " i1.NumId AS Intervalo1_NumId," & _
							  " i1.TagName AS Intervalo1_TagName, " & _
							  " i1.TagTimeStamp AS Intervalo1_TagTimeStamp," & _
							  " i1.TagValue AS Intervalo1_TagValue,"

				For index = 2 To contador
					SQLConsulta = SQLConsulta & " i" & index & ".NumId AS Intervalo" & index & "_NumId," & _
										   " i" & index & ".TagName AS Intervalo" & index & "_TagName," & _
										   " i" & index & ".TagTimeStamp AS Intervalo" & index & "_TagTimeStamp," & _
										   " i" & index & ".TagValue AS Intervalo" & index & "_TagValue,"
				Next

				'Calculo Media
				SQLMedia = " CASE WHEN i1.TagValue IS NOT NULL"

				For index = 2 To contador
					SQLMedia = SQLMedia & " AND i" & index & ".TagValue IS NOT NULL"
				Next

				SQLMedia = SQLMedia & " THEN (i1.TagValue"

				For index = 2 To contador
					SQLMedia = SQLMedia & " + i" & index & ".TagValue"
				Next

				SQLMedia = SQLMedia & ")/" & contador & ".0 ELSE NULL END AS media_valor"

				'Definição OUTER JOIN
				SQLOn = " FROM Intervalo1 i1"
				For index = 2 To contador
					SQLOn = SQLOn & " FULL OUTER JOIN Intervalo" & index & " i" & index & " ON i1.NumId = i" & index & ".NumId "
				Next
				SQLOn = SQLOn + " WHERE i1.NumId IS NOT NULL"
				
				SQLCompleta = SQLIntervalos & SQLConsulta & SQLMedia & SQLOn & ";"
				
			 Else
				SQLCompleta = " SELECT"&_
				"ROW_NUMBER() OVER (ORDER  BY [TagTimeStamp]) AS Intervalo1_NumId, [TagName], [TagTimeStamp], [TagValue] AS media_valor"&_
				" FROM [SinterHistoryA].[dbo].[TagHistory]"&_
				" WHERE"&_
				" TagTimeStamp >= '" & Data_Ini(1) &_
				"' AND TagTimeStamp <= '" & Data_Fim(1) & _
				"' AND TagName = '" & variavel &"'" 'Acrescentada a Variável

			End If
			
				' Configuração do grafico - Gráfico Médio da Queima
				Set Chart3 = Report.Sections("Detail").Controls(grafico)
				Set Consulta3 = Chart3.Queries.Item("Consulta" & numConsulta)	
				
				'Screen.Item(grafico).Item(numConsulta).SQL = SQLCompleta
				Consulta3.SQL = SQLCompleta
				Consulta3.Execute()
				Chart3.LoadData()
				
				'MsgBox "Grafico: " & grafico &" - Consulta: " & "Consulta" & numConsulta &" - Variavel: " & variavel & " idxGrafico = " & idxGrafico
				'Application.Trace(SQLCompleta) 				
				numConsulta = numConsulta + 1
		End If
		Next ' Este Next se refere ao loop For Each
	idxGrafico = idxGrafico + 1
Next

End Sub

Alguém teria uma ideia de como resolver este problema?