Posts by Motta • 3,568 points
233 posts
-
2
votes1
answer354
viewsA: Doubt when mounting a Where in a Procedure
Only the filter, a solution ( ( @pago = 1 and SaldoAbertoNF > 0) Or ( @pago = 2 and SaldoAbertoNF = 0) Or ( @pago = 3) )
-
1
votes1
answer31
viewsA: How to ban a DDL action on oracle
CREATE OR REPLACE TRIGGER TG_HORARIO BEFORE UPDATE OR DELETE OR INSERT ON XNOTA FOR EACH ROW DECLARE D_SEMANA CHAR(1); HORA CHAR(4); BEGIN D_SEMANA := TO_CHAR(SYSDATE, 'd');--SELECT NAO NECESSARIO…
-
0
votes2
answers574
viewsA: Calculate Geometric Mean in SQL Query
--li ponderada e nao geometrica --usando a propriedade dos logs se tem o produto exp(sum(log(valor))) --calcule a raiz power(exp(sum(log(valor))),(1/(count(*))) --isto deve resolver para números…
-
0
votes2
answers59
viewsA: Query to add fields from two tables
Just leave only the required in the "group" select COUNT(*), qualidades.cor, qualidades.raça from pessoas, qualidades where pessoas.id_qualidades_pess=qualidades.id_qualidades group by…
-
2
votes3
answers340
viewsA: Sort in SQL by prioritizing a specific field value
It is possible to apply a CASE rule to order by ... ORDER BY (CASE WHEN id_sistema_ls = 26606 THEN 0 WHEN id_sistema_ls = 18 THEN 1 ELSE 2 END),id_sistema_ls…
-
1
votes1
answer1805
viewsA: SELECT inside a FOR in declare SQL/ORACLE
DECLARE vf_Arquivo SYS.UTL_FILE.FILE_TYPE; vs_caminho varchar2(25); vs_arquivo varchar2(50); vs_linha varchar(4000); CR CHAR(1) := Chr(13);--CARRIEGE RETURN BEGIN vs_caminho := '... utl…
-
1
votes1
answer287
viewsQ: What exactly is the UTL_HTTP (ORACLE) SET_PROXY Proxy
utl_http.set_proxy(http://pgau:abcd@[email protected]:5678'); In this example what exactly this Proxy address ? I tried the internal IP and it didn’t work , the below my company said not to have…
-
1
votes1
answer1396
viewsA: convert varchar to date oracle plsql
--this block lists dates that are not in expected format set serveroutput on;--se sql plus, senao setar o output na ferramenta declare vd date; begin for r in (select data_final from tabela) loop…
-
0
votes1
answer161
viewsA: LISTAGG returning repeated values
a solution and make a virtual table SELECT PEH.PN, LISTAGG (PEH.AC_TYPE, ',') WITHIN GROUP (ORDER BY PEH.AC_TYPE DESC) AS "EFETIVIDADE" FROM\ (SELECT DISTINCT PEH.PN,PEH.AC_TYPE FROM…
-
1
votes1
answer150
viewsA: How to sort the result of a query by a column where there is a value in another column in the same table?
SELECT DISTINCT * FROM products, tech_products WHERE products.idsub = 1 AND tech_products.id = products.id ORDER by (case when (tech.id_cor = 5) then 0 else 1 end) ASC, tech.id_cor The syntax of…
-
4
votes2
answers2225
viewsA: How to take the sum of an entire column in SQLSERVER
Something like : Select campo1, sum(valor1) valor from tabela group by cube (campo1)
-
1
votes1
answer295
viewsA: Select to pick up 2-column Constraint
SELECT COL.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col WHERE Col.Constraint_Name = Tab.Constraint_Name AND Col.Table_Name =…
-
1
votes1
answer225
viewsA: Oracle - Cursor
SET SERVEROUTPUT ON DECLARE V_NAME VARCHAR2(50); CURSOR CUR_CURSOR IS SELECT FIRST_NAME FROM EMPLOYEES; TEMP CUR_CURSOR%ROWTYPE; BEGIN IF NOT CUR_CURSOR%ISOPEN THEN OPEN CUR_CURSOR; LOOP FETCH…
-
2
votes1
answer46
viewsA: Signaling the last SQL record
Assuming the latter is to have no larger product* in the same location/note , I make a subselect in the same table counting the largest if greater than zero is not last , if it is not. select…
-
0
votes1
answer45
viewsA: Run Trigger according to the value of a column
I think it would be better DELIMITER $$ CREATE TRIGGER update_bid AFTER UPDATE ON auction FOR EACH ROW BEGIN IF ((OLD.auc_status <> NEW.auc_status) and (NEW.auc_status = '3')) THEN /* Conteúdo…
-
1
votes0
answers206
views -
1
votes2
answers9821
viewsA: Select to display the column name of a table?
This Function is useful for developers as it generates an editable list for SQL create or replace FUNCTION colunas (TABELA IN USER_TABLES.TABLE_NAME%TYPE, PREFIXO IN VARCHAR2 DEFAULT NULL, SUFIXO IN…
-
1
votes1
answer593
viewsA: Field as parameter in Generic Update - ORACLE
The basic idea in an anonymous block follows below DECLARE VS_SAI VARCHAR2(4000); VS_TABELA VARCHAR2(30) := 'PESSOA_FISICA';--nome da tabela do banco WS_RNOME VARCHAR2(30) :=…
-
1
votes1
answer278
viewsA: Decode vs Case Query
The decode functions as a if of some languages condition result EXAMPLE SELECT DECODE(TIPO_PESSOA,'J','JURIDICA','F','FISICA','DESCONHECIDO') FROM CLIENTES The marry allows more complex "rules"…
-
1
votes3
answers1756
viewsA: How to declare a cursor variable in PL-SQL?
I use this syntax , follows example. BEGIN FOR RCLIENTES IN (SELECT * FROM CLIENTES) LOOP DBMS_OUTPUT.PUTLINE(RCLEINTES.NOME); END LOOP; END;
-
1
votes1
answer138
viewsA: Error After Running Error: No output displayed
No Sql Developer Menu Exibir Saída DBMS Botão **+** Escolher conexão In this window the outputs.
-
0
votes1
answer72
viewsA: Select the value of a field depending on a date
SELECT * FROM TB_DEPRECIACAOSOCIETARIACAL A1 WHERE CD_SEQUENCIALBEM = 20 AND DT_FIMCALCULO = (SELECT MAX(DT_FIMCALCULO) AS DT_FIMCALCULO, FROM TB_DEPRECIACAOSOCIETARIACAL A2 WHERE A2.DT_FIMCALCULO…
-
0
votes2
answers1597
viewsA: Postgresql Error: "ERROR: there is no Unique Constraint matching Given Keys for referenced table "address""
CREATE TABLE ENDERECOCLIENTE( ELogradouro VARCHAR (50) NOT NULL, ECEP VARCHAR(50) NOT NULL, ENumero INT NOT NULL, CCPFCliente INT NOT NULL, PRIMARY KEY (ELogradouro,ECEP,ENumero,CCPFCliente),…
-
1
votes1
answer193
viewsA: Create a Freight Calculation Table in sql server 2012
--without sticking to Pks and Fks CREATE TABLE Estados ( Id INT, Sigla VARCHAR(2), CONSTRAINT PK_Estados PRIMARY KEY (Id, Sigla) ) CREATE TABLE Tarifas ( Id INT PRIMARY KEY IDENTITY(1,1), Descricao…
-
2
votes1
answer26
viewsA: Relation between tables of the same values in the columns
A solution (among others) is to use a sub-select with not exists, check the existence of values in the sub-select select * from tabela2 where not exists (select null from tabela1 where…
-
1
votes1
answer209
viewsA: How to create a query with a non-existent and error-free table
Try something like : CREATE OR REPLACE PROCEDURE get_record (user_name IN VARCHAR2, service_type IN VARCHAR2, record OUT VARCHAR2) IS query VARCHAR2(4000); BEGIN BEGIN -- Following SELECT statement…
-
0
votes1
answer113
viewsA: Query in sql server with different value depending on some variables
With a CASE a sum can be conditioned select T.Grupo Grupo , MAX(G.Descricao) "Grupo", sum(E.QtdCat*S.FactConvEst) "M2 cativos", sum(case when E.Estado='N' then E.QtdCatS.FactConvEst else 0 end)…
-
5
votes1
answer8478
viewsA: Select most recent record from a given table - Postgresql
A solution can be a subselect with MAX SELECT codigosuprimento, numeroserie, data FROM public.estoque p1----faz uma primeira instancia da tabela where usado = '1' and numeroserie = '1201607048733'…
-
2
votes1
answer678
viewsA: How to create a Foreign key and Unique attribute at the same time?
A Unique Key Constraint Solves ALTER TABLE cliente_telefone ADD CONSTRAINT cliente_telefone_uk UNIQUE (numero); Documentation : https://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm…
-
1
votes2
answers754
viewsA: Method that uses a random value as parameter to swap the characters of a string
create or replace FUNCTION EMBARALHA_NOME (NOME IN VARCHAR2) RETURN VARCHAR2 AS primeiro_Nome VARCHAR2(100); embaralha VARCHAR2(100); nome_Cortado VARCHAR2(100); nome_Embaralhado…
-
0
votes1
answer528
viewsA: Calculation in SELECT oracle
Use the Decode and multiply by the value select sum(decode(incfgm,'+',1,'-',-1,0)*valeve) as valor from R046VER where numemp = 404 and numcad = 4170 and codcal = 1136 and codeve in (select codeve…
-
3
votes1
answer1116
viewsQ: Read a DBMS Procedure in ADVPL
It is possible to read a DBMS Procedure in ADVPL (TOTVS language) treating the return of a Cursor ? Is there any object ? The bank in question is Oracle. create or replace PACKAGE abc IS TYPE…
-
1
votes1
answer2389
viewsA: The result of the string concatenation is too long
It has a de facto limit, treat distinct and reduce size sometimes resolves, Try SELECT LISTAGG (V.pfnomereduzido || ' - ') within GROUP ( ORDER BY pf.pfid) FROM (SELECT DISTINCT…
-
1
votes1
answer96
views -
3
votes1
answer67
viewsA: Repetitions and combinations of values
--aligning select c1 c from tabela union all select c2 c from tabela union all select c3 c from tabela union all select c4 c from tabela union all select c5 c from tabela --reading as a table select…
-
0
votes2
answers107
viewsA: SQL result in columns
--a solution with CASE and MAX --the case makes pivot and max to secure in just one line --assuming the names of the columns SELECT AFD.CODEMP AS CODEMP, AFD.CODFUNC AS CODFUNC, AFD.DTMOV, max(case…
-
1
votes2
answers25
viewsA: Difficulty mounting query for Search in multiple columns
A solution could be create a view create view v_produto as select id_produto,user_id,carac_a carac from tab_produto union all select id_produto,user_id,carac_b carac from tab_produto union all…
-
2
votes1
answer208
viewsA: Changing a data type across the schema in the oracle
-- --LOCATE ALL COLUMNS OF TYPE VARCHAR2 AND SIZE 80 AND CHANGE TO 100 --LIST THESE COLUMNS SELECT * FROM USER_TAB_COLUMNS WHERE DATA_TYPE = 'VARCHAR2' AND DATA_LENGTH = 80 --SCRIPTING SELECT 'ALTER…
-
0
votes2
answers37
viewsA: Update table according to column quantity
Make an SQL of the type : select IDDoFuncionario , nomeDoFuncionario , 1 codigoDoDependente , nomeDoDependente1 nomeDoDependente from tabela_atual union all select IDDoFuncionario ,…
-
1
votes2
answers235
viewsA: Ensure query result random sorting
Has how many occurrences in the table ? I have done a similar report and this problem did not occur but there are thousands of data in the table, with few cases the chance to draw them is high. In…
-
0
votes1
answer436
viewsA: Sum a summed field - PL/SQL
Tried to : SELECT A FROM (SELECT DTAGENDA, AG.CODITPROD, CD, ROW_NUMBER() OVER(PARTITION BY AG.CODITPROD, CD ORDER BY DTAGENDA) AS RANK, sum(QTAGENDA) QTD_AGENDA, SUM(QTAGENDA) OVER(PARTITION BY…
-
1
votes1
answer57
viewsA: Select extreme values from a certain range in Mysql
MAX and GROUP BY select hora , min(id) min_id , max(id) max_id from tabela group by hora GROUP BY is used when using aggregators as sum , count , mean, standard deviation , maximum and minimum , can…
-
0
votes3
answers2459
viewsA: Doubt SQL Working Days Query
Select dbo.dias_uteis('01-04-2017','30-04-2017') abril, dbo.dias_uteis('01-05-2017','31-05-2017') maio, dbo.dias_uteis('01-06-2017','30-06-2017') junho I believe this is it…
-
1
votes3
answers124
viewsA: Update to CD_SETOR column values in all tables found with column
Improving the Robinho’s response DECLARE VN_ALTERADOS NUMBER := 0; BEGIN FOR R IN (select 'update '||owner||'.'||table_name|| ' set '||column_name||'= 406 where '||column_name||' in (20)' LINHA from…
-
0
votes2
answers2463
viewsA: Mysql select multiple columns from the same table with conditions
CASE , something like that select (case when tipo = 1 then cpf when tipo = 2 then cnpj else null end) cpf_cnpj, ...
-
2
votes3
answers5999
viewsA: several values for a column in Where
and Produto in ('2933','4044,'5987','9432','2599','7845') or and ( Produto in ('2933','4044') or Produto in ('5987','9432') or Produto in ('2599','7845') ) but I didn’t understand the question of…
-
1
votes1
answer307
viewsA: Build school report
Select max(case when bimestre = 1 then nota else null end) 1bim, Max(case when bimestre = 2 then nota else null end) 2bim, ... From notas Could , be the case , group by student also.…
-
8
votes1
answer3970
viewsA: How to calculate a person’s age in Oracle SQL?
Do NVL(Floor(Months_Between(DtCalc,DtNasc)/12),0) is a solution I actually use a Function for this.
-
0
votes2
answers34
viewsA: Problem in SQL statement
... "SELECT U.*, A.*, G.*, ( SELECT COALESCE(SUM(V.countView), 0) FROM usuario_visitas V WHERE V.codUsuario = U.codUsuario ) as total_visitas, ( SELECT COALESCE(SUM(F.countVotos), 0) FROM…
-
1
votes2
answers517
viewsA: LISTAGG function returning repeated values
The behavior is the same , follows an example : SELECT CLIENTE, CAST (LISTAGG(PRODUTO, ';') WITHIN GROUP (ORDER BY PRODUTO desc) AS VARCHAR2(30)) PRODS --CAST SÓ PARA O SQL PLUS FROM ( SELECT 'A'…