Posts by Sorack • 27,430 points
864 posts
-
0
votes1
answer68
viewsA: Transcribing from Mysql to SQL
Basically just use the function DATEPART to check what time your DATETIME and so categorize according to what you want. I made a more simplified example below because I did not understand its…
-
0
votes1
answer440
viewsA: Can I work with array in SQL SERVER?
Create a table type variable to store the name of the banks; Enter the names of the banks you want to select values; Scroll through the table with a cursor; Use the function EXEC to execute a…
-
3
votes1
answer481
viewsA: Create JSON files relating arrays
Separate cities by groups using the function reduce resulting in the creation of a Map grouping cities by state; Walk the array of states obtaining all the cities of each of the Map previously…
-
0
votes2
answers446
viewsA: How to use reserved words in SQL
At first the word STATUS is not a reserved word of SQL Server as you can see in reserved keywords list. I tested in the db<>fiddle and your example (without <<>>) works normally.…
-
0
votes2
answers151
viewsA: Axios Node Put with authentication does not access
All indicates the server you are trying to access uses a smaller TLS version than supported by your version of Node.js. Since the v11.4.2 the minimum TLS version is TLSv1.2. To run your code, try to…
-
1
votes1
answer133
viewsA: Variable Node.js takes Undefined value
You are trying to perform an asynchronous function (async) without waiting for the result, so its console.log is showing a Promise. To wait for the resolution of the promise, you can use the…
-
1
votes2
answers484
viewsA: How to use multiple answers for the same variable in just one if?
You can use a ternary conditional operator to achieve the desired result (although you don’t think it’s the most readable): nome === null ? alert('Você clicou em cancelar') : (nome === ? alert('seu…
-
3
votes1
answer187
viewsA: Resultset Return null in Java
First on its query you must assign a alias to the column: ResultSet rs = stmt.executeQuery("SELECT to_regclass('public.clientes_alter_log') AS existencia")); Then check whether the column value is…
-
2
votes1
answer228
viewsA: Javascript - Calculation of school grades with object use, is it possible?
You can use a function reduce allied to the destruction of the object: const alunos = [] const calcular = ({ nota1, nota2, nota3, nota4 }) => [nota1, nota2, nota3, nota4].reduce((total, nota)…
javascriptanswered Sorack 27,430 -
1
votes2
answers165
viewsA: GROUP BY WITH ORDER BY
Use the clause NOT EXISTS to return only results that do not have another record with the same tipo but with codigo minor. Schema (Mysql v5.7) CREATE TABLE tabela ( id INT AUTO_INCREMENT PRIMARY…
-
1
votes1
answer30
viewsA: Migrate data from one DB1 table to another DB2 database table
Ordering the data does not matter, you must explicitly define the order you want using the clause ORDER BY in the SELECT. To insert from one bank to another you perform the insertion in this way:…
sql-serveranswered Sorack 27,430 -
0
votes2
answers187
viewsA: Nodejs query in SQL Server with Like
Utilize Template String to more easily view the error in your query: router.get('/materialNome/:material', (req, res) =>{ let filter = ''; if(req.params.material)filter= ` WHERE NOME_SERVICO LIKE…
-
2
votes1
answer90
viewsA: SQL Server - Return only the searched word
You can create a function that counts the word occurrences you want: CREATE FUNCTION contar_ocorrencias(@palavra VARCHAR(30), @frase VARCHAR(100)) RETURNS INT BEGIN DECLARE @inteiro INTEGER; DECLARE…
-
0
votes1
answer28
viewsA: how to use the SELECT statement to be inserted in a column in the same table?
You must make a UPDATE for that reason: UPDATE nametosplit SET alias = SUBSTRING(names, 10, 50);
-
3
votes1
answer91
viewsA: Group array with javascript
Adapting this answer to the question Grouping objects of an array by equivalent dates for your need we have the following functions: let dados = [{ codigo: '0341', codigo_1: '0320', visao: '14',…
-
1
votes1
answer75
viewsA: Check all the requests
Just create a middleware and assign it in the function app.use: const validar = (req, res, next) => { if (!req.session.uniqueId) { res.redirect('/'); return; } next(); }; app.use(validar);…
-
0
votes1
answer203
viewsA: Count separate records with filtering condition in another field
Use a DISTINCT allied with the COUNT: SELECT COUNT(DISTINCT PDV) FROM table WHERE desc = 'Automa' COUNT This function returns the number of items found in a group. DISTINCT The DISTINCT keyword…
-
2
votes3
answers249
viewsA: DOUBT IN SQL -TURN ROWS INTO COLUMNS
If the version of yours Oracle really is the 11g use the function LISTAGG: SELECT CODPROD, LISTAGG(EMB, ', ') WITHIN GROUP (ORDER BY EMB) AS EMBALAGEM FROM TABELA WHERE PRODUTO = 2 GROUP BY CODPROD…
-
3
votes2
answers69
viewsA: How do I extract the number of orders with 5 different items? I have this query but it is coming out a different result than expected
If you only want the order quantity that has more than 5 items, just use one subquery that counts the amount of itens_dopedido and be compared with the number you want: SELECT COUNT(1) AS quantidade…
-
0
votes2
answers90
viewsA: Function to queue walk in the Array, first turns the last in Javascript
Based on the answers to the question "How to access a circular array?" we can reach a function that uses the function shift of array to always return a copy with the different orders: const circular…
javascriptanswered Sorack 27,430 -
0
votes2
answers460
viewsA: Take the 2 position of a string in an array
Just use a map to scroll through item by item from array and then, in each item, use the function split to catch the last group separated by /: const links = ['https://www.devs.com/produto/chave1',…
javascriptanswered Sorack 27,430 -
0
votes1
answer89
viewsA: Nodejs + mssql how to do a search sequence
The .then in the sql.connect and pool.request().query indicate that your returns are promises. So your code runs asynchronously. To run them in an organized way you can use the async/await as…
-
2
votes1
answer79
viewsA: Grouping objects of an array by equivalent dates
Adapting the answer to the question "how to group by and sum array of Object?" for your example, we can use the function reduce of array to obtain an object with keys according to the field you…
-
0
votes2
answers212
viewsA: How to Keep the values of a variable, even when restarting the program, in Javascript
You can use a library like the cacache to save information via value key. For example: const cacache = require('cacache'); cacache.put('caminho-para-meu-arquivo', 'teste', '1'); And to read the…
-
7
votes3
answers2052
viewsA: Convert a comma-separated string to a multidimensional array
One way to accomplish the necessary transformation is to follow the following steps: Use the function split to separate the results by ,; Perform the function map in the array resulting to remove…
-
2
votes3
answers216
viewsA: As a search for any term removing spaces, traces and points with LIKE
The first suggestion I give is that you create a function to remove special characters from the text: DELIMITER $ CREATE FUNCTION remover_especiais(texto VARCHAR(20)) RETURNS VARCHAR(20) BEGIN…
-
3
votes2
answers420
viewsA: The Datediff function resulted in an Sql Server overflow
DATEDIFF ... To according to, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds. As the documentation itself says, the maximum difference between two dates in seconds is…
-
0
votes1
answer175
viewsA: Self Join subquery approach
The first step is to sort the columns by grouping by usuarioId, dia and turno using a CASE to define the description that will be used in the columns later. Then use the PIVOT to turn descriptions…
-
1
votes2
answers100
viewsA: Remaining minutes between field and dateAtual
The DATEDIFF will not work for your case because, according to documentation, the result value is expressed in days, so it will not be possible to check the difference in minutes. An alternative is…
-
0
votes1
answer52
viewsA: I want to display a value according to the current date
In free translation your mistake is as follows: Only an expression can be specified in the selection list when the subconsultation is not entered with EXISTS. That is, you are searching for more…
-
2
votes1
answer788
viewsA: Group different values of days per month (Oracle)
You can simply use your query above within another grouping together with the SUM: SELECT x.mes, SUM(x.qtd) AS qtd FROM ( SELECT TO_CHAR(a.dt_atendimento, 'MM') AS mes, TO_CHAR(a.dt_atendimento,…
-
1
votes1
answer73
viewsA: How to select the highest value of the following query?
Just use the above query as subquery: SELECT MAX(x.total) FROM ( SELECT SUM(DISCIPLINA.VALOR) + MATRICULA.VALOR AS total FROM ALUNO, MATRICULA, MATDISCIPLINA, DISCIPLINA, SEMESTRE WHERE…
-
2
votes3
answers124
viewsA: Mysql Inner Join... Column error 'Id_occurrence' in field list is ambiguous
As the error message already says the column id_ocorrencia is defined ambiguously, that is, it exists in more than one table and you are not defining from which table the database should obtain the…
-
4
votes3
answers61
viewsA: Query SQL Doubt - Query using two tables
One way you have to do it is to realize subqueries to calculate the purchase and sale and then use a GROUP together with the SUM in the result: SELECT x.pais, SUM(x.vendas) AS vendas, SUM(x.compras)…
-
2
votes3
answers903
viewsA: How to assign Fetch value to a variable
Since your code is asynchronous, to ensure that the value of the variable is populated you must do it within the then. Currently there is the option of using the async/await also ensures that the…
javascriptanswered Sorack 27,430 -
1
votes2
answers193
viewsA: SQL Compare values in other rows
You can use a subquery to search for the next record. This way also there is no danger of breaking the logic if there is a "hole" in the sequences: SELECT t.*, (SELECT FIRST 1 t2.data_entrada FROM…
-
4
votes3
answers144
viewsA: Return last contributor who checked the car
From the MySQL 8.0 you can follow the following steps: Add an incremental column to link events according to the history. To do this use the function ROW_NUMBER linking the columns IdCarro and…
-
1
votes2
answers7751
viewsA: ORA-06502 - string buffer too small numerical or value - Long Field
Your column ds_evolucao is the type LONG and when you try to convert to VARCHAR2 without specifying the size, the database tries to convert first to CLOB which is larger than the reported size. To…
-
3
votes1
answer1240
viewsA: Convert Unix Timestamp to Datetime
Just use the value you have, in seconds, added to day 01/01/1970: SELECT DATEADD(SECOND, Column_Name, CAST('1970-01-01 00:00:00' AS DATETIME)) FROM Table_Name WHERE Condição What is the Unix time…
-
4
votes3
answers37
viewsA: List of vehicles that are without photos
You just need the clause NOT EXISTS: SELECT * FROM tbVeiculo v WHERE NOT EXISTS(SELECT 1 FROM tbImagemVeiculo iv WHERE iv.VeiculoId = v.VeiculoId) And to check by image type, make a CROSS JOIN with…
-
2
votes1
answer110
viewsA: Difficulty creating a mysql query
You can use the clause NOT EXISTS to check in the table tarefa which TipoTarefa has no record (using CROSS JOIN with the table quarto to realize the bond of all TipoTarefa with all the quarto):…
-
1
votes2
answers817
viewsA: Update to numbering in column order
Utilize ROW_NUMBER: UPDATE x SET x.CODIGOVENDA = x.NOVO_CODIGOVENDA FROM ( SELECT ROW_NUMBER() OVER (ORDER BY CODIGOVENDA) AS NOVO_CODIGOVENDA FROM VENDAS V ) x WHERE x.CODIGOVENDA IS NOT NULL;…
-
1
votes1
answer37
viewsA: Count twice the number of records from one column to different values using the COUNT(*) function in a single query
Utilize CASE: SELECT U.nome AS usuario, U.email, M.titulo AS _midia, T.tipo, S.nome AS _secao, COUNT(CASE WHEN A.`type` = 1 THEN 1 END) AS acessos, COUNT(CASE WHEN A.`type` = 2 THEN 1 END) AS…
-
1
votes3
answers34
viewsA: How do I filter a time coming through a select
You can use the function DATE_FORMAT to display the data you want to date in any way you want: SELECT DATE_FORMAT(colName,'%H:%i:%s') AS horario FROM tempo DATE_FORMAT Formats the date value…
-
0
votes1
answer168
viewsA: Is there an equivalent to SQLSERVER’s @ROWCOUNT in Postgres?
You can use the instruction GET DIAGNOSTICS after your main instruction to get the number of affected lines. For example: DO $$ DECLARE linhas INTEGER; BEGIN UPDATE tabela SET valor = 'valor'; IF…
-
4
votes1
answer1084
viewsA: Return value of Promise
You can simply use a map to transform each function call getReader in a promise and then use the function Promise.all together with the await to get a array of values: const promessas =…
-
3
votes2
answers1109
viewsA: Insert error: There are more Columns in the Insert statement than values specified in the values clause
As the error already says (in free translation): Error in the INSERT: There are more columns in the instruction INSERT than values specified in the clause VALUES In the fields of his INSERT you…
-
0
votes1
answer35
viewsA: Problem with Controllers
Your options to check sane middlewares therefore must be declared next to the routes: application.post('/noticias/salvar', [ check('titulo','Título é obrigatório').not().isEmpty(),…
-
0
votes2
answers53
viewsA: Learning Javascript and Nodejs
const mysql = require('mysql'); const { promisify } = require('util'); const connConfig = require('./dbConfig'); const connection =…
-
1
votes1
answer55
viewsA: Problem in the Result
Apparently you are not following the example available in the documentation of express-validator. In it you can check that your if error checking shall be as follows: if(!errors.isEmpty()) { return…