Posts by José Diz • 6,942 points
305 posts
-
0
votes1
answer109
viewsA: PIVOT after execution the message is "Incorrect syntax next to the keyword 'FOR'."
Roberto, before the FOR must inform an aggregation function. Something like max(), avg(), sum() etc. In case of your code maybe you can use PIVOT (max ([CH CURSO CT]) FOR [CURSO] IN Details in the…
-
0
votes3
answers3992
viewsA: Convert Date to year / month
Try -- código #1 SELECT convert (char(7), nome_coluna, 111) as AnoMes from tabela; where column name is stated as date, datetime or some other type of data specific to date storage. Style 111 refers…
-
0
votes2
answers500
viewsA: Selecting data and organizing by 15min Sql interval
Failed to inform how the columns are declared. -- código #1 @CodLocal int = 38, @Data varchar(10) = '2019-05-16', @HoraIni varchar(8) = '08:00:00', @HoraFin varchar(8) = '09:00:00' as with…
-
3
votes1
answer194
viewsA: Changing the character of the vehicle registration plate - Mercosur standard
Here is a suggestion for SQL Server. -- código #1 v2 SELECT Placa, -- método 1 substring (Placa, 1, 4) + char ((65 + cast (substring (Placa, 5, 1) as int))) + substring (Placa, 6, 2) as [Placa 1],…
-
5
votes1
answer66
viewsA: Performance of sql server with concatenated joins
In the code snippet INNER JOIN Totvs12.dbo.SE1010 receber ON receber.E1_FILIAL + receber.E1_NUM + receber.E1_PREFIXO = pedido.C5_FILIAL + pedido.C5_NOTA + pedido.C5_SERIE this concatenation of…
-
1
votes2
answers43
viewsA: How to find the value from accumulated balances
Here is the solution that deals with the possibility of cars having registration in different periods. -- código #1 with Registros_0 as ( SELECT Placa_Veiculo as placa, min (AnoMes) as…
-
2
votes1
answer1116
viewsA: Make a "distinct" in a query with many fields in select
(1) Before entering the issue of 75 x 213 returned lines, I suggest you review the clauses FROM/ON and WHERE from the point of view of sargability. For example, in the FROM clause of your code there…
-
0
votes2
answers198
viewsA: Conditional help in SQL WHERE
the field SD1010.D1_TES can assume N values, however, if this field is equal to empty, need one more restriction to be applied (SF4010.F4_TEXTO NOT LIKE '%%')* Make sure the following filter meets…
-
0
votes1
answer41
viewsA: How to create a precedent that accesses a substring and calculates using the substring as parameter?
Try: -- código #1 CREATE PROCEDURE exL @livro varchar(50), @pMedia money output as begin set nocount on; set @pMedia= (SELECT avg (valor_livro) from TB_Livro where titulo_livro like '%' + @livro +…
-
2
votes1
answer653
viewsA: Is it possible to make a direct SPLIT in a QUERY (SELECT) in SQL SERVER 2012?
To separate columns that have several values in the same row the usual is to use functions of the type string split. Even from version 2016 there is the native function STRING_SPLIT. However, when…
-
0
votes2
answers44
viewsA: Changing positions of an integer
Staying in the purely numerical context, here’s another solution: -- código #1 SELECT ((coluna % 100) * 1000000) + -- dia (((coluna / 100) % 100) * 10000) + -- mês (coluna / 10000) -- ano from ...…
-
1
votes1
answer794
viewsA: SQL Server Row Number Counter
It does not make sense to use the same column in the PARTITION BY clause and in ORDER BY. What is done is to use a set of columns to define the group (partitioning) and another set of columns to…
-
1
votes2
answers210
viewsA: Add Identity field in already filled table
Will it number according to the order that was inserted or will pick random order to apply the Identity? In SQL Server a table is a set of data without any order. Even if there is a column with a…
sql-serveranswered José Diz 6,942 -
1
votes2
answers92
viewsA: Error doing Insert in BD SQL using JDBC
The error was caused by a given value for date & time columns: INPUT, DATE, DATE, DATE, RECCREATEDON, RECMODIFIEDON. Most likely in the seconds part of the time 10:48:8, although the string…
-
0
votes2
answers171
viewsA: Select within select where condition exists
Here’s a suggestion that uses nonrecursive CTE. -- código #1 with ctePai as ( SELECT A.Referencia, A.Quantidade, A.Preco, A.Referencia as Pai, 0 as Tag from tabArtigos as A where not exists (SELECT…
-
-1
votes2
answers4491
viewsA: Query Search Specific Word in a Field - SQL Server
I am only trying to get from the Validity until 2019 Try something like -- código #1 v3 ... case when charindex (', validade ', DescricaoAndamento) > 0 then substring (DescricaoAndamento,…
-
0
votes3
answers133
viewsA: SUM of one table minus the SUM of another in the same field
I need to get a listing of outstanding invoices by taking partial payments and ignoring those that are already paid. The result should be as follows: Customer Valordocumento Valoremdebito -- código…
-
2
votes1
answer51
viewsA: Set primary keys in sequence
Here’s a solution for SQL Server: -- código #1 with empresas_seq as ( SELECT seq= row_number() over (order by cod), nome from bd1..empresas ) INSERT into bd2..empresas (cod, nome) SELECT seq, nome…
-
3
votes1
answer49
viewsA: How to avoid Inserts Simultaneos
Paul, what you question can really occur and is due to competition of proceedings. In SQL Server there are some solutions; one of them involves the use of the function SCOPE_IDENTITY: -- código #1…
-
2
votes3
answers147
viewsA: Join between banks
The result I imagine to have are the COMMON records of both sources and the records of a who are not in b but I am only shown the records in common. In the case of LEFT JOIN joins care should be…
-
1
votes2
answers67
viewsA: Difference in performance when Duplicate a table
There is significant difference in performance between these two examples? ... to use in a previous Not. No difference in performance. Internally, the same execution plan is generated, in both…
-
0
votes2
answers213
viewsA: How to filter only the last day of each month?
I need to bring these fields filtering by day 31/01, 28(or 29)/02, 31/03, 30/04, etc. The date field is E8_DTSALAT From what I understand, want the query return the information on 31/1/2018,…
-
0
votes1
answer1003
viewsA: SQL - Show the top 10 values per month
Evaluate the following suggestion, which considers that the column Data is stated as date. -- código #1 v3 -- informe ano a consultar declare @Ano smallint; set @Ano= 2018; -- variáveis de período…
sql-serveranswered José Diz 6,942 -
1
votes1
answer86
viewsA: Query to update field from one table based on another
(...) I need to update the field Lvi.vlCustoUnited with the data of the second table contained in pe.Vlprecocusto. Sketch of the code considering that there are no lines repeated by the column…
-
1
votes4
answers2917
viewsA: Filter using only month and year on SQLSERVER
Here is a solution that informs only the month and year and the code takes care of mounting the filter. -- código #1 v2 declare @Ano smallint, @Mês tinyint; -- informe o ano (formato aaaa) e mês…
-
0
votes1
answer36
viewsA: Assemble query for specific scenario
I need to find the records where START has the same value as the previous END Evaluate the following suggestion: -- código #1 SELECT T1.CODIGO, T1.INICIO, T1.FIM, T2.INICIO as [INICIO L2], T2.FIM as…
sql-serveranswered José Diz 6,942 -
3
votes1
answer128
viewsA: How to insert records via cursor?
I think using cursor for something so simple is complicating something that can be solved with INSERT ... SELECT. -- código #1 declare @Hoje datetime; set @Hoje= cast(current_timestamp as date);…
-
4
votes3
answers237
viewsA: Query SQL Add duplicate value
Here is the suggestion that the FROM clause contains the junctions and the WHERE clause the filters. -- código #1 set dateformat dmy; SELECT CODIGO_BARRAS.barra, sum (produtos_eventos.quantidade) as…
-
0
votes2
answers69
viewsA: Doubt with a query (SQL Server)
Here’s a solution: -- código #1 with servidores_seq as ( SELECT *, seq= row_number() over (partition by matricula order by ano desc, mes desc) from dbo.vw_portal_rhf_servidores where ... ) SELECT…
-
1
votes1
answer361
viewsA: Error trying to import . Bak file from Sql Server on Linux
SQL Server 2017 for Linux is compatible with Ubuntu 16.04; I don’t know if it is stable in Linux Mint 19.1, which is derived from Ubuntu 18.04. In the article Installing SQL Server 2017 for Linux on…
-
1
votes2
answers1911
viewsA: Create Trigger to change record only when modifying a field
Here’s a possibility: -- código #1 CREATE TRIGGER atualizadata on CADASTRO after INSERT, UPDATE as begin -- verifica se há algo a processar IF not exists (SELECT * from INSERTED) return; -- verifica…
-
3
votes2
answers380
viewsA: How to filter records by date using SQL
The figure shows that there are several lines for the same product. The solution varies according to the version of SQL Server. Considering getting the latest information from a single product, we…
-
0
votes1
answer125
viewsA: Performing temporary table comparison
What I want to return is, among all the data of the temporary table it returns the ones I have in the bank and the ones I do not have in the bank Rudolph, here’s a suggestion. -- código #1 v2 with…
-
1
votes1
answer111
viewsA: Migration oracle to sql server
For migration you can use the Migration Assistant (SSMA) to Oracle, which documentation is available at Introduction to the SSMA for Oracle. For the process of periodically synchronizing the SQL…
-
4
votes1
answer367
viewsA: When firing an Update Trigger, one of the fields only updates after a second Update
Trigger procedures (Trigger) are more difficult to be programmed in SQL Server than in other managers, because of the schema that is used internally in SQL Server. In general, when programming…
-
1
votes1
answer516
viewsA: Replace SQL SERVER between columns
The construction with the REPLACE() function is correct. If it did not work, one hypothesis is that the columns are defined with grouping case sensitive. If that is the case, A is different from a.…
-
2
votes1
answer43
viewsA: How to return what the customer bought/paid?
Braian, here is a sketch of the code that returns the value of each customer’s order and what has already been paid for each order. -- código #1 with Valor_Pedido_Cliente as ( SELECT Pd.ClienteID,…
-
4
votes2
answers161
viewsA: Update in three tables
For each table an update command should be used: UPDATE. It is simple to assemble. I suggest that you encapsulate the 3 commands with the pair BEGIN TRANSACTION / COMMIT -- código #1 declare…
-
0
votes2
answers210
viewsA: SQL Server foreign key is giving null value when entering data in the main table
Milestone, after the inclusion of the customer you must obtain the value that was generated for the column idclient. This is possible using the SCOPE_IDENTITY function(). It seems to me that both…
-
1
votes3
answers3588
viewsA: How to store more than one value in an "SQL variable"?
Otávio, evaluate if Report Builder accepts the following construction: -- código #1 declare @tbUnidade table (Deno_Unidade ___); INSERT into @tbUnidade (Deno_Unidade) SELECT NOME from UNIDADES where…
-
2
votes4
answers3071
viewsA: If condition in Where SQL Server
As it is a stored procedure it is necessary to be attentive to avoid Parameter sniffing, subject explained in a didactic form in the article Introduction to Parameter sniffing in SQL Server. If…
-
0
votes2
answers51
viewsA: Search for elements with a certain distance of time (informing only time)
For each row of the table there may be a subset of lines that are in the 40 second range. A single row may participate in more than one subset. -- código #1 SELECT T1.ID, T1.Data, T2.Data from…
-
0
votes2
answers462
viewsA: Bring today’s date records up to 1 month ago SQLSERVER
How do I get today’s date records up to 1 month ago on SQLSERVER? I suggest you use the function DATEDIFF. -- código #1 declare @UmMêsAtrás date; set @UmMêsAtrás= datediff (month, -1, cast…
-
1
votes1
answer2682
viewsA: Incorrect syntax near ','
I don’t understand ASP/VBSCRIPT but the languages have a lot in common, which allowed us to understand what the code does; and how it does it. In the code transcribed for this topic I noticed that…
-
0
votes2
answers113
viewsA: Doubt integration test
(...) would like to understand what would be the most lean and correct way to test the operation with the database. It is not necessary to count how many lines meet the clause WHERE; just find a…
-
3
votes2
answers1789
viewsA: Division in the sqlserver
You need to be aware of the data type of the denominator and the divisor. When both are numerical expressions in the domain of integer numbers, the integer division occurs, obtaining the integer…
-
0
votes2
answers160
viewsA: How to get the difference in seconds with Sql Server
Here’s a solution for 2012 (or newer) version of SQL Server. -- código #1 SELECT ID_Serviço, ID, lag (DataExecucao) over (partition by ID_Servico order by ID) as DataExecucao_ant, DataExecucao,…
-
0
votes1
answer111
viewsA: SQL Server 2017 - SELECT with Duplicate Metadata
It seems to me that the junction with the exhibition sys.extended_properties is incomplete. Try the following: -- código #1 .... LEFT JOIN sys.extended_properties TEP ON T.object_id = TEP.major_id…
-
2
votes1
answer225
viewsA: select distinct in temporal table sql server
representatives have the same code (...) catching the most current representative, in short the last guy who synchronized If what you need is the last sync of each representative, make sure the…
-
0
votes3
answers187
viewsA: Display record when another table has no reference
In set theory what you’re looking for is C = A - B where A is the news and B is the photos. There are several ways to solve what you need. Here are a few ways to solve it using T-SQL. -- código #1…