Posts by Clarck Maciel • 947 points
57 posts
-
-1
votes1
answer44
viewsA: Digital catalog
You can do this if both in the programming language and in the SQL query language. In SQL: Select @estoque := 1 as Estoque, IF(@estoque>0, "Disponível", "Indisponível") as Disponibilidade;…
-
0
votes1
answer30
viewsA: Trigger to devalue apartment price if garage linked to it is excluded
First, it is necessary to know what a Trigger. Trigger is a trigger programmed to be triggered when a particular event occurs in a specific database object. CREATE TRIGGER desv_apartamento AFTER…
-
0
votes1
answer29
viewsA: SQL Server - Function to format date field with Convert command
When using the function convert, is using a date display style. Recommended is to use varchar in this situation to present the information. To manipulate the record with the type DATE, must…
-
0
votes1
answer26
viewsA: Changing primitive fields and constraints Modify with MARIA DB
Your command: alter table pessoa modify column profissao varchar (25) not null default ''; The problem is trying to apply the restriction not null in a table that already has in this field a value…
-
0
votes1
answer47
viewsA: Turn specific rows into columns
In the situation where you have a history table, basically, we will have a table with 3 fields: ID, Datarecord, Observation; These fields will answer the questions: With which record happened? When?…
mysqlanswered Clarck Maciel 947 -
0
votes1
answer34
viewsA: UNION is not consolidating SOMA between 2 Postgresql tables
The UNION should not be used to "consolidate sum", but to join sets. After joining, you can use Group By to group the results and function sum to add the desired fields. The ideal is to seek to…
-
-2
votes1
answer92
viewsA: Specific join between tables
You can use nested consultations, following the next two steps: First: A consultation should be made that results in a preliminary set that I will call groundwork of the desired information;…
-
2
votes1
answer51
viewsA: Set age from date of birth in another table
CREATE VIEW IdadeFuncionarios AS SELECT TIMESTAMPDIFF(YEAR, P.`data_Nascimento`, NOW()) FROM funcionario F, pessoa P INNER JOIN `pessoa` ON (F.`cpf_funcionario` = P.`cpf`); The problem here is at…
-
5
votes1
answer68
viewsA: Prevent the insertion by the user of the same number of CPF in three different fields
As @Motta suggested, one possible solution is to perform control through Trigger. CREATE TRIGGER ValidaTresCPF BEFORE INSERT ON Agenda FOR EACH ROW begin DECLARE msg VARCHAR(255); IF…
-
1
votes3
answers721
viewsA: Select Distinct is bringing duplicate Mysql data
I need to bring only the last registration of each id_face that has on table images but for some reason is bringing duplicate id_faces Just add to your where a clause that will limit the last record…
-
-1
votes1
answer42
viewsA: Update with subconsultation and Inner Join - Mysql
The idea is to update the campanha_id field of contracts using a subconsulta and Inner Join The question is: How to update the campanha_id field of all contracts returned in the query ? Adjust your…
-
0
votes1
answer31
viewsA: Trigger to check for data in another table
As indicated: The Trigger will be in the Table Carrier, and need only check if in City.codcidade, there is some value equal to the Transport.City_codcity, which I am inserting. Just check with the…
-
-1
votes2
answers63
viewsA: How to open a form behind an already opened Delphi form
Use the Popupmode and Popupparent property of forms. Consider 3 Forms: Main form is the main form; Formbackend is the form that should be Modal and stay behind Formcadastro; Formcadastro is the form…
-
0
votes1
answer70
viewsA: Update using different record from the same table
The problem is that in your command of update subconsulta is returning more than 1 record as reported in the error message. To correct, you will need to relate the sub-allowance in such a way that…
-
0
votes1
answer39
viewsA: Error accessing state change that has a country
I don’t know if it’s right, but from what I understand, something in the country is coming empty. How do I fix this. I’m not getting. The error message displayed: Comand [Qpaises] text must be not…
-
3
votes1
answer43
viewsA: 'End of statement expected' pyCharm error
The problem is that the assignment of the value of the global variable is on the same line. The right thing would be: state = False def change_state(x): global state state = not x print(state)…
-
3
votes2
answers81
viewsA: LIMIT 1 with LEFT JOIN
I believe the following command elucidates the problem. I did not use Limit 1. I used what is most common in these cases, which is to use a sub-consumption to restrict the result as mentioned in the…
-
0
votes1
answer37
viewsA: Subquery Delete in Mysql
I need to delete this user’s 2 records. The problem is that like the user column id does not have Primary Key, Mysql does not allow for safe update account. I needed to get around this without…
-
0
votes1
answer68
viewsA: How to insert the average of a table into another table?
It is possible to do through a update as follows: Update historico set nota_final = (Select (NVL(m.nota1,0)+NVL(m.nota2,0)+NVL(m.nota3,0))/3 from matricula m where m.cod_aluno = historico.cod_alun…
-
0
votes1
answer77
viewsA: Calculate the time difference without giving the date
The problem is that you are working with different dates and wanting to do the calculations only with the hours. Considering the problem at hand, considering your words: I would like to know for…
-
-1
votes1
answer68
viewsA: How to unify the results of Count POSTGRES?
Since the goal is: Unifying the results of Count Since your query is already resulting in the partial information you need, just perform the grouping of this partial result. One possibility is to…
-
0
votes1
answer24
viewsA: <table> does not display the data I am passing
Note the section of the method getAllAmericaSul: ResultSet rs = stmt.executeQuery("select nome_pais, tipo_medida, total_focos, ano_medida FROM fato f INNER JOIN apaises p ON p.id_paises =…
javaanswered Clarck Maciel 947 -
0
votes1
answer35
viewsA: Table does not display data
The bugs are in the codes you’re doing nome_continente = America do Sul. public List<Paises> getAllAmericaSul() { List<Paises> listaAmericaSul = new ArrayList<Paises>(); try {…
-
1
votes1
answer73
viewsA: Calculate Average by SQL SERVER periods
Use the Clause OVER(): The Clause OVER() used in conjunction with the PARTITION BY divides the result of Select on partitions allowing you to use functions that, without this artifice, required the…
-
0
votes1
answer40
viewsA: How to group the records by year?
Create a variable to store the current year $current_year = ""; and print only if (if) verify that the registration in question is different from the year previously printed. Follow suggested code:…
-
0
votes1
answer32
viewsA: sum values in subquery by field
I need to sum the remaining amount of each loan in a subquery called "remainder", but what happens is that it adds up the value of all loans. I need you to show only the sum of each number loan.…
mysqlanswered Clarck Maciel 947 -
1
votes1
answer87
viewsA: "ORA-00955: name is already used by an existing Object" error
Can someone help me find the mistake? The error is that there is already a table with this name TB_PROVA . Run to check if the table already exists: Select * from TB_PROVA; To recreate a table, you…
-
0
votes1
answer44
viewsA: How to bring, in different columns, values from the same column of another table, with different conditions
The goal is to bring the contacts of each participant, who today are contact table for this new query table. This query can be performed in several ways, I will demonstrate two ways, the first…
mysqlanswered Clarck Maciel 947 -
0
votes2
answers60
viewsA: SQL Server Questions When Adding Shift 3 (SUM)
The problem is that shift 3 ends the next day (starts at 22hrs and ends 6:00 the next day) To solve this problem, consider that the date datetime must be manipulated in a way that "seems" to belong…
-
0
votes2
answers58
viewsA: Recover database data on Django
Use: dados = Dados.objects.all().values_list for d in dados: print(d) Another option is to use the values(): dados = Dados.objects.all().values() See the possibility to use __str__() Example of…
-
0
votes1
answer25
viewsA: How to Join with 3 tables in Mysql
Perform the query as follows: select `u`.*, `p`.*, `o`.* from `pack` as `p` inner join `order` as `o` on `p`.`id` = `o`.`id_pack` inner join `user` as `u` on `p`.`id_user` = `u`.`id` WHERE…
mysqlanswered Clarck Maciel 947 -
1
votes1
answer38
viewsA: I need to show a dataset in MYSQL
About the View Need to pull the amount of matches played, amount of goals scored, create an average goal per match (average goals = Number of matches / Number of goals) all in one View According to…
-
0
votes1
answer39
viewsA: SQL VIEW Query
Purpose: I need to know the amount of records per person month and year to be defined and in the query below I have the result expected. So far all ok. Since in its consultation, it has already…
sql-serveranswered Clarck Maciel 947 -
0
votes1
answer45
viewsA: How to return the result of a function to a python file
The code needs several adjustments, let’s see: The function inventario() ends in the return (codprod, qt, motivo, user), then the lines of code after the return need to be at the beginning of the…
-
0
votes1
answer24
viewsA: Call variable in another main
You need to take the List<CadastroCliente> clientes = new ArrayList<>(); from within the method cliente(), as written, the variable clientes is with local visibility, ie only within the…
javaanswered Clarck Maciel 947 -
0
votes2
answers467
viewsA: How to check query with empty return?
How can I check if a query is null and assign a value to when it has multiple columns? You can check multiple columns using the CASE WHEN as an example below. Let us consider that the outworking of…
sqlanswered Clarck Maciel 947 -
-1
votes2
answers92
viewsA: Problem in SQL search
In this solution I tried to offer 3 resolution options, being them: Solution Proposition 01 - With GROUP BY Solution Proposition 02 - With Sub-Consumption Proposed Solution 03 - With CASE WHEN The…
-
0
votes1
answer43
viewsA: Select only returns result in terminal
I believe that the problem is in getting the connection, because as you said yourself, the query is working when consulting through the terminal. To solve: Change the form of connection by informing…
-
0
votes2
answers108
viewsA: SQL query to bring the last occurrence
To perform this query, think of set theory where you already have an initial set and need to select specific items from that set. I suggest two solutions: Using sub-consumption based on the result…
-
2
votes1
answer96
viewsA: What is the best way to store Civil Status data in the database?
My question is whether I should directly insert the HTML (example: Single(a) and etc). Or, if I should create a table in the database called state_civil, and insert data as id = 1, description =…
-
0
votes1
answer22
viewsA: View in SQL ERROR
Is there any other way I can get these results? Yes, another way to bring this result would be by using sub-consultations as an example below: SELECT c1.id_ativo_externo, c1.id_obra, c2.codigo_obra,…
mysqlanswered Clarck Maciel 947 -
2
votes1
answer70
viewsA: Double quotes when exporting files
Does anyone have any idea why this happens ? I found that this problem happens because in the source file there are two characters that we are not seeing at first, but are present. To see these…
mysqlanswered Clarck Maciel 947 -
0
votes2
answers77
viewsA: Group a conversion list within SELECT
Your query has been adapted to generate a column named as codigos_conversao. For the "Those who do not have conversion code show blank" criterion Isnull was used (content,'') The field has been…
-
0
votes1
answer86
viewsA: How do I lower a csv file of 3 GB, to be able to import in pandas in google colab?
I suggest you download an application that aims to split a giant CSV file into small ones pieces, that is, in several smaller CSV files that will have a number of lines defined by you. I suggest the…
-
1
votes1
answer49
viewsA: String Concat with Join Sqlserver
To get the desired result, just insert a second DISTINCT in consultation as follows: SELECT DISTINCT SUBSTRING ( ISNULL ( STUFF ( ( SELECT DISTINCT convert(varchar(10), C.nomeRazaoSocial) + ',' AS…
-
1
votes2
answers76
viewsA: How to mount this query
Assuming your database is Postgresql, I propose the SQL query below: SELECT distinct cs.idclienteunidade from clienteunidadeservico cs where cs.anocobranca = 2021 and cs.mescobranca = 4 and not…
-
0
votes1
answer67
viewsA: How to concatenate a result of a case with other fields?
The items to be added or concatenated must be of the same type as the value to be returned by the excerpt case...when, I made an example using the cast to explain the desired type. In your case, the…
-
0
votes1
answer46
viewsA: SQL Server - insert data after comparison of 2 tables
I suggest you carry out the command case ... when to generate the output columns you need, as you can perform the when as an example: Select * into Final from ( SELECT p.produto, case when ((preco…
-
0
votes1
answer41
viewsA: Java - Bluej giving error return Error found in class
The Problem The message indicates that there are errors in the class. Listing some existing errors in the class Line 37: valorPorExtenso += " e " + getExtenso1000("centavos", "unidade", " dezena");…
-
0
votes2
answers42
viewsA: sum of values of more than one table
The Problem The problem lies in the form in which the left join is being employed, since the relationship 1 for many between the various united relationships leads to the repetition of values…
sqlanswered Clarck Maciel 947