Posts by Heitor Scalabrini • 781 points
52 posts
-
0
votes2
answers65
viewsA: Statement update SQL
To make a UPDATE in the column IMPLANT table PEOPLE, with the parole of MES table EXIT, you need to perform a UPDATE with parole IN: UPDATE pessoas SET implante = true WHERE pessoas.prontuario IN…
-
2
votes1
answer28
viewsA: Query to insert into a Sqlserver table
To enter a specific data use the INSERT: INSERT INTO nome_da_tabela (id,nome,nascimento) VALUES (000015,'José',2001) To view all data from a table use the SELECT: SELECT * FROM nome_da_tabela…
-
1
votes2
answers53
viewsA: How to transform a numeric value into CURRENCY FORMAT (Brazilian Real) in SELECT MYSQL
I did it that way: SELECT res.Total, CONCAT('R$ ' , FORMAT(res.Total, 2, 'de_DE')) AS Valor FROM ( SELECT ( (SELECT SUM(`valor`) AS Total FROM `lancamentos`) - (SELECT SUM(`valor`) AS Total FROM…
-
0
votes1
answer47
viewsA: table with calculated field
I recommend using a Trigger that when inserting any new field, or updating any data the database automatically calculates or recalculates the data from that column: --A trigger irá executar toda vez…
mysqlanswered Heitor Scalabrini 781 -
0
votes1
answer40
viewsA: SQL Server Query
What you need to do is just cross all the tables, and then bring the results as requested. Using the above tables, create a query that links a store to the corresponding region(s): Select…
-
1
votes2
answers57
viewsA: Condition Se in Excel
In the case of IF in Excel the construction is like this: =SE([CONDICIONAL];[RESULTADO CASO VERDADEIRO];[RESULTADO CASO FOR FALSO) Example: =SE(1=1;"Igual";"Diferente") The result of the SE will…
excelanswered Heitor Scalabrini 781 -
1
votes1
answer184
viewsA: INNER JOIN of tables does not appear the values of their columns
I recommend not using the quotes on ON of SELECT, and when you list all the tables at once and you don’t find the result you probably don’t have any item that is in all the tables, because not all…
mysqlanswered Heitor Scalabrini 781 -
0
votes2
answers790
viewsA: How to get max() and min() data in SQL?
Convert the field to the type DATE: SELECT MIN(CONVERT(DATE, LEFT(Data, 10), 103)) AS Data_Minima, MAX(CONVERT(DATE, LEFT(Data, 10), 103)) AS Data_Maxima FROM Dados_Compras…
-
1
votes1
answer35
viewsA: Return value that is not in the other table
I recommend that you cross tables through the JOIN, Besides being easier to read the code is faster. The way you are currently seeking is this: FROM sol_com sc INNER JOIN ord_com oc ON sc.cd_sol_com…
-
0
votes1
answer38
viewsA: Assign values from a select to a variable
First you have to declare the Codconta, then do two "SELECT INTO": --RECEBE CodConta para realizar o filtro DECLARE CodConta DECIMAL DEFAULT 0; --VALOR CONTA SELECT Valor INTO valorConta FROM Contas…
-
0
votes1
answer40
viewsA: How to fix the error ? Error happens when I am trying to insert data into the course table
When you created the table COURSE, defined that the id_course would be a field AUTO_INCREMENT: CREATE TABLE CURSO ( CODIGO INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, -- Código interno (PK) When you…
-
1
votes2
answers106
viewsA: SQL search between two tables with duplicate result
The problem lies in this line of code: V_CUSTOMER.CUSTOMER_ID IN (Select cus.id as customer_id From BUSINESSDATA_1SAT.CUSTOMER cus WHERE V_CUSTOMER.CUSTOMER_ID = cus.ID) At no time before in the…
-
0
votes1
answer50
viewsA: Sum of two different tables msql php
The easiest way is to already calculate in your SQL command. After creating the connection with the bank do the following: Create an SQL query that brings the sum to the $resultado_query variable:…
-
0
votes2
answers137
viewsA: Issue advanced MYSQL queries
To solve this type of problem in a clear way it is necessary to cross with the table status_os twice, because? In the first step you will bring all the fields and bring the MAX(cd_status) at this…
-
0
votes3
answers405
viewsA: Trigger that inserts automatic value in a record
I believe you’re making a mess between UPDATE and INSERT, in case the field already exists you want to change it to’S', in case you have to use the UPDATE: CREATE OR REPLACE TRIGGER…
-
0
votes1
answer105
viewsA: DELETE IN SQL WITH WHERE STATEMENT NO ID REQUIRED
The error is stating that there is a foreign key connection to a primary key of a table called LOG_FAIRO. To solve this problem it is necessary to delete the information containing the same BAI_NU…
-
0
votes1
answer38
viewsA: Mysql - merge queries into different tables
What you can do is create a SUBSELECT for each table "simulating" as an existing table and cross them through the OUTER JOIN (this is a type of Join that brings the result of the two tables), see…
-
3
votes1
answer238
viewsA: Table creation error (ORA-00904)
You cannot create a table with a column called USER, because this way the bank confuses itself with a language command, in order to create the column use the quotation marks "USER", as an example…
-
1
votes2
answers62
viewsA: Union of select’s to return only 1 query
It is noticeable that the COD_REPRESENTANTE is always the same and you intend to search for 3 information from the same code only in different tables, I recommend using the concept of SUBSELECT and…
-
0
votes1
answer67
viewsA: Update with JOIN with 2 tables together with Select
There are several ways to achieve a UPDATE with INNER JOIN, the easiest and error-avoidance method is to perform a SELECT as if it were a crossing of tables and bring the value Ancient and the New…
-
0
votes1
answer282
viewsA: I want to total the multiplication of two columns
I recommend using the concept of SUBSELECT. Follow the example: SELECT CODIGO, CODIGO_BARRAS, NOME, (QUANTIDADE * CUSTO) AS TOTAL FROM( SELECT ID_PRODUTO AS CODIGO, BARRAS AS CODIGO_BARRAS, PRODUTO…
-
0
votes1
answer92
viewsA: Select Lotofácil games that must contain dozens of specific
In that case you’re stating that all columns from Bol1 to Bol15 must be either 11 or 17 or 21. You won’t be able to solve this problem this way. Create 3 validation columns (for each number) with…
-
0
votes1
answer73
viewsA: SUM() in a clause Where - Mysql
Use the sub-select is the best option for this type of problem: SELECT res.* FROM ( SELECT DISTINCT `contract`.*, SUM(invoice.value) as amount FROM`tb_contract` `contract` LEFT JOIN `tb_invoice`…
mysqlanswered Heitor Scalabrini 781 -
0
votes1
answer136
viewsA: Subquery with left Join
Analyzing your case I believe that it is not necessary to use the Subquery, but I would recommend creating the code as follows: SELECT CODPROD, CARACT FROM PRODUTOS WHERE CODPROD NOT IN (SELECT…
-
0
votes1
answer411
viewsA: What is the solution to the error below?
Have you ever tried to do something like, using the TO_DATE? --COM TRUNC (DAY) SELECT 'OK' AS RESULTADO, TRUNC(sysdate, 'DAY') AS SYS_DATE, TRUNC(TO_DATE('18/03/2020','DD/MM/YY'), 'DAY') AS…
oracle11ganswered Heitor Scalabrini 781 -
0
votes1
answer99
viewsA: Insert blank line in vba email code
Use the command vbCrLf: texto = "linha1" & vbCrLf & "linha2" msgbox texto
excelanswered Heitor Scalabrini 781 -
0
votes1
answer41
viewsA: Store Procedure with SELECT and with data make an INSERT
You need to use the SELECT INTO, for example: SELECT dot.* INTO tabela_final FROM dados_outra_tabela dot Organize the data of SELECT equal to the data in the final table (in which case it is the…
sql-serveranswered Heitor Scalabrini 781 -
1
votes1
answer87
viewsA: I need the GROUP BY clause read after HAVING
If you want to perform one command before the other in it SELECT I would recommend using the concept of SUBSELECT: --Segundo SELECT SELECT DISTINCT resultado.* FROM ( --Primeiro SELECT SELECT p.id,…
-
2
votes2
answers76
viewsA: Display values in one table but not in the other
I’ll give you some tips, first you should organize your code leave well structured so that you can read the information more easily: SELECT cbal.NúmeroConta, cbal.Descrição, forn.Nome,…
-
0
votes1
answer253
viewsQ: Help with Trigger Mutating
I am creating a Rigger that needs to be fired through a table, even creating a COMPOUND TRIGGER it accuses error of mutating, I am aware that I cannot perform a SELECT of the table I am firing to…
-
1
votes1
answer107
viewsA: INNER JOIN data mixed in php + json
I suggest perfecting your code SQL: SELECT tt.idticket, ttp.codprd, tp.nomefantasia, tp.preco1, tt.idcartao FROM TTICKET tt INNER JOIN TTICKETPROD ttp ON ttp.idticket = tt.idticket INNER JOIN…
-
3
votes1
answer100
viewsA: Include the value of a variable within another string
Use the concatenator &. Do the following: Dim name As String Set name = "Bruno" ActiveWorkbook.SaveAs Filename:= _"\Colegio\compartilhamentos\secretaria\aluno - " & name & ".xlsx"…
excel-vbaanswered Heitor Scalabrini 781 -
0
votes1
answer39
viewsA: How do I print data by data in sql?(selects all data, but only prints one by one in array format)!
Do it: $sql = "SELECT * FROM perguntas ORDER BY id_pergunta"; $execsql = mysqli_query($conn, $sql) or die("Erro"); while($row = mysqli_fetch_array($execsql)){ # code... $IdPergunta =…
-
1
votes1
answer521
viewsQ: SQL - Function to calculate working hours considering business hours, working days and holidays
I need a function to calculate working hours considering business hours, working day and holidays, I have searched in several websites and can not find any function that contain all these…
-
2
votes1
answer521
viewsA: SQL - Function to calculate working hours considering business hours, working days and holidays
I created a function with all these features that can be useful for several people, even more that it contains different examples of data processing for the conversion of dates:…
-
1
votes2
answers89
viewsA: Can you help me optimize this SQL query?
The reason for the slowness is that for each row of your select you are running 4 selects. Imagine you run 4 subselect times 10thousand lines... I would create a trial, or include these steps in the…
-
0
votes1
answer1236
viewsA: Sum values with PROV
Very simple to solve, use the SOMASIS: =SOMASE(Alimentos!J:J;A3) With this formula you will sum all the values of column J (in case the value 7 of procv) in the Food! tab that contain the value of…
-
0
votes1
answer244
viewsA: Do you have a better way to format value with space after the symbol of the coin?
I did it as follows, using the FM to clear the spaces: SELECT 'R$ '||to_char('1235675430,12','FM999G999G999G999D99') FROM DUAL;
-
1
votes1
answer114
viewsA: How do you translate this English excel formula into Portuguese?
I believe error is occurring due to quotation marks, where it has to be in this format: "" I did it here and it worked, I just switched the quotation marks and the ninbox for 2:…
-
0
votes2
answers37
viewsA: Roolback in Mysql 5.7 Workbench does not work! Help
You are probably using tables in the format Myisam that does not support transactions. Instead use table in format Innodb. How to create tables in this format: CREATE TABLE t1 (a INT, b CHAR (20),…
-
0
votes1
answer162
viewsA: Loop in Email with VBA
This is the structure I use to create email with Excel: Sub EnviarEmail() Application.ScreenUpdating = False Dim finalRow As Integer Dim Email_Subject, Email_Send_From, Email_Send_To, _ Email_Cc,…
-
2
votes1
answer40
viewsA: Get 12 months of table date for group by and continue taking data from another table with variable date using LEFT JOIN
Do the following: SELECT MONTH(b.fulldate) AS MES_DATE, YEAR(b.fulldate) AS ANO_DATE, COUNT(a.demissao) AS adm_1 FROM dates AS b LEFT JOIN cadastro_funcionarios_connect AS a ON MONTH(a.demissao) =…
mysqlanswered Heitor Scalabrini 781 -
0
votes1
answer342
viewsA: Read data from an Excel cell to search via SQL
To concatenate this type of information it is necessary to use the vba: Sub Download_Standard_BOM() 'Inicializando variaveis Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim…
-
1
votes3
answers249
viewsA: DOUBT IN SQL -TURN ROWS INTO COLUMNS
You need to use the PIVOT, it will turn rows into columns, for example: Select CODPROD , [1] as coluna_um , [2] as coluna_dois , [3] as coluna_tres from TABELA pivot (Embalagem for Emb…
-
1
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
In fact you are doing some things wrong, in Where you are comparing product name_a random sum. You also need to use the requested itens_do_table, I made a very simple example with a subselect, I…
-
1
votes1
answer557
viewsA: Case in Where for query filter
The problem is that SQL does not support conditional IS NULL or NOT NULL referenced from a case, the most it does is the following: SELECT * FROM atendimento atd WHERE atd.cd_paciente > (case…
-
0
votes1
answer98
viewsA: SQL Error no Postgres
The problem is in this section: WHERE v.id = ( SELECT DISTINCT r.cavalo FROM lg_relacao_veiculo r WHERE ( r.cavalo = (SELECT mv.gr_veiculo_id FROM mt_solicitacao_manutencao_veiculo mv WHERE…
-
0
votes1
answer56
viewsA: SQL Server: Access tables in [Cg]
I recommend that you check the table privileges with the following command: SELECT GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE FROM ALL_TAB_PRIVS WHERE AND TABLE_NAME = 'nome da tabela'; If you find…
-
0
votes1
answer1044
viewsA: ORA-01722: invalid number
A practical solution is to use the following command after Insert: SELECT ID INTO ID_OCORRENCIA FROM OCORRENCIA WHERE CLASSIFICACAO_CENIPA = :P2_CLASSIFICACAO_CENIPA DATA_UTC = :P2_DATA_UTC, UF =…
-
5
votes2
answers193
viewsA: I was given this exercise: "ALL CUSTOMERS WHO BOUGHT MORE THAN R$ 200,00 LAST MONTH"
You forgot to group the sex. Lacked some good programming practices to facilitate reading and avoid errors, I do not like to use the conditionals at the intersection (Inner Join) as sex like 'f%'…