Posts by Motta • 3,568 points
233 posts
-
0
votes1
answer28
viewsA: Oracle Year in full
The documentation itself says that the return will always be in English.
-
0
votes1
answer28
viewsQ: Oracle Year in full
This question is allowed ? Simple , in thesis SELECT TO_CHAR(SYSDATE, 'YEAR','NLS_DATE_LANGUAGE=PORTUGUESE') FROM DUAL; You should return Two thousand and twenty-one. But returns TWENTY-TWENTY-ONE…
-
0
votes1
answer28
viewsA: Use more than one value to use parameter in an Oracle function
--sem testes todavia --criação de uma view --sequencia retroativa SELECT (LEVEL-1)*-1 FROM DUAL CONNECT BY LEVEL <= 12; --gerando datas nisto SELECT ADD_MONTHS(TRUNC(SYSDATE,'MM'),(LEVEL-1)*-1)…
-
0
votes2
answers436
viewsA: Select subtraction in columns of different tables
Solution assuming there is always caution, but using Uter Join select m.cod_material,(m.qtd - c.qtd) qtd from (SELECT cod_material, (SUM(quantidade) qtd FROM lista_geral WHERE local = 'xxx' GROUP BY…
-
1
votes2
answers90
viewsA: I need to create a report that sums up values by category and groups this information according to origin
try with CUBE or ROLLUP , generates a "running total" I had no way to test however. SELECT UNIDADE, TURMA, SUBSTRING(TURMA,1,2) GRUPO, SUM( CASE WHEN CODTX IN ('000','001','002') THEN VALORPAGO ELSE…
-
0
votes1
answer44
viewsA: Group by month (in each column) SQL
This should work, CASE per month, maybe a Function mysql or sql-server (which BD is ?) type MONTH , EXTRACT gets better SELECT CASE WHEN CODGRUPOPROD LIKE '20%' THEN 'TALHA CORRENTE' WHEN…
-
0
votes1
answer24
viewsA: Error in the subselect of an sql script to generate update script bringing more than one line and giving errors
Without testing though, basic idea. BEGIN FOR R IN (SELECT DISTINCT PAR.CODPARC, PAR.RAZAOSOCIAL, PAR.CGC_CPF AS CPF_ERRADO, B.RAZAOSOCIAL RAZAOSOCIALCH, B.CNPJ_CPF AS CPF_CORRETO FROM…
-
-1
votes1
answer53
viewsQ: Oracle - Error : ORA-24263: Certificate , how to treat?
Sending https via Oracle Version Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production PL/SQL Release 12.2.0.1.0 - Production "CORE 12.2.0.1.0 Production" TNS for Linux: Version…
-
0
votes1
answer53
viewsA: How to Migrate Oracle 11G Long Raw Columns
The solution I made because I had such a legacy was something like : create or replace FUNCTION OBTER_TEXTO_XYZ (P_CHAVE IN NUMBER) RETURN VARCHAR2 IS /* FAZ A CONVERSAO NECESSARIA DO TIPO LONG RAW…
-
0
votes2
answers49
viewsA: HOW TO TREAT EMPTY VALUES IN SEVERAL COLUMNS?
Since this is the model: Take a view like CREATE VIEW OR REPLACE VIEW V_CLIENTE AS SELECT TRIM(OPERACAO) AS OPERACAO, TRIM(NOME) AS NOME, TRIM(CPF) AS CPF, RTRIM(TELEFONE) AS TELEFONE,…
-
1
votes1
answer41
viewsA: Oracle PL/SQL problem with AS
--creio que RESOLVA SELECT CC.NOME, SUM(1) AS QTD_CEL, SUM(CASE WHEN C.STATUS LIKE 'INATIVO' THEN 1 ELSE NULL END) AS QTD_INATIVO , MIN(C.NEW_COL) AS DATA FROM…
-
0
votes2
answers59
viewsA: How to make a SELECT to get the value of the last registered date
try something like ,CUS.CUSSEMICM -- esse código era só um teste porém traz qualquer valor do custo e não o ultimo ,CUS.CUSSEMICM ,CTE.QTDEST * CUS.CUSSEMICM AS CUS_SEM_ICMS -- esse é o calculo que…
-
0
votes1
answer35
viewsA: Filter search for only ID’s that have more than one POSTGRESQL linked record
Try SELECT (id, MAX(reduzido)) FROM contas INNER JOIN reduzidocontas ON contas.id = reduzidocontas.idconta AND reduzidocontas.ano = 2020 GROUP BY contas.id HAVING COUNT(DISTINCT reduzido) > 1 Not…
-
0
votes1
answer117
viewsA: Is there any way to get a JSON "result" using Oracle’s utl_http package
There wasn’t much mystery add res := utl_http.get_response(req); FOR i IN 1..utl_http.get_header_count(res) LOOP if nome = '"id"' Then VS_ID := valor; exit; end if; END LOOP;…
-
0
votes1
answer117
viewsQ: Is there any way to get a JSON "result" using Oracle’s utl_http package
Is there any way to get a JSON "result" using the package utl_http oracle ? The status code of the following routine returns 200 but would also return a "Sponse" with a JSON conformation, an…
-
0
votes1
answer335
viewsA: Time range between ORACLE SQL dates
Separe data e hora SELECT cd_atendimento, ds_atendimento, cd_valor, hr_atendimento FROM atendimento WHERE cd_atendimento IN ( 5587064, 5584065, 5587654, 4568985,…
-
0
votes1
answer63
viewsA: How to select from 2 id to bring Different Name
Something like that (as @Jmslasher said select nrprocesso, exp.nmpessoa as Exportador, imp.nmpessoa as importador, txdescricaomercadoria from processo inner join diitem on processo.idprocesso =…
-
1
votes2
answers71
viewsA: How to do WHERE with an incomplete name to find the record that in the bank is with the full name?
A solution is in the "form" to separate the name inputado in an array with several names for example SO-AND-SO array {FULANO, BELTRANO, TAL} eliminate the OF make a dynamic sql with the array (NOME…
-
1
votes1
answer52
views -
0
votes3
answers92
viewsA: Error using case clause inside a Where
I believe this is it ... in general CASE is used as a column but it can be used in Where. ... (CASE WHEN ST.cod_sistema = 'LS' THEN 'DA_GRP_TPO_SERVICO_LS'; WHEN ST.cod_sistema = 'IH' THEN…
-
-1
votes1
answer35
viewsA: Updating duplicate records
Try (untested) , didn’t I use the rm.bol_numerodocument assuming to be the table’s primary key ? Tip : set bol_idassociated as UNIQUE KEY. Note : Your sql seems right , tried negative < (COUNT(*)…
-
0
votes2
answers533
viewsA: Query data from two different tables and list without merging data
SELECT SUM(debito) debito, SUM(credito) FROM ( SELECT valor_total_pedido AS debito, 0 credito FROM tb_pedido_compra WHERE fornecedor = '$this->id' UNION ALL SELECT 0 debito, valor credito FROM…
-
1
votes1
answer45
viewsA: counting records by time interval
Try, maybe slow down SELECT T1.DATA_UTC , T1.MATS , COUNT(*) QTD FROM (SELECT o.data_utc, listagg(a.matricula, ', ') within group (order by a.matricula) mats FROM aeronave a JOIN aeronave_ocorrencia…
-
0
votes3
answers64
viewsA: Why using 'IN" to filter records along with a subselect returns records that have, in certain columns, equal values
This makes sense when applying some condition to subselect, for example filter only retail suppliers. In which countries we have retail suppliers ? SELECT Country FROM Customers WHERE Country IN…
-
0
votes2
answers288
viewsA: Sum the total value of a given column for each table - Mysql
try like this, you may need to do another group by/sum SELECT FORMAT(valor_receita,2,'de_DE') AS valor_receita, FORMAT(valor_despesa,2,'de_DE') AS valor_despesa, DATE_FORMAT(data_vencimento,…
-
0
votes2
answers114
viewsA: Advanced Firebird Join - I can’t join the tables I need and sort in the right result
Solution with Join SELECT SECCOD, SECDES, SUM(COMPRA) AS COMPRA , SUM(VENDA) AS VENDA FROM (SELECT A.SECCOD, B.SECDES, SUM(C.ITEVLRTOT) AS COMPRA , 0 VENDA FROM ITEM_ENTRADA AS CJOIN PRODUTO AS A ON…
-
0
votes1
answer64
viewsA: SQL - search data from different tables without relationship with Ids
--try SELECT pj.id, pj.cnpj FROM Pessoa_Juridica pj WHERE ( pj.cnae in ('6499999') or exists (SELECT null from cnae_secundario c where c.cnpj = pj.cnpj and c.cnae in ('6499999')) )…
-
0
votes2
answers32
viewsA: Query that returns sum where states are equal to X and other clusters
Or SELECT (CASE WHEN ESTADO NOT IN ('RJ','SP') THEN 'OUTROS' ELSE ESTADO END) ESTADO, SUM(VALOR_VENDA) FROM vendas GROUP BY (CASE WHEN ESTADO NOT IN ('RJ','SP') THEN 'OUTROS' ELSE ESTADO END) ORDER…
-
0
votes3
answers405
viewsA: Trigger that inserts automatic value in a record
CREATE OR REPLACE TRIGGER AD_TRG_INC_UPD_TGFPAR BEFORE INSERT OR UPDATE OF CLIENTE ON TGFPAR FOR EACH ROW BEGIN IF TRIM(:NEW.CLIENTE) IS NULL THEN :NEW.CLIENTE := 'S'; END IF; END; You could also…
-
0
votes2
answers86
viewsA: Consult the last 3 days in which there was record in the table
I think it’s something like that, I may have missed some detail of the syntax --1 SELECT distinct DataOp FROM OperacoesCaixa order by DataOp desc --2 select DataOp from ( SELECT distinct DataOp FROM…
-
1
votes2
answers62
viewsA: Union of select’s to return only 1 query
A solution can be by UNION , these solutions can generate slow queries however. SELECT SUM(TOTAL_CAR) TOTAL_CAR, SUM(TOTAL_FAT) TOTAL_FAT, SUM(TOTAL_PED) TOTAL_PED, COD_REPRESENTANTE, REPRESENTANTE…
-
0
votes1
answer26
viewsA: Insert random numbers through Precedent
--sequencia artificial SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10; --COM ALEATORIOS SELECT LEVEL handle ,DBMS_RANDOM.STRING('U',20) nome FROM DUAL CONNECT BY LEVEL <= 10; --COM INSERT…
-
1
votes3
answers137
viewsA: Using IF and UPDATE in a PROCEDURE to update a table
One way , tries to change , if nothing is affected if Inserts. BEGIN -- supondo nome_variavel a PK data tabela UPDATE co_t_conf SET valor_variavel = p_valor WHERE nome_variavel = p_variavel; IF…
-
2
votes1
answer85
viewsA: Division of SUM() into SQL Developer
Publishing and improving this solution ... (CASE WHEN SUM(FAITEMPE.PR_ORIGINAL) <> 0 THEN ( SUM(FAITEMPE.PR_UNITARIO) / SUM(FAITEMPE.PR_ORIGINAL) ) ELSE NULL END) as DIFERENCA_PERCENTUAL, ...…
-
0
votes0
answers51
viewsQ: Oracle DDL trigger , identify a "drop column"
In Oracle it is possible to create DDL triggers (Data Definition Language) something like create or replace trigger trg_ddl before ddl on database declare begin ... end; My , doubt , how to…
-
0
votes1
answer20
viewsA: Does anyone know why I can’t create FK?
The foreing key only connects to the Primary Key of the referenced table. https://en.m.wikipedia.org/wiki/Foreign_key…
-
2
votes2
answers45
viewsA: sql records = 0
--I do this trick, data that "doesn’t exist" -applies best to more complicated sqls --in this case an Outer Join solves as well select sum(total) total,nome from (SELECT count(*) as total,…
-
2
votes5
answers97
viewsA: Doubt with Select using ORDER BY
try with subselect and max select A.id_a, A.produto, A.tipo, B.id_B, B.descricao, B.dt_renovacao from tabela_A A,tabela_B B where B.id_B = A.id_a and B.dt_renovacao = (select max(B1.dt_renovacao)…
-
1
votes1
answer31
viewsA: SQL - Set Timestamp in Select
--minino select min("timestamp") from teste --maior que select * from teste t1 where "timestamp" > (select min("timestamp") from teste t2 where t2.uf =…
-
0
votes1
answer57
viewsA: Bring data regardless of the order of the numbers are found
1 your table would have : Cod , value , order Seria Select cod , valor From tabela Order by ordem 2 a 'gambiarra' : SELECT (codigo, Valor> BULK COLLECT INTO teste FROM tb_teste ( select 1,…
-
0
votes2
answers51
viewsA: Find out if people did not attend
Try with subselect and not exist SELECT armario.idcad FROM armario Where not exists (select null From presenca Where armario.idcad = presenca.id_cad And data_presenca > CURRENT_DATE()-10) The…
-
1
votes1
answer41
viewsA: How to find Wrapped objects in the database
Already published select type, owner, name from all_source where line = 1 and instr(text, 'wrapped') > 1; Source…
-
1
votes1
answer917
viewsA: Merge two queries in SQL Server
From what I understood a Join using the two sql as virtual tables , rest is to manipulate the aligned fields. SELECT * FROM ( SELECT MONTH(created) AS 'MES' , YEAR(created) AS 'ANO' , COUNT(Number)…
-
1
votes2
answers53
viewsA: Select floor() in oracle
The same select floor(4.5) from dual FLOOR(4.5) ---------- 4
-
0
votes1
answer45
viewsA: Count SQL repeats with INTERSECT and COUNT
--crie duas views do tipo create view v_tabela 1 as select '1' ordem,campo1 campo from tabela1 union all select '2' ordem,campo2 campo from tabela2 union all ... /*idem tabela2*/ --localizando…
-
0
votes1
answer110
views -
0
votes1
answer110
views -
1
votes2
answers112
viewsA: How to select 2 records in an SQL Table in a given sequence?
A solution ( sql only) SELECT * FROM dbchuva c1 WHERE [id] = id AND chuva_manha != 0" AND EXISTS (SELECT 1 FROM dbchuva C2 WHERE C2.id = (C1.id + 1) AND C2.chuva_tarde != 0) The EXISTS seeks the…
-
0
votes1
answer88
viewsA: Date range query in a given time
Use to_char and extract the time select TO_CHAR(FROM_TZ(TO_TIMESTAMP(TO_CHAR(DT, 'yyyy/MM/dd hh24:Mi:ss'),'yyyy/mm/dd hh24:mi:ss'), 'UTC') AT TIME ZONE 'AMERICA/SAO_PAULO', 'dd/mm/yyyy') as Data,…
-
0
votes1
answer303
viewsA: Average and filter by values above this mean
Calculate the overall average (average of averages) and then compare that of customers SELECT a.primeiro_nome FROM cliente AS a INNER JOIN pagamento AS b ON (a.cliente_id = b.cliente_id) GROUP BY…