Posts by anonimo • 4,084 points
322 posts
-
0
votes3
answers99
viewsA: Sum of groups in an SQL
Try: SELECT S1.cd_secretaria, to_char(SUM(S2.nr_horas)*60*'1 min'::interval, 'DD HH24:MI') FROM sch_sismapa.tb_servidor S1 JOIN sch_sismapa.tb_he_norm_diurno S2 ON (S2.id_servidor = S1.id) WHERE…
-
1
votes1
answer139
viewsA: Error starting Postgresql - [FAILED]
The PostgreSQL uses the model MVCC (Multiversion Concurrency Control) for control of transactions and in it the Multixact Ids are used for lock control records. It is implemented as a 32-bit…
-
1
votes1
answer60
viewsA: Inner Join and sub-query problems
I think what you need is a correlated subquery, try: (select tblCategoria.nomeCategoria as nomeCategoria from tblCategoria where tblAnimeHentai.categoria = tblCategoria.idCategoria) as categoria, As…
-
1
votes2
answers145
viewsA: SELECT RANGE 1 IN 1 HOUR - POSTGRESQL
See if this is what you want: SELECT * FROM generate_series( (SELECT min(date_trunc('hour', time)) FROM t)::timestamp, (SELECT max(date_trunc('hour', time)) FROM t)::timestamp, '1 hour'::interval);…
-
1
votes1
answer65
views -
0
votes1
answer136
viewsA: SQL: Copy records from a column
If your table is empty then you should use an INSERT and not an UPDATE. INSERT INTO condutors_empresas(empresa_id, condutor_id) SELECT DISTINCT empresa_id, condutor_id FROM Licencas;…
-
0
votes3
answers37
viewsA: Select To view two-year billing sum
Wouldn’t it be simpler to do: SELECT SUM(CASE WHEN year(datger) = 2018 THEN VLRBRU ELSE 0 END) AS "Faturamento 2018", SUM(CASE WHEN year(datger) = 2019 THEN VLRBRU ELSE 0 END) AS "Faturamento 2019"…
-
0
votes1
answer33
viewsA: SQL: JOIN and Count Repeated Number
Just for that it is not necessary to join, just: SELECT pedido.cliente_id, count(*) FROM pedido HAVING (count(*) > 1) GROUP BY pedido.cliente_id;
-
0
votes2
answers81
viewsA: How to use WHERE function in column created from a query?
Try: WITH cte AS (SELECT campo1, replace(replace(campo1,'-',''),'.','') as resultado FROM dbo.tabela1) SELECT cte.campo1, b.campo1, cte.resultado FROM dbo.tabela2 b WHERE b.campo1 = cte.resultado;…
-
0
votes2
answers160
viewsA: How to delete duplicate lines in Postgres database?
One possibility is to use the DISTINCT ON clause to create an auxiliary table without duplicates and then replace the original table with the auxiliary. CREATE TABLE ter AS SELECT DISTINCT…
-
0
votes2
answers63
viewsA: Return Mysql result as ranking
SELECT t_login.id_login, SUM(t_fotos.pontuacao_foto) FROM t_login JOIN t_fotos ON id_login = id_login_foto ORDER BY 2, 1 DESC LIMIT 0,10;
-
0
votes1
answer378
viewsA: ERROR: Missing FROM-clause entry for table "new"?
Try: ELSIF (TG_OP = 'INSERT') THEN select dblink_exec( 'dbname=postgres hostaddr=***.**.**.*** user=*** password=*** port=5432', concat('INSERT INTO teste.teste_replica ("id", "nome") values (',…
-
1
votes1
answer900
viewsA: How do I use order by in two columns in the same table? Postgresql
If the field created is effectively TIMESTAMP then use SELECT id, title, description, city, created FROM budget WHERE created::DATE BETWEEN '2019-01-01'::DATE AND '2019-12-30'::DATE ORDER BY…
-
1
votes3
answers1548
viewsA: select text field separated by comma return lines
You can use the function array_position combined with the function regexp_split_to_array. array_position(regexp_split_to_array(seu_campo_texto), elemento_a_pesquisar) NOT NULL If the function…
postgresqlanswered anonimo 4,084 -
1
votes2
answers104
viewsA: Convert a Query with a Subquery to a Query with a Join
If you only need the data of rad_votoitem try: SELECT count(DISTINCT id_voto) FROM rad_votoitem WHERE id_alternativa IN (1068, 1061) GROUP BY id_voto HAVING count(id_voto) > 1;…
-
1
votes2
answers51
viewsA: Find out if people did not attend
From what I understand: SELECT armario.idcad FROM armario LEFT OUTER JOIN presenca ON armario.idcad = presenca.id_cad WHERE data_presenca < CURRENT_DATE()-10
-
1
votes1
answer62
viewsA: return specific content with regex - POSTGRESQL
A possibility: SELECT regexp_matches(regexp_split_to_table('12555, {memory_percent: 3.6073870265949464, name: code},10581, {memory_percent: 5.82421985505014, name: VirtualBoxVM},11518,…
-
1
votes1
answer223
viewsA: LIMIT TO TWO OUTPUT CHARACTERS WITH REGEX - POSTGRESQL
If I understand correctly your problem try: SELECT LEFT(NULLIF(REGEXP_REPLACE(temperature, '\D','','g'), ''), 2)::numeric AS "Temperature" FROM tbl_temperature_hosts WHERE temperature LIKE '%Core…
-
1
votes1
answer2432
viewsA: SQL - SELECT DISTINCT in only one field
Postgresql has the DISTINCT ON clause. For your case would be: SELECT DISTINCT ON(geo_geometria) geo_geometria, ds_camada FROM geral_elemento_cartografico WHERE ds_camada LIKE ('%MEIO%') OR…
-
1
votes1
answer388
viewsA: Multiply even index values of a string in C
No reversal is required, it is sufficient to determine whether the total digits are even or odd. #include <stdio.h> #include <string.h> int main() { char num[1024], result[2048]="",…
-
0
votes1
answer57
viewsA: I need to change a table data with information from a different table
Considering only the tables you put in the question, since your SQL command references columns that do not exist in the tables presented, or need to make a join, just use the historian table (unless…
-
1
votes2
answers43
viewsA: Search for missing records within the same table
Just use a subselect to verify the existence: SELECT * FROM registro WHERE competencia = '01/2019' AND NOT EXISTS (SELECT * FROM registro WHERE competencia = '02/2019') The above command has an…
-
0
votes2
answers1167
viewsA: Insert an empty space between a concatenation of strings in C, without the string library. h
Only concatenation can be done as follows (consider the above comment): #include <stdio.h> int main() { int i, j, k; char aux; char nome[10]; char sobreNome[10]; char concatNome[20];…
-
2
votes1
answer136
viewsA: You can analyze my C calculator and help me finish the project
Here: scanf("%c", operation); missed & before variable operation; idem in: scanf("%f", N2);. There is no sense in these switches before each case. Review the syntax of the switch/case command.…
-
0
votes1
answer163
viewsA: How to use a function to fill all positions of the dynamically allocated vector?
The second function is: int preencherVetor(int *vetor, int tamanho) { int i, soma=0; vetor = (int *) malloc(tamanho * sizeof(int)); for (i=0; i<tamanho; i++) { vetor[i] = receberInteiro(); soma…
-
1
votes2
answers519
viewsA: SQL Query average time DO NOT take into account Weekends and holidays
Here is an SQL expression, in the Postgresql DBMS, that provides the working days of a period. I considered that there is a table with the registration of holidays and other days that should not be…
-
0
votes1
answer44
viewsA: take field name in query and popular other!
I didn’t understand your construction for the value of the year and month to be searched but try to put in the list of fields of your SELECT: SUM (CASE WHEN (year(data1) = ano AND month(data1) =…
-
1
votes1
answer1605
viewsA: ERROR: more than one Row returned by a subquery used as an Expression
Kicking, since you have not provided enough information as is recommended on this site [minimum, complete and verifiable example], I think it may be: SELECT (cast(srvs AS int) - (SELECT COUNT(*)…
-
0
votes1
answer46
views -
1
votes2
answers240
viewsA: How to count repeat emails in a Mysql table?
If you just want to list the repeated ones use the HAVING clause: SELECT email, COUNT(*) FROM cadastro GROUP BY email HAVING (COUNT(*) > 1);
-
0
votes2
answers59
viewsA: Doubt Exercise - Academic
Try to use the repeat command correctly. For example.: int i, j; double soma = 0, result = 1, sinal=1; printf ("Somatorio dos termos: S = 1 + 1/3 + 1/5 + 1/7 + ............+ 1/99 \n"); for (i = 1; i…
-
0
votes3
answers212
viewsA: Relational Algebra - Query in 3 tables
Despite having studied Relational Algebra for a long time, I believe that the expression can be translated into SQL as: SELECT P.nomep from material_peca AS MP JOIN peca AS P ON P.codigop =…
-
0
votes1
answer152
viewsA: SQL query to add Rows and display in two Columns in MYSQL
Assuming that SUM1 refers to ITEMSAIDA.NCODLINHAMERC = 13 and SUM2 to ITEMSAIDA.NCODLINHAMERC = 24, because you didn’t specify the criteria for each of the sums, you can try something like: SELECT…
-
0
votes1
answer170
viewsA: How to do an automatic UPDATE after an INSERT in tables without FK in Postgresql
Do as the example below: UPDATE cnh SET pontos = cnh.pontos - OLD.pontuacao + NEW.pontuacao FROM multa, registro WHERE cnh.numero_cnh = registro.numero_cnh AND multa.numero_auto =…
postgresqlanswered anonimo 4,084 -
2
votes1
answer1049
viewsA: How to make a "scan" using SQL?
Resolve with a simple update: UPDATE tgffin, tgfnat SET tgffin.CODNAT = tgfnat.CODNAT WHERE tgfnat.AD_NAT_OLD = tgffin.AD_CODNAT_OLD;
-
1
votes2
answers118
viewsA: Last Registration by date
To bring the highest time_end of each date use: SELECT Ordem, Hora, MAX(hora_fim), status FROM sua_tabela GROUP BY Hora ORDER BY Hora; select above will only work in Mysql if SQL mode…
-
2
votes1
answer40
viewsA: How to check if the duplicate in query SQL and use another column to choose which will for final query
SELECT `nameItem`, `descItem`, MIN(`valuePrice`), `datePrice` FROM item, price WHERE item.idItem = price.fk_idItem GROUP BY `nameItem`, `descItem`
-
0
votes1
answer20
viewsA: Edit the Return of a function using postgresql
Try: CREATE OR REPLACE FUNCTION soma(numero1 int, numero2 int) RETURNS text AS $$ BEGIN RETURN 'A soma é: ' concat(numero1, ' + ', numero2, ' = ', numero1 + numero2); END; $$ LANGUAGE plpgsql;…
-
5
votes1
answer43
viewsA: Loop is not being completed
The way you declared the X array has no known N value at the time of the declaration. Declare the array after reading the N variable. If you start considering smaller as the first element of the…
-
0
votes1
answer76
viewsA: Postgres - lo_import - import user’s machine directory image
Really command INSERT runs on the server and it assumes that the file resides on the server. To send a file resident on the client machine to the server use, for example. the command \lo_import…
-
0
votes1
answer29
viewsA: Update to an A table of distinct numbers using table B of repeated numbers
I believe the command below meets your needs: UPDATE NUMD set H00 = H00 + I.H00, H01 = H01 + I.H01, H02 = H02 + I.H02, H03 = H03 + I.H03, H04 = H04 + I.H04, H05 = H05 + I.H05 FROM #TBLtmp I WHERE…
sql-updateanswered anonimo 4,084 -
1
votes1
answer101
viewsA: Select to join two Father and Son tables one below the other
Use the UNION operator: SELECT * FROM ( SELECT chave, Data_Venda, Qtde_Produto, Valor_Produto FROM Tabela_Pai UNION SELECT chave, Data_Venda, Qtde_Produto, Valor_Produto FROM Tabela_Filho ) temp…
-
2
votes3
answers196
viewsA: How to select to find a record between two values?
I believe the query below meets: SELECT juros FROM sua_tabela WHERE 2500 BETWEEN valor_min AND valor_max;
-
0
votes1
answer331
viewsA: Select mysql with COUNT and GROUP BY MONTH
Try with a merge between tables: SELECT MONTH(COALESCE(consultas1.data, consultas2.data)) as MES, COALESCE(COUNT(consultas1.id_consulta), 0) as TOTAL1, COALESCE(COUNT(consultas2.id_consulta), 0) as…
-
0
votes1
answer89
viewsA: Subquery with mysql with two tables
From what I understand of your problem, try: SELECT projeto.codProj, projeto.titulo, AVG(trabalhaem.horas) FROM projeto INNER JOIN trabalhaem ON (projeto.codProj = trabalhaem.codProj) HAVING…
-
0
votes2
answers43
viewsA: Mysql Help with INNER JOIN
Name of the science teacher SELECT * FROM teacher WHERE subscription='ciencias'; The quotes were missing. List of names of all the science teacher’s students, taking into account that the only data…
-
-1
votes3
answers607
viewsA: Declaring a string in C
The way you did you’re assigning to the variable nome the variable carlos. I believe it’s not what you want. In C the function is used strcpy, of <string.h>, to copy strings. Study on array…
-
0
votes1
answer115
viewsA: Enter lower case/upper case in C
Use the function toupper of <ctype.h>. After your scanf do: p = toupper(p); that p will be capitalized regardless of whether it has been typed in upper or lower case.…
-
1
votes1
answer110
viewsA: While with logical operator "or" - C++
Misparenting: while((strcmp(continuar, "YES")!=0) || (strcmp(continuar, "NOT")!=0)){ and I believe that the OU in this case will not meet what you expect since if it is either "YES" or "NOT" will…
-
7
votes2
answers127
viewsA: Search last access record with MYSQL
Use the GROUP BY clause with the MAX aggregation function: SELECT Cliente, MAX(Data_Registro) FROM clientes GROUP BY Cliente ORDER BY Cliente;