Posts by rLinhares • 7,797 points
331 posts
-
2
votes1
answer91
viewsA: Sum return of select
I don’t quite understand why it brings different results by just removing the grouping, but if you turn it into a subconsultation must receive the correct return: SELECT customer_id,…
-
1
votes1
answer111
viewsA: Bring only one product per affiliate to sql server
The query below should bring you what you need: SELECT ID, CODFIL, max(DT) FROM X GROUP BY ID, CODFIL Thus, you will have only the longest date for each returned product.…
-
2
votes1
answer48
viewsA: Search for NULL mysql via Java
Change the completion of String sql for String sql = "UPDATE usuario_acesso SET data_saida = ? WHERE usuario_nome = ? AND data_saida IS NULL"; As stated in the commentary, the "comparison" should be…
-
3
votes2
answers2740
viewsA: Select the first and last day of the previous month
Considering the query you already have, just search the same data subtracting from the date a month: select ADDDATE(LAST_DAY(SUBDATE(CURDATE(), INTERVAL 1 MONTH)), 1) primeiro_dia,…
-
1
votes6
answers12886
viewsA: Doubt about logical operators && e || in Java
Split: aquaName != null will be true if aquaName is not null, i.e., has any value ("test", "", ", etc.") !aquaName.getText().toString().isEmpty() will be true if it is empty (on behalf of !). Using…
-
1
votes2
answers690
viewsA: SQL - group by, Count
From what I understand, you can know which vehicle made the most trips. So, play this search in a subconsultation and the main consultation is done on top of the id of the vehicle (detail, the query…
-
0
votes2
answers39
viewsA: How to insert for all users in PLSQL when there is no data?
If I understand correctly, you will only create type 130 if it does not exist on the base. So, one IF solves the problem: IF NOT EXISTS (SELECT * FROM USUARIO WHERE tipo = '130') BEGIN INSERT INTO…
-
5
votes3
answers203
viewsA: How to show a column, but if null show another sql
As commented in the question itself, it can be done using the COALESCE: SELECT id, COALESCE(nome_resumido, nome) as nome FROM nome_tabela It is also possible to use the IFNULL: SELECT id,…
-
3
votes1
answer50
viewsA: SQL Problems with query
The problem is that you are not adding up the total nights; what you are doing is just listing the nights and the customers (so if the customer has 10 reservations, it will be displayed 10 times).…
-
3
votes3
answers399
viewsA: How to select the last rows added in a mysql table
I believe this consultation should bring you what you need: SELECT C1.idterreno, C1.idusuario, C1.data FROM sir_ter_conversa C1 WHERE C1.id = (SELECT max(C2.id) FROM sir_ter_conversa C2 where…
-
2
votes3
answers393
viewsA: Get last ID or date inserted in a Mysql table
The idea is that you bring everything at once? The code below brings the most current date, grouping by the id of the conversation and the user: SELECT idconversa, idusuario, max(data) FROM conversa…
-
0
votes1
answer25
viewsA: Subselect Orderby first line
The problem is that you cannot use the order by on an undercurrent. That question of soen is more or less the same point as its. Actually "Ordering" only makes sense on the outermost query That is,…
-
2
votes4
answers1474
viewsA: Remove text within column in MYSQL
Considering the response of @Diego (that was the one that came closest to my understanding) and the comment on the tag have a variable value, use the following code: UPDATE produtos SET obs = ' '…
-
1
votes1
answer65
viewsA: Mvc List only available rooms and not busy ones
Assuming that the date of departure will always be greater that of the input, you can use the following: SELECT DISTINCT Quarto.ID_Quarto FROM Quarto Q1 LEFT JOIN Reserva R1 ON Q1.ID_Quarto =…
-
1
votes2
answers780
views -
0
votes3
answers402
viewsA: How to do an operation on a Trigger? MYSQL
I believe that will solve your problem: create trigger AUMENTARSALARIO after insert on EMPREGADOS for each row begin update EMPREGADOS set SALARIO = SALARIO * 1.1 where SALARIO < 900; end; You…
-
0
votes1
answer77
viewsA: Fetch last recorded date in sql cpf_cnpj
I suggest you use the structure below: SELECT cpf_cnpj, aplicado, data_aplic, IS_NULL((SELECT MIN(data_aplic) FROM tabela T2 WHERE T1.cpf_cnpj = T2.cpf_cnpj AND T1.data_aplicada <>…
-
0
votes1
answer627
viewsA: OPENROWSET - How to assign a variable instead of the file name to open?
Use the CONCAT; you would have something like: declare @nome_arquivo varchar(255) select @nome_arquivo = '252203408.xlsx' SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', CONCAT('Excel 12.0…
-
2
votes1
answer38
views -
1
votes1
answer2683
viewsA: Date Shorter and Longer than X days Mysql
You can use the DATE_SUB, as below; SELECT * FROM registro WHERE v.date > (DATE_SUB(CURDATE(), INTERVAL 3 DAY)) AND v.date < (DATE_SUB(CURDATE(), INTERVAL 30 DAY))…
-
1
votes1
answer252
viewsA: SQL returning error when selecting non-existent field
Based in that question of SOEN, is possible, but not directly. It suggests checking if the column exists and only then run the select: DECLARE v_column_exists number := 0; BEGIN SELECT count(*) INTO…
-
1
votes2
answers136
viewsA: Replace a fixed CASE value with a SELECT and JOIN?
What you want is possible, yes; even you already make a subconsultation. SELECT DISTINCT P.CD_PACIENTE -- demais campos , (CASE WHEN UI.CD_UNID_INT = (SELECT MAX(S_2.CODIGO) from SETOR S_2 where…
-
0
votes1
answer221
views -
1
votes1
answer298
viewsA: Scanf function causing infinite loop
The code has no stopping condition. From what I understand, you want to leave the loop when the user type 0. while (1) { printf("Enter a character: "); if (scanf("%c", &c) == 0) { printf("Err");…
-
2
votes3
answers2216
viewsA: SQL - Bring only 1 year records back
If you only need the customer’s name, you may not return the other field. When you bring the data also in consultation, the same customer with different dates is returned. SELECT DISTINCT p.ID,…
-
0
votes2
answers477
viewsA: Condition in Trigger
In the sql-server you can use IF, that is, the structure of proc can be maintained, and where you left "reserved" for -- CONDICAO, between the following: IF @ID IS NOT NULL BEGIN ... END…
-
2
votes1
answer1158
viewsA: List tables that have Trigger
I found this answer in a question from SOEN, I believe it will solve you: SELECT t.name AS TableName, tr.name AS TriggerName FROM sys.triggers tr INNER JOIN sys.tables t ON t.object_id =…
-
5
votes2
answers1867
viewsA: View with double SELECT from the same table
From what I understand of the question, you can test the query as below: SELECT ZS.Referencia, ZF1.Doc_orig, ZF2.Referencia FROM ZSF053 ZS JOIN ZFI003 ZF1 ON ZS.Referencia = ZF1.Referencia JOIN…
-
2
votes1
answer2571
viewsA: Reset AUTO INCREMENT and organize item ID
To regularize your database, you need to update the data already registered, as well suggested @Sveen. SET @count = 0; UPDATE `tabela` SET `tabela`.`id` = @count:= @count + 1; Done this, you need to…
-
0
votes2
answers1079
viewsA: How to disable Trigger for all tables in Postgresql?
ALTER TABLE tblname DISABLE TRIGGER USER This should solve your problem. It has been marked as accepted in this answer of SOEN; there are some more alternatives, in case you want to take a look.…
-
3
votes2
answers1011
views -
1
votes3
answers536
viewsA: How to show quantity of sales per month payment in SQL Server
You could achieve the expected result with the following query: SELECT MONTH(DataVenda) AS 'Mes', (SELECT COUNT(*) FROM vendas V2 WHERE V2.DataVenda > '2018-01-01' AND MONTH(V2.DataVenda) =…
-
1
votes1
answer22
viewsA: Postgresql query bringing more results than expected
From what I saw the mistake is in the second join, the selected fields are not those indicated. Try it this way: SELECT DISTINCT(matricula.mat_id) FROM matricula INNER JOIN professor_turma ON…
-
1
votes2
answers271
viewsA: How to eliminate duplicate lines without using distinct?
I suggest you create a new temporary table to control what will be displayed or not, as this cannot be done by Id. IF object_id('tempdb..#tmpHistorico') IS NOT NULL BEGIN DROP TABLE #tmpHistorico…
-
1
votes2
answers57
viewsA: Sort VARCHAR database records with semicolon
Whereas your notation is the Brazilian that the point would only be for formatting, you can remove it in the sort: SELECT *, CAST(representantes_vendas.valor AS INT) FROM representantes_vendas INNER…
-
1
votes1
answer42
viewsA: Mysql conditional average (avg)
In the mysql you can use the ifnull(): SELECT AVG(IFNULL(fraction, 0)) FROM table…
-
1
votes2
answers107
viewsA: Bring another field in group by
I found a similar question in the SOEN and second she just put the fields directly in the group by: var grupo = from item in aliquotaProduto group item by new { item.CFOP_ID, item.CAMPO_CATEGORIA,…
-
1
votes2
answers186
viewsA: Delete folder with CHMOD777 permission
I went to check on PHP manual and saw that to delete the folder, "the directory has to be empty and the relevant permissions authorize this operation". In the manual itself there is an example of…
-
0
votes2
answers222
viewsA: Format year on Sqlite
The suggestion of Giovanni It’s interesting, it doesn’t suit you? I don’t know a way "direct" bring the year with only two characters (e.g. yy or %y), then my suggestion would be to go around it and…
-
4
votes2
answers473
viewsA: Regular expressions C++
Test that code: string palavraRetorno = ""; string palavra = "concept:personasia:toby_pizur:personasia:teste"; int indice = palavra.LastIndexOf(':'); if (indice >= 0) palavraRetorno =…
-
2
votes2
answers433
viewsA: SQL server data default
I believe it is enough for you to change the value that is being assigned as default: declare @dataDefault datetime select @dataDefault = convert(datetime, '1990/01/01') create table Coordenador (…
sql-serveranswered rLinhares 7,797 -
1
votes1
answer77
viewsA: Command similar to sql Convert in c#
Whereas the value received from comic is a timestamp, you need to convert it into date and then display in the format corresponding to the 103 of sql (dd/mm/yyyy): string valorSql = "52,451541554";…
-
0
votes1
answer66
viewsA: Foreach in fields
If I understood the idea correctly, the logic in foreach is a little confused. Admitting that in the variable $input you own several addresses, and within the addresses, several involved, I believe…
-
2
votes3
answers103
viewsA: Use BETWEEN together with IN()
It is "possible", but to do this you need to isolate the expressions: SELECT * FROM Conteudo WHERE campo_data BETWEEN DATE(DataInicio) AND DATE(DataFim) AND campo_data IN('2018-03-22','2018-03-23')…
-
5
votes2
answers9612
viewsA: Select that takes the last 30 days from the current date
Use the DATE_ADD(): WHERE viacao_os.creation_date BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY) AND CURRENT_DATE() Possibly your query works with the -20 for being on the day 21, that is,…
-
2
votes1
answer38
viewsA: Query to return the value that comes before a specific character
Follows the idea commented by Rovann, uses the position(): SELECT substr (coluna_answer, (POSITION(")" IN coluna_answer) - 1), 1) THE answers…
-
4
votes1
answer251
viewsA: Sub select with COUNT and SUM
You need to remove the e1.numeroserie of the query (since it is unique) and group by the other columns: SELECT s1.suprimento, s1.capacidade, e1.numeronotafiscal, e1.valorunitario,…
-
0
votes3
answers931
viewsA: Sql Help that queries 2 fields from the same table
Like coluna1 and coluna2 are primary and foreign key in tabelaX and tabela2, respectively, you need to use these fields in JOIN: SELECT tx.id as idTabela1, t2.id as idTabela2, tx.coluna1 as coluna1,…
-
3
votes2
answers75
viewsA: Error when doing sum inside foreach
The mistake is that you are going through the valordespesas but always adds up the same value; you need to add som, nay valor: foreach (float som in valordespesas) { somadesp += som; }…
-
3
votes3
answers328
viewsA: Format integer value in "000" format
The right way to do what you want is by using the PadLeft(): string valor = "1"; valor.PadLeft(3, '0');…