Posts by Sorack • 27,430 points
864 posts
-
1
votes1
answer419
viewsA: Haversine formula for SQL SERVER
To SQL Server 2008 or higher there is the type GEOGRAPHY that "translates" the latitude and longitude in a location. Once this is done you can calculate it before using for its validation. WITH…
-
1
votes3
answers438
viewsA: Over with Group by
You can use WITH nested to get the order and calculate the values: WITH sequencia AS ( SELECT ROW_NUMBER() OVER(PARTITION BY r.emp_id ORDER BY r.mes) AS sequencia, r.mes, r.credito, r.debito,…
-
6
votes3
answers144
viewsA: Help with select
Use the LEFT JOIN together with the clause CASE: SELECT f.no_equipe, h.no_pessoa_fisica, CASE WHEN a.no_cidadao IS NULL THEN 'GESTANTE NAO CADASTRADA' ELSE a.no_cidadao END AS no_cidadao, d.dt_ficha…
-
2
votes1
answer622
viewsA: Hssfworkbook - How to copy one spreadsheet to another?
I created a class to accomplish what you want based on in this answer: import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import…
-
1
votes2
answers1482
viewsA: SQL: Doubt in the Relationship between Tables
Everything related to what was/will be purchased should be inside the table ITEM_PEDIDO if related to ITEM. Thus the PRECO, how is it of ITEM and is unrelated to the request, should be placed in the…
-
0
votes3
answers1110
viewsA: How to find "holes" in SQL Server tables?
You can use the clause NOT EXISTS: SELECT t1.* FROM tabela t1 WHERE NOT EXISTS(SELECT 1 FROM tabela t2 WHERE t2.id -1 = t1.id)…
-
2
votes2
answers422
viewsA: Calculating average time
Additionally you can calculate the average directly in your query which returns the database data as follows: SELECT AVG(TIME_TO_SEC(TIMEDIFF(t.saida, t.entrada))) / 60 AS espera_media FROM tabela t…
-
2
votes1
answer107
viewsA: How to search data from a third table based on the associative table
Basically you will use the keys of the tables to link them. I have set an example without knowing the name of the columns, but it is something like: UPDATE e SET e.email = d.email FROM escola e…
-
0
votes4
answers578
viewsA: Problems with Input Angularjs
The problem is that you are using the ng-model for a temporary value, when in reality it should be the property that should mirror the value of the input: angular .module('app-pedido', [])…
-
2
votes1
answer1833
viewsA: How to get the ng-value value of checkbox in Angularjs/Ionic
I put down an example of radio using AngularJS for you to take as an example. You just need to link the ng-model with the variable you want to assign value. I noticed that you describe the behavior…
-
1
votes2
answers542
viewsA: How to convert json to object and put into a system.out.println?
The real problem is that you weren’t reading the buffer to the end. I’ve also made some improvements to your code to prevent variables from being unnecessarily created: import…
-
3
votes2
answers178
viewsA: How to update all records of all tables that have FK of a table
The best solution would be not to replicate the information to the table ItemContrato, after all this would be redundancy. The best way would be to fetch the information directly from the source…
-
3
votes4
answers865
viewsA: How to separate a string when the string does not have a separator?
You can perform the split by "no" character: String texto = "1234"; String split[] = texto.split(""); // [0] = "1" // [1] = "2" // [2] = "3" // [3] = "4"…
-
1
votes1
answer638
viewsA: Read Json without "knowing" the JAVA keys
The class JSONObject has a method called keySet listing the keys to an object: JSONObject profissoes = (JSONObject) jsonObject.get("profissao"); chaves = profissoes.keySet(); for (String chave :…
-
3
votes6
answers65532
viewsA: Sum in 2 inputs and appear in real time - Javascript
An alternative is to use a framework that automates the work for you. The following example is using the AngularJS: angular .module('app', []) .controller('SomaController', SomaController); function…
-
1
votes2
answers1913
viewsA: Query to pick text after a certain character
You can get the position of the last occurrence of >> using the function LOCATE together with the REVERSE and after that use the amount of characters found in this result to return the…
-
1
votes1
answer1072
viewsA: Create a pie chart with html and Angularjs
You can use the library Angular Chart to do what you want. I put a simple example. (function() { 'use strict'; angular .module('app', ['chart.js']); angular .module('app')…
-
1
votes1
answer637
viewsA: How to compare the values of a column with the average of these values in SQL?
You can calculate the average before and assign to a variable: DECLARE @media numeric(15, 2); SELECT @media = AVG(l.price) FROM livros l; SELECT l.* FROM livros l WHERE l.price > @media; Or…
-
0
votes3
answers62
viewsA: Get only 1 of each journey
You can use the clause NOT EXISTS to check if there are codes larger than the current one for the specific journey: SELECT tj.* FROM tabelajornadas tj WHERE NOT EXISTS(SELECT 1 FROM tabelajornadas…
-
4
votes2
answers254
viewsA: Group By different results
The problem in your query is not the GROUP BY and yes the HAVING. The GROUP BY serves to group, the HAVING makes a restriction based on some aggregate function. In the case of your second query you…
-
2
votes2
answers1015
viewsA: $injector:unpr Unknown Provider
Check the import of .js in his index.html.
-
2
votes2
answers64
viewsA: How to add up the amount of records in which the penultimate record enters the Where?
You can get the desired result by calculating the position of the record JOÃO on the daughter table. SELECT COUNT(1) FROM chamado chmd INNER JOIN (SELECT @row_number := CASE WHEN @num_chamado =…
-
1
votes1
answer30
viewsA: Delete period without tag Between
Transform the DATETIME in DATE to remove the time and use the operators maior que and menor que: SELECT * FROM tabela WHERE (CAST(x AS DATE) < CAST(HorIni AS DATE) OR CAST(x AS DATE) >…
-
1
votes1
answer62
viewsA: Evaluating the $http response from Factory to the controller in Angularjs
You can directly pass the promise and use the .catch to handle the error. Factory: //Listar..Recuperar ..Inserir ...Editar..{...} usuarioService.Excluir = function (id) { return $http({ method:…
-
0
votes1
answer159
viewsA: Problem when executing a query in a repeat loop on the Mssql Node
Use promises to resolve only when all callbacks are finalized: api.contabilizacaoItems = function(req, res, next) { var itensParaEnvio = []; var dados = req.body; var promessas = [];…
-
3
votes2
answers4660
viewsA: Is it possible to simulate safari in windows?
You can download the version 5 of Safari for Windows at this link. Apparently this will be the latest version with support for Windows. Source: Where can I download Safari for Windows?…
-
1
votes3
answers241
viewsA: Identifying SQL brothers
Perhaps the easiest way is to group by the combination of the parents' name (disregarding the combinations that are both empty). This considers the names to be unique: WITH PARENTALIDADE AS ( SELECT…
-
1
votes2
answers337
viewsA: Add WITH query inside another query - SQL Server
Just change the condition of JOIN to be part of a CASE: WITH dias AS( SELECT CAST('2017-06-26' AS DATE) AS dia UNION ALL SELECT DATEADD(DAY, 1, d.dia) FROM dias d WHERE d.dia < '2017-06-30' )…
-
1
votes4
answers32475
viewsA: Query to catch word after given character
You can use a separation function and pick up the last part of the sequence: SELECT (SELECT TOP(1) sep.item FROM separacao(s.solcaminho, ' >> ') sep ORDER BY sep.sequencia DESC) AS solcaminho…
-
0
votes3
answers640
viewsA: Grouping by day and by date period
You can use the clause WITH to isolate the days and use them in a LEFT JOIN with his query current: WITH dias AS( SELECT CAST('2017-06-26' AS DATE) AS dia UNION ALL SELECT DATEADD(DAY, 1, d.dia)…
-
3
votes2
answers512
viewsA: JSON to XLS in java
One option is to use the library Apache POI to record the XLS and the library JSON-java to read the JSON. Below is an example of the implementation of the class with the two libraries: import…
-
1
votes2
answers1303
viewsA: How to transform an Object into XML in the specified format?
I used the method SOAPHeader.addHeaderElement to create the elements that would be needed in the header. After that I did the treatment as String, after all the namespace is mandatory on certain…
-
1
votes1
answer38
viewsA: Jsoup doesn’t seem to be working
You can use the following method to find all course descriptions: public List<String> buscarCursos(String letra) throws IOException { String site =…
-
4
votes3
answers2769
viewsA: How to know if the chosen date is less than the current date?
Remove the toDateString in comparison, otherwise you will be comparing value of Strings: comparar("17/08/2017"); comparar("16/08/2017"); comparar("15/08/2017"); function comparar(strData) { var…
-
2
votes1
answer548
viewsA: How to mount an ng-repeat using "track by"?
ngRepeat - Error - dupes Duplicates in a repeater are not allowed. Use 'track by' expression to specify unique keys. Repeater: {0}, Duplicate key: {1}, Duplicate value: {2} Occurs if there are…
-
2
votes2
answers80
viewsA: Doubt select in two tables
You should wear a LEFT JOIN to search in the second table even if there are no records. After this concaten the result, if you want one row per drawer: SELECT g.nome_gaveta,…
-
2
votes1
answer343
viewsA: SQL - Delete records that have a field with the same value as another, only when there is more than one record
You can use the clause WHERE usually to check if the code is equal to the barcode and then check if there is another record with the reverse condition: DELETE t1 FROM tabela t1 WHERE t1.CodProduto =…
-
5
votes2
answers152
viewsA: How to generate multiple columns in a query from just one column?
Use the aggregation function SUM together with the structure CASE: SELECT inscricao, SUM(CASE WHEN cfop < 5000 THEN contabil ELSE 0 END) AS entrada, SUM(CASE WHEN cfop >= 5000 THEN contabil…
-
15
votes3
answers3392
viewsA: _ (underline) in consultation with LIKE
Your case SGBD be the SQL Server you need to escape the character: SELECT t.* FROM tabela t WHERE t.NM_CAMPAIGN LIKE '%[_]R[_]%' LIKE Determines whether a specific character string matches a…
-
2
votes1
answer264
viewsA: Select command to delete duplicates by summing the results
If you want to delete the smallest amount: DELETE p1 FROM produto p1 INNER JOIN produto p2 ON p1.produto = p2.produto WHERE p1.quant < p2.quant; If you want to delete the highest amount: DELETE…
-
0
votes3
answers124
viewsA: Selecting Active Batch
Use the expression WITH to prioritize prices together with the function ROW_NUMBER: WITH Prices (Id, ProdutoID, Preco, Inicio, Fim, Prioridade) AS ( SELECT lot.Id, lot.ProdutoID, lot.Preco,…
-
0
votes1
answer235
viewsA: How to pass data from one table to another with checkbox?
You only need to filter the second table according to the attribute selected in the first: angular .module('ModuloTabelas', []) .controller('ControllerTabelas', ControllerTabelas);…
-
0
votes1
answer323
viewsA: Problem getting user data in the application’s localStorage
The function localStorage#getItem returns a String, therefore it is necessary to make the conversion to object using JSON#parse: var user = JSON.parse(localStorage.getItem('user')); var name =…
-
3
votes1
answer1238
viewsA: Angularjs Filter various table fields
The $filter accepts an expression as parameters in addition to a object, so you can filter as follows: $filter('filter')(array, 'ABC'); Or in the HTML: ng-repeat="produto in listaCadProduto |…
-
1
votes1
answer242
viewsA: Error entering data into database
Your INSERT is incorrect. The correct syntax would be: "insert into Clientes(codigo_Cliente, nome_Cliente,telefone) values(?,?,?)"
-
0
votes2
answers87
viewsA: I have this query and I’m not able to group by month
The order for the execution of a query is the following: clause FROM clause WHERE clause GROUP BY clause HAVING clause SELECT clause ORDER BY So you can’t use one GROUP BY with a column named. Here…
-
1
votes3
answers2314
viewsA: How to calculate two columns and compare the result with another column?
You can accumulate the results and show them as follows: WITH calculo (montante) AS ( SELECT (SELECT SUM(qtde_pontos) FROM dbo.tbl_cc_ponto WHERE re = '9289' AND natureza_operacao = '1' AND…
-
3
votes2
answers1303
viewsQ: How to transform an Object into XML in the specified format?
I would like to generate the XML for a request that I will make on Java from an object. The XML that I want to generate is the following: <?xml version="1.0" encoding="utf-8" standalone="yes"…
-
2
votes2
answers150
views -
0
votes4
answers1722
viewsA: How to recover a value from within a tag in XML?
If your column is the type NVARCHAR or VARCHAR you can use a LIKE in consultation: SELECT SPDNFE.* FROM SPDNFE WHERE SPDNFE.DsXML LIKE '%<qVol>3</qVol>%' LIKE Determines whether a…