Posts by José Diz • 6,942 points
305 posts
-
3
votes1
answer172
viewsA: Query SQL Server Error
But if I put a top 1 after SELECT and go running one by one it works. The error is not in the inclusion code but in the Trg_lock trigger procedure. Since the inclusion code works when one line is…
sql-serveranswered José Diz 6,942 -
-1
votes2
answers762
viewsA: How to put ISNULL in Subselect with case?
The calculation of "Invoiced" can be rewritten to -- código #1 v2 SELECT concat(CC.Descricao,'-', U1.UsuNome), C.CompDesc as QtdeCNPJs, case when exists (SELECT * from Tarefa as T1 where T1.ProjID =…
-
0
votes2
answers3352
viewsA: How to fix accentuation query sql server data
This is due to the difference between the character encoding used in the column and the encoding used in the application. One should check which is the COLLATE of the column and then configure in…
-
4
votes4
answers1193
viewsA: Which DBMS to use so that it can be installed on the client’s computer to run a C#application?
I wanted an indication of DBMS that is good and simple and lightweight, to use as a bench in this application C#. I need only one computer with the database and the other computers with the system…
-
1
votes1
answer328
viewsA: Select with multiple Left Joins
Edilaine, here is code draft that considers that, no CPF in one of the bases, the crossing is performed phone to phone. For this it was necessary to transform each row of each table into 3 rows, one…
-
1
votes3
answers186
viewsA: Crossing of Tables with Multiple Coalesce
Here’s an initial suggestion: -- código #1 v4 SELECT coalesce(A.ano_pcs_novo, D.ano_pcs_novo) as ano_pcs_novo, coalesce(A.nro_pcs_novo, D.nro_pcs_novo) as nro_pcs_novo, A.pcs_antigo, B.assunto,…
sql-serveranswered José Diz 6,942 -
2
votes2
answers651
viewsA: SQL query with list in WHERE
You can put everything in a single query (with subconsulta). -- código #1 SELECT Nome from restaurantes where id in (SELECT idEmpresa from endereco_atendimento where bairro = 'nome bairro');…
-
3
votes2
answers419
viewsA: Unixodbc: Decimal Formatting Problem
It seems a matter of language definition (language) in the presentation. For example, in the English language the decimal separator is the point and in Portuguese is the comma. Evaluate the behavior…
-
0
votes2
answers530
viewsA: How to make a direct calculation in the database
(...) I would like to calculate a teacher’s salary based on the course hours he teaches, (...) Here is a suggestion, which first adds up the workload of all the disciplines that the teacher teaches…
-
0
votes3
answers378
viewsA: Doubt SQL query d
I am wanting to perform the calculation in sql query where the result is the number of open calls divided by the number of working days chosen period. This is a different approach to the code…
-
1
votes1
answer833
viewsA: Help with Trigger summing/subtracting
The procedure Trigger then it is to monitor the inclusion of rows in the Transaction table. -- código #1 v2 CREATE TRIGGER incTransacao_Conta on Transacao after INSERT as begin -- encerra o…
-
0
votes3
answers2459
viewsA: Doubt SQL Working Days Query
On the issue of working days I suggest reading the article "Business day operations on SQL Server"that details ways to get the calculation, including with the optional use of calendar table. Here is…
-
1
votes2
answers284
viewsA: Query that only returns the result if a field is in another table
Here is the suggestion for what you request, which works with any number of accessories to search. -- código #1 -- informe os extras a pesquisar CREATE TABLE #Pesquisa (Extra varchar(30) unique);…
-
2
votes1
answer64
viewsA: Doubt in conversion of rows into columns Sql Server 2012
Here is the suggested code to get the report. -- código #1 -- monta nome das colunas declare @colID varchar(200), @colDescID varchar(4000); set @colID= ''; set @colDescID= ''; SELECT @colID+= '[' +…
-
3
votes1
answer1648
viewsA: How to assign a EXECUTE SP_EXECUTESQL @SQLSTRING to a temporary table
If you declare the temporary table in context external to the procedure, it is visible in the procedure and also in the dynamic SQL command executed within the procedure. You must run INSERT within…
-
0
votes1
answer113
viewsA: SPLIT_STRING in several 'paragraphs'
Bruno, I initially thought of using the comment separator (blank line, which means double CR+LF) as the comment separator. It would be simple to implement, but it would not be a good strategy. So I…
-
1
votes2
answers273
viewsA: Condition in the trial
Try -- código #1 v2 SELECT ... from ... where GG.ID_GERENTE = @ID_GERENTE and 1 = case when @ST_TIPO is null then 1 when @ST_TIPO = 'BSP' then case when PT.ST_TIPO = 'BSP' then 1 else 0 end when…
-
0
votes2
answers267
viewsA: Store Procedure not working properly
It seems to me that the code needs to be rewritten. The cursor cVendas is declared to read row in table NOTAFISCAL and return the columnsDATA, VALORNOTA and MAT. In the first FETCH the use of the…
-
4
votes1
answer1016
viewsA: View with parameters without external Where?
I need to receive parameters for a view, To remind me there is no way to use parameters for a display (view). What is possible is to add, in the call to the display, clauses such as WHERE and ORDER…
-
0
votes1
answer147
viewsA: Bring the word HISTORICAL when the line date is less than the previous dates,
This is an approach that analyses all the previous lines: -- código #2 SELECT CD, Item, ValA, ValB, [Data], case when exists (SELECT * from Tabela as T2 where T2.CD < T1.CD and T2.[Data] >…
-
0
votes1
answer42
viewsA: How to change the contribution code for duplicate records
Considering that the value of the Cod_contribution column should be renumbered for each registration, ordered by the date of payment, here is sketch of the code. -- código #1 v3 with cteRecod as (…
-
2
votes2
answers625
viewsA: Query SQL - CASE with LEFT JOIN
Here is a suggestion to evaluate: -- código #1 v2 with cteAcumNotas as ( SELECT A.codigoAluno, A.nome as nomeAluno, M.codigoMateria, M.nome as nomeMateria, sum(N.valor) as valor from ALUNO as A…
-
1
votes2
answers605
viewsA: Trigger updates only to 1 record
A few weeks ago I’m writing articles on the pitfalls in the programming of procedures Trigger in SQL Server. The question of the procedure Trigger treat only one line is one of the most common…
-
1
votes1
answer321
viewsA: problems to create multiple tables in Oracle
I can’t remember if Oracle Database allows more than one object to have the same name. In the script you posted, the primary key of each table is with the same table name. You can set the primary…
-
2
votes1
answer1249
viewsA: Find a numeric group in the string
Here is demonstration of the use of the function charindex to find the section you are looking for. -- código #1 declare @URL varchar(2000); set @URL=…
-
1
votes2
answers183
viewsA: Select in Sql Server
The statement leaves open whether, for the highest value of Version, it should return all lines (if there is more than one) or whether it should get the highest value for the pair (Version,…
-
11
votes3
answers1699
viewsA: Why doesn’t "= NULL" work?
The comparison WHERE cli.cpf = NULL is valid as long as ANSI_NULLS is set to OFF. As stated in the documentation of ANSI_NULLS, When SET ANSI_NULLS is OFF, the comparison operators Equal to (=) and…
-
2
votes1
answer83
viewsA: How to interpret * in this query
At first the construction *= is the same as LEFT JOIN. Constructions of the type -- código #1 SELECT T1.codigo, T2.nome from tab1 as T1, tab2 as T2 where T1.codigo *= T2.codigo; are usually…
-
2
votes1
answer327
viewsA: How many percent N item represents in the overall total - Query
Evaluate if the code below meets your needs. -- código #1 SELECT A.COD_ITEM, A.Subtotal, (A.Subtotal / B.Total * 100) as Perc from (SELECT COD_ITEM, sum(SUBTOTAL) as Subtotal from ITENS group by…
-
0
votes3
answers2752
viewsA: Comparison of Strings in sqlServer
Direct version comparisons like string are unreliable. For example, "2.1.20" is more current than "2.1.9" but, when directly comparing the two values, the most current value returned is "2.1.9". --…
-
9
votes2
answers48751
viewsA: What are Begin, Commit and Rollback transactions?
In the book Sistemas de banco de dados, from Elmasri & Navathe, chapters 17 to 19 (pages 395 to 452, 4th edition) deal with transaction processing theory. On the question "What are…
-
4
votes2
answers1906
viewsA: Convert and save photo in BD
Thomas, I suggest you declare the column that will store the photo image as varbinary(max). Is that the data type image will be disabled (someday...), according to Microsoft. If the average image…
-
1
votes1
answer530
viewsA: Standard structure of a SQL Server database
Some suggestions: (1) use the command set NOCOUNT on at the beginning of the procedure, before any other. (2) return information on whether the procedure was executed with or without errors through…
-
2
votes1
answer58
viewsA: How to sort a list by current time and next dated records
Evaluate if the following code meets you. -- código #1 SELECT id, programa, descricao, hora_ini, hora_fim, link from tbProgramacao where id >= (SELECT id from tbProgramacao where hora_ini <=…
-
2
votes1
answer958
viewsA: Compare variables with values from an SQL Server table C#
I suggest you run the search directly on the database server, to prevent all the contents of the IVA table from moving over the network. SELECT Code from IVA where Value = @taxpercentage This way…
-
-1
votes1
answer144
viewsA: I made my first program and now I need to run it on the client. How do I?
Gilberto, the database itself (i.e., the data) is architecture-independent (32 or 64 bits). But you will first have to install SQL Server Express 2016 32-bit on the client’s computer. On your…
-
3
votes1
answer329
viewsA: Mysql - Linux installation problems
It seems to me that there is no problem in installing Mysql but rather some previous incomplete installation (Broken). Error message states that there was an attempt to install the package atom, but…
-
1
votes2
answers364
viewsA: sql inserts the wrong datetime into the table
What happened is that the value 1999-10-02 was considered as a numerical expression: 1999 - 10 - 2 = 1987 And this numerical value was added to the lowest date value available for datetime, which is…
-
1
votes1
answer93
viewsA: Update an Access application
Miguel, there are two update lines, which seem to me partially parallel: the migration of sgbd (Access to SQL Server) and the replacement of language/environment. DATABASE To migrate the Access…
-
0
votes2
answers950
viewsA: Calculate Value of SQL columns
would like the total sum of the order value made on a given date In this case it is not necessary to use the clause GROUP BY. Here is the code suggestion: -- código #2 SELECT sum(I.NR_QTDE *…
-
2
votes4
answers11284
viewsA: How to create temporary mirror table in another
To create an identical structure, but without any content, you can use the following form: -- código #1 IF Object_ID('tempDB..#tabela','U') is not null DROP TABLE #tabela; SELECT * into #tabela from…
-
2
votes1
answer1315
viewsA: SQL - scope_identity() for INSERT SELECT
Filipe, you can use the clause OUTPUT to write to a table variable (or even another table) the values generated during inclusion (instruction INSERT). Following is model, assuming that the table…
-
8
votes2
answers9904
viewsA: What is the difference between clustered index and nonclustered index?
Marconi, the example you cite specifically deals with SQL Server. What is the difference between clustered index and nonclustered index? The basic difference between index clustered (grouped) and…
-
2
votes1
answer619
viewsA: Select that does not return SQL blank
"(...) I need that when the field is blank do not return me anything" Kevin, evaluate the following suggestion: -- código #1 v2 declare @Hoje date; set @Hoje= cast(current_timestamp as date); SELECT…
-
0
votes2
answers543
viewsA: Assign value to SQL variable
If variable use is required, as stated in the title of the topic, here is an approach: -- código #1 (T-SQL) declare @jogos int; set @jogos= 10; -- SELECT codigo, @jogos as jogos from minha_tabela;…
-
3
votes2
answers32603
viewsA: How to get the size of each table in the database?
In the Technet Gallery there are several scripts on how to get table size. For visual analysis, the simplest way is -- código #1 execute sp_MSforeachtable 'execute sp_spaceused [?]'…
-
1
votes2
answers126
viewsA: View Not Updatable in SQL Server
In the documentation of CREATE VIEW there is something about updatable views and what are the rules for obtaining them. By break some of the rules, it seems to me that we have a non-upgradable…
sql-serveranswered José Diz 6,942 -
1
votes2
answers99
viewsA: Return values less than 10 from a column with Datediff
Jander, for this totalization are not necessary some of the tables, which were used in the original query only to obtain descriptions. Here is a way: -- código #1 v2 with ctePATRIMON as ( SELECT…
-
1
votes2
answers5013
viewsA: How to add two columns?
It seems to me that the section you posted is a correlated subconsultation. Evaluate the following code: -- código #1 v4 SELECT ..., (SELECT sum(case when sd3_sub1.D3_UM = 'PC' then…
-
2
votes2
answers1649
viewsA: Optimize a data transfer from one table to another SQL SERVER
Cirineu, why you report the problem is that, during the execution of the first T-SQL code described, all the available space on the disk is used, even before the execution of the code ends, aborting…