Posts by Motta • 3,568 points
233 posts
-
1
votes3
answers899
viewsA: NOT EXIST ? How to use
Roughly would be something like select * from empresas where plano in ('A','B','C','D','E') and not exists (select null from Franquias where Franquias.cod_empresa = empresas.cod)…
-
1
votes1
answer169
viewsA: Average value of entries up to 100km sql
try : select avg(valor) from ( SELECT valor,(6371 * acos( cos( radians(-23.2632227) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-45.9414303) ) + sin( radians(-23.2632227) ) * sin(…
-
0
votes1
answer188
viewsA: Create view by transforming deleting or transforming DATE into VARCHAR in ORACLE
Follows example generating dbms_output , but a basic idea , generates the script DECLARE VN_C NUMBER := 0; VN_COLUNAS NUMBER; BEGIN SELECT COUNT(COLUMN_NAME) INTO VN_COLUNAS FROM USER_TAB_COLUMNS…
-
1
votes2
answers2102
viewsA: Case no order by
order by cod_rua, (case cod_lado when 'M' then cod_lado else cod_lado ASC end) desc, cod_altura The "case" is a column the "desc" comes after.
-
2
votes1
answer1642
viewsA: Doubt in select with group by and Count?
Oracle "group by" has to be the same as "select" select f.numemp, f.numcad, f.nomfun, f.codcar, c.titcar, count(*) from R034fun f join R024car c on f.codcar = c.codcar where f.codcar in…
-
0
votes1
answer131
viewsA: SQL Report Builder 3.0 Merge Rows into a Totalizer column
A solution : SELECT NOME, CODIGO, (SELECT SUM(IDADE) FROM TABELA) SOMA_IDADE FROM TABELA Some BDS ANALYTIC FUNCTIONS that facilitate tasks of this type.…
-
0
votes1
answer41
viewsA: How to realize the sum of values I multiplied?
I believe it’s something like : select cod_produto , sum(valor_unitario * quantidade) valor_total from vendas group by cod_produto
-
1
votes1
answer707
viewsA: Bulk insert into a table
A possible solution (without clinging to syntax) In a protected block BEGIN TRY INSERT INTO ....; END TRY BEGIN CATCH --SE ERRO DE DUPLICADOS ENTAO UPDATE ....; END CATCH;…
-
1
votes1
answer96
viewsA: How to delimit a phone field (Oracle Database)
One solution may be to create a check Constraint. For example ALTER TABLE CLIENTES ADD CONSTRAINT CHK_CLIENTES_TEL CHECK (TELEFONE IS NULL OR LENGTH(TRIM(TELEFONE)) IN (8,9)); Here for example will…
-
2
votes2
answers405
viewsA: Oracle order by
SELECT * FROM tab_estado WHERE (nm_uf like'%MG%' or nm_uf like'%BA%') order by nm_uf desc Either the city is Minas or Bahia ... Order by asks for a column.
-
0
votes2
answers717
viewsA: Search and people who have the same name, surname and date of birth
Broadening the @Davy Machado solution a bit SELECT * FROM PESSOAS WHERE (NOME, SOBRENOME, DATA_NASCIMENTO) IN (SELECT NOME, SOBRENOME, DATA_NASCIMENTO FROM PESSOAS GROUP BY NOME, SOBRENOME,…
-
1
votes1
answer426
viewsA: Update with calculation
In some line the value of preco_cost must be zero , can not be divided by zero , do : update produto set margem_lucro = (case when preco_produto > 0 then ((((preco_unit/preco_custo) * 100) -…
postgresqlanswered Motta 3,568 -
2
votes2
answers138
viewsA: doubt with select sum() Mysql
Your model is bad make one of the kind Table Date Valor With date functions it becomes easy to total by month , year, week etc.…
-
0
votes4
answers6919
viewsA: SELECT of month/year in DATES
SELECT * FROM FRCAST WHERE TO_CHAR(DATA_ID, 'mm-yyyy') ='07-2010'
-
0
votes1
answer148
viewsA: Delete duplicate lines with postgresql conditionals
Create a key in the key that you don’t want to repeat, an error will occur if an Insert tries to duplicate, handle the error in the application Example ALTER TABLE distributors ADD CONSTRAINT…
-
4
votes0
answers84
viewsQ: Oracle , how the parameter no_proxy_domains of UTL_HTTP.set_proxy works
In the package UTL_HTTP , we have the method set_proxy in it a parameter no_proxy_domains , how this works. In short I want to request a URL without authentication.…
-
0
votes3
answers701
viewsA: Make a search independent of the order of the keywords
something like that , mounting a dynamic sql based on an array of words Joao holy christ select * from tabela where ( description like '%joao%' or description like '%santo%' or description like…
-
1
votes2
answers158
viewsA: Query to return specific values
select * from identificacao_pessoal where length(identificador) <> 11 Tip , rethink the model.
-
0
votes1
answer478
viewsQ: Oracle plsql email attachment file will empty
I have the PLSQL block listed below, the email is sent but the file that should be attached arrives empty in the destination email. Send an existing file, no ? What am I doing wrong ? Grateful…
-
0
votes1
answer87
viewsA: Make a select according to an indexed table
If you want to fetch the array 'A/C', 1 'Extintor', 1 You mount a sql like this DECLARE vs_sql varchar2(4000): Begin --SUPONDO UM ARRAY vs_sql := 'SELECT getSalas.ID_SALA'; vs_sql := vs_sql || 'FROM…
-
0
votes4
answers898
viewsA: SQL Count Parole
SELECT QTD,COUNT(*) Ocorrencias From ( SELECT data, COUNT(valor) QTD FROM treatment_output WHERE valor = 0 GROUP BY data ) VIRTUAL Group by QTD …
-
2
votes1
answer258
viewsA: Why using a column of a virtual table makes an SQL in Oracle slow
If interest to others , I resolved with a HINT SELECT /*+USE_CONCAT*/ * from (select a,b,c,virtual.d from tabela1, (select a,b,c,d from tabela2 where ....) virtual where virtual.a = tabela1.a and…
-
3
votes1
answer258
viewsQ: Why using a column of a virtual table makes an SQL in Oracle slow
Good. Without wanting to leave the subject too "gassy" but wanted only tips of things to investigate in the environment. I have an SQL that uses a virtual table, for a number of reasons that are not…
-
3
votes2
answers246
viewsA: DATES applied SQL Decode function
Assuming a DATE field, two solutions 1) DECODE(DATA_SAIDA,NULL,'ATIVO','INATIVO') STATUS 2) (CASE WHEN DATA_SAIDA IS NULL THEN 'ATIVO' ELSE 'INATIVO' END) STATUS
-
-1
votes5
answers1818
viewsA: Group records per day
SELECT ID, SUM(CASE WHEN DAY(INICIO) = 1 THEN HORAS ELSE 0 END) AS D01, SUM(CASE WHEN DAY(INICIO) = 2 THEN HORAS ELSE 0 END) AS D02, ... SUM(CASE WHEN DAY(INICIO) = 31 THEN HORAS ELSE 0 END) AS D31…
-
2
votes1
answer264
viewsA: Insert same data into two Mysql columns
+or- this CREATE TRIGGER ins_table BEFORE INSERT ON table FOR EACH ROW BEGIN SET NEW. z = new. y; END;//
-
0
votes3
answers928
viewsA: Update in a field that cannot be negative
In thesis (just testing) a update Trigger in the table could solve : Something like CREATE TRIGGER BEFORE UPDATE IN TABELA ... IF :NEW.VALOR < 0 THEN :NEW.VALOR := 0; INSERT INTO TABELA…
-
6
votes1
answer1448
viewsA: How to toggle lines of a report with custom colors Crystal Reports and C# winforms?
You have the Function Color (red, green, blue) You can have a help in a normal color edition , "more" button edit the color as you want and use the red, green and blue parameters in the cited…
-
0
votes1
answer38
viewsA: Mark lines where balance sum = 0
select data , num , sum(credito) credito , sum(debito) debito , sum(credito-debito) balance , (case when sum(credito-debito) = 0 then 's' else 'n' end) zero_balanco from tabela where .... group by…
postgresqlanswered Motta 3,568 -
0
votes2
answers87
viewsA: How to list last 7-day birthdays with Crystal Reports and c# winforms
(totext({Comando.NASC},"MMdd") >= totext(CurrentDate-7,"MMdd") and totext({Comando.NASC},"MMdd") <= totext(CurrentDate,"MMdd")) Example I did here and it worked…
-
2
votes1
answer54
viewsA: Doubt with "pivot" ORACLE
No entry on the merits of the subselect motive SELECT CODIGO_PROCESSO, NOME_PRODUTO_PROCESSO, PROVADO_USO, MAX (DECODE (tipo_arquivo, 'BT', caminho_arquivo)) ArquivoBT, MAX (DECODE (tipo_arquivo,…
-
1
votes2
answers558
viewsA: Multiple if in one SQL condition
... AND ( IF(aci_tipo_pagamento = "PE", bai_data_cadastro, bor_data_criacao) >= '2016-11-22 00:00:00' AND IF(aci_tipo_pagamento = "PE", bai_data_cadastro, bor_data_criacao)…
-
2
votes2
answers60
viewsA: Calculation for year "turn" with Trigger
I BELIEVE IT WORKS DECLARE P_COUNT NUMBER(5); DIA_VENDA VARCHAR(2); MES_VENDA VARCHAR(2); MES_VENDA2 VARCHAR(2); MES_VENDA_DEPOIS VARCHAR(2); ANO_VENDA VARCHAR(4); P_DATA DATE; P_DATA2 DATE; BEGIN…
-
0
votes1
answer309
viewsA: How to create a sub-select in oracle in a composite key table?
Something like Select * From tabela t1 Where ... And data = ( select max(data) From tabela t2 Where t2.chave1 = t1.chave1 And t2.chave2 = t1.chave2)…
-
1
votes2
answers242
viewsA: How to add several decodes in the same line?
Try. SELECT iTG.GUI_NUMERO, itg.pro_tipo_procedimento, sum(case when itg.pro_tipo_procedimento='MAT' then NVL(itg.GUI_VALOR_PAGO_CUS,0) + NVL(itg.GUI_VALOR_PAGO_FIL,0) NVL(itg.GUI_VALOR_PAGO_SER,0))…
-
1
votes3
answers94
viewsA: Select with Count and add
follows a solution using CASE SELECT COUNT(*) AS 'quant_total', (case when empresa in ('X','Y') then 'GRUPO XY' else empresa end) empresa, turma FROM `alunos` WHERE data_matricula >= '2014-01-01'…
-
0
votes1
answer123
viewsA: View creation with record limit
Using Analytic Functions , creates a rank and is ordered by it , the view goes on top of this. SELECT primeironome, Ident, Nome, Dt.Nasc, Local FROM ( SELECT a.ID AS Ident, B.NOME AS Nome,…
-
0
votes1
answer82
viewsA: Check for UK before creating a
Without getting into the merits of the solution declare vn_qtd number; ... begin SELECT count(*) into vn_qtd FROM DBA_CONSTRAINTS WHERE CONSTRAINT_NAME = 'UK_t127pwh154arjh5whq0g4dlrm'; if vn_qtd =…
-
3
votes1
answer192
viewsA: In a dependent SELECT, with data of cities of a table how to leave a city in evidence?
A solution : select cities do something like select cod,nome from cidades order by (case when nome = 'Ribeirão Preto' then 0 else 1 end).cidades.nome
-
0
votes1
answer50
viewsA: Creation of multiple dynamic lines in an oracle table
BEGIN FOR R IN (SELECT NOME,QUANTIDADE FROM EXEMPLO) LOOP FOR I IN 1..R.QUANTIDADE LOOP INERT INTO TABELA (NOME,NUMERO) VALUES (R.NOME,I); END LOOP; END LOOP; END;…
-
4
votes3
answers1696
viewsA: How to transpose rows into columns?
With UNION maybe select 'jan' , jan linhabase from tabela union select 'fev' , fev linhabase from tabela union ... select 'dez' , dezlinhabase from tabela But the problem became unclear to me ,…
-
2
votes1
answer32
viewsA: SQL - Query Doubt
But the date you want 18/12/2015 is not between the range 23.12.2015 and 01.01.2016, seems to bring the smallest only if the lowest general is in the requested range , want to do when not bring null…
-
1
votes1
answer1788
viewsA: How to set output size in Sqldeveloper "script" option
I managed to sort it out like this : I create the following script in . sql file (text) set linesize 10000 set pagesize 10000 Going to Tools / Preferences / Database point this file I do not know if…
-
1
votes1
answer1788
viewsQ: How to set output size in Sqldeveloper "script" option
Oracle - Sqldeveloper - Version 4.1.3.20 Build MAIN-20.78 When running an SQL block there are two options (Run Instruction or Run Script), in this option , after an update is made, there is a…
-
1
votes1
answer80
viewsA: Mysql query with mixed ORDER BY?
Try this select * from ( SELECT p.nome AS produto, SUM(iv.quantidade) AS qtd, pp.preco_venda AS preco, (SUM(iv.quantidade)*pp.preco_venda) AS renda FROM tb_venda v INNER JOIN tb_item_venda iv ON…
-
0
votes2
answers204
viewsA: Save values whenever a new line is added via Trigger
create or replace TRIGGER trg_line_total AFTER INSERT ON LINE -- STATEMENT NAO "EACH ROW" BEGIN --MONTA UM CURSOR (RTOTAL) PARA TOTALIZAR OS VALORES for RTOTAL in (SELECT line.inv_number ,…
-
2
votes2
answers1104
viewsA: Oracle SQL result ordering
order by to_date(mln.lot_number,'YYYYMMDD') , 15 ASC but I would review the solution, better working with dates (type date).
-
3
votes2
answers161
views -
2
votes2
answers152
viewsA: How to make something expire after certain months in db?
Do not do this deletion, create a DATA_DE_VALIDADE column , in the application test something like select * from compras where userid = 'DaviDEV' and CURDATE() <= DATA_DE_VALIDADE The advantage…
-
0
votes2
answers1031
viewsA: calculate how many hours between call opening date and current date in pl/sql
Subtracting two dates in Oracle results in difference in days (or fraction) to know in hours , multiply by 24. Something like. Select ... From tabela Where ... And (datachamado - sysdate) * 24 >=…