Posts by Motta • 3,568 points
233 posts
-
1
votes3
answers1184
viewsQ: Read an Array or Bulk Collect as a table in an Oracle PLSQL
How to load an Array or Bulk into a PLSQL and then read this as a table. Example DECLARE VA_ARRAY ....DEFINIÇÃO DO ARRAY VN_QTD NUMBER; BEGIN -- TABELA01 É UMA TABELA FÍSICA EXISTENTE NO BANCO…
-
1
votes2
answers672
viewsA: Can I use IF inside FROM in Mysql?
A solution can be to create a view create or replace view v_pessoa_cliente as select cpf , nome from pessoa union select cpf,nome from cliente In the application do select * from v_pessoa_cliente…
-
2
votes2
answers91
viewsA: Query mix of oracle products
SIMPLIFYING NOTA_FISCAL_ITEM NUMERONOTA CODCLIENTE CODPRODUTO CAMERAS SELECT NUMERONOTA FROM NOTA_FISCAL_ITEM WHERE CODPRODUTO = 'CAMERA IP' SOLD TOGETHER SELECT * FROM NOTA_FISCAL_ITEM WHERE…
-
1
votes1
answer528
viewsA: Select field from next line
LEAD AND LAG Vide https://technet.microsoft.com/en-us/library/hh213125%28v=sql.110%29.aspx https://technet.microsoft.com/en-us/library/hh231256%28v=sql.110%29.aspx Using the query as a virtual…
-
1
votes2
answers2379
viewsA: Merge 2 selects into one, showing fields
Make an Outer Join using selects as virtual tables select * from (select ... concat ... ) s1, (select ... concat ... ) s2 where s1.concat (+) = s2.concat
-
2
votes2
answers47
views -
2
votes1
answer274
viewsA: SQL return in different columns, not rows
Doing the Pivot "in hand" select max(nome1) nome1 , max(nome2) nome2 from ( select u.nome_completo AS NOME1 , '' as nome2 from usuarios u where u.cracha = 5357 UNION select '' as nome1 ,…
-
1
votes1
answer347
viewsA: Filter data from multiple sql columns
select distinct coluna from ( select coluna1 coluna from tabela union select coluna2 from tabela union select coluna3 from tabela union select coluna4 from tabela )
-
1
votes0
answers635
viewsQ: How to make a Delphi-Oracle connection without Oracle Client
I have an application that was developed at a time we had a client-server system , with this there was always an Oracle client on each machine, now we have a 3 layer system and we no longer have…
-
7
votes2
answers4076
viewsQ: Simple example of how to generate an XML file on Oracle
One would have a simple example of a PLSQL Procedure (or documentation location) to generate an XML file based on a Schema (xsd), the data would be generated from an SQL query. In reality archives…
-
0
votes1
answer56
viewsA: SQL Query according to the Make a New Column
A possible solution is : Create a view that brings the two together tables create or replace view v_view as select cod,nome from tabela1 union select cod,nome from tabela2 Do the Join in the view…
-
0
votes1
answer150
viewsA: NOT IN SQL ( I want to select pilots who have never done a certain route) how?
Pilots who made route x select id_copiloto from Voo where cod_rota = 12345 union select id_comandante from Voo where cod_rota = 12345 Pilots not who made the route x select * from pilotos where…
-
3
votes1
answer57
viewsA: Understanding pl/sql syntax Oracle 11g
This %Type creates a variable with the same Type (type and size) as the field passed , in the val_parameter case of the table ts_odo.odo_controle_system , the advantage of this call and that if the…
-
1
votes2
answers391
viewsA: Filter MYSQL results according to the result of a line
[Tabela Respostas dadas] - id - ficha_id - resposta_id - pergunta_id For example, I need to know who answered "23" in the field ansosta_id when question_id = "81", answered in the other questions.…
-
0
votes1
answer435
viewsA: Dynamic grouping in report
I don’t know if this is it but I use "formulas" for this, for example : The report has a parameter ptipodedata and a formula f_data in defining the formula if ptipodedata = 'emissao' then…
crystal-reportsanswered Motta 3,568 -
5
votes2
answers2251
viewsQ: Oracle - How to force a field size on a Function return
I hope to be clear on this issue , I will define in a general way but I believe that enough 1) I create any FUNCTION that returns a VARCHAR, it does not interfere with the return 2) I create a VIEW…
-
0
votes1
answer1200
viewsA: How to perform procedures within another?
Use the command EXECUTE IMMEDIATE
-
1
votes1
answer246
viewsA: How to select smallest record by field
I believe this is it , min com Group by SELECT LIGACOES.ID ,ACORDO.ID_ACORDO ,ACORDO.DATA as 'DATA_ACORDO' ,LIGACOES.time_of_contact AS 'DATA_LIGACAO' ,MIN(ACORDO.data-LIGACOES.time_of_contact) as…
-
2
votes1
answer459
viewsA: Doubt about FK, Visual Studio database
Note that the size of the fields differ by 150 x 100, and being nchar (fixed size) this generates a difference. Try using the ID as a key in the tables (or a single field as CPF, barcode etc) and…
-
4
votes1
answer322
viewsQ: What is the most efficient way to connect an Oracle BD to a Mysql BD
Having the following scenario: a simple telephony application "writes" in a Mysql BD and an Oracle BD needed from time to time to search for information in this Mysql BD. Someone already did this?…
-
3
votes3
answers11223
viewsA: What is the MINUS command for and how it works on oracle
A succinct form is a "minus", the result of one query minus the other. Example : sellers who have not sold anything this month Select codigo from vendedores Minus Select codigo_vendedor From vendas…
-
2
votes1
answer759
viewsA: Oracle PLS-00103 error while doing INSERT
I think I have too many quotes .... follows the proposed solution , I could not test however SET SERVEROUTPUT ON DECLARE v_idEmp INTEGER := &numero_funcionario; v_nome VARCHAR(30) := &nome;…
-
0
votes1
answer84
viewsA: SQL how to make me return the number of negative intervals (INTERVAL of date)
Data is missing but it will be something like this testing only negative values select count(dif_dataativacao_fimdegus) soma from tabela where dif_dataativacao_fimdegus <= -1 or treating a column…
-
1
votes1
answer167
viewsA: How to keep data up to date in two tables?
The problem is that one Rigger fires the other, perhaps by creating an "origin" field you can address the issue IF old.`password` <> new.`password` AND new.origem <> 'DB2' THEN UPDATE…
-
1
votes1
answer204
viewsA: Consider the final term until the next day at 03 am using PLSQL
Something like that CREATE OR REPLACE TRIGGER TRG_TABELA BEFORE INSERT OR UPDATE ON TABELA BEGIN /*3 horas de "hoje" até 3 horas de "amanhã"*/ IF NOT (DATA BETWEEN (TRUNC(SYSDATE) + 3/24) AND…
-
0
votes2
answers192
viewsA: Value based on another field (Crystal Reports)
Repeating my answer, I think I misunderstood before What can be done in sql is to use a subselect SELECT CodItem ,DataCompra ,'Qtde' = SUM(Qtde) ,ValorUnit ,ValorTotal FROM tabela t1 WHERE…
-
1
votes1
answer574
viewsA: Graphics with Crystal Reports
Create a year field in your database or create formula for the Year, use Function Year() Enter a chart, type Line (Stacked lines) Na alteração de : Ano(formula ou campo) Data (formate exibição para…
-
1
votes1
answer996
viewsA: Sum by period in Crystal Reports
A solution may be : Create a formula f_soma In the rule If [Relatorio.data] >= DATE(2015,01,01) and Relatorio.data <= {?DataFinal}) Then Relatorio.Faturamento Else 0 In the report use the…
crystal-reportsanswered Motta 3,568 -
2
votes4
answers428
viewsA: Make SELECT return data in default language when no translation can be found
The solution with Function would be something of the type (without accurate syntax) FUNCTION OBTER_PAGINA (PID,PIDIOMA) SELECT TEXTO FROM TABELA WHERE ID = PID AND IDIOMA = PIDIOMA; SE LOCALIZOU…
-
2
votes2
answers10071
viewsA: Update to clear entire column
UPDATE TABELA SET DT_ATUALIZACAO = NULL But watch out: 1) There are triggers in this table , the update generates some demand on account of this operation ? Log etc 2) There is room for track Audit…
-
6
votes4
answers38774
viewsA: Sequential numbering of query results
On the @Bacco line, solution for the Oracle SELECT ROWNUM, t.campo1, t.campo2 FROM tabela t
-
2
votes3
answers389
viewsQ: How to find a string in a Crystal Reports file in windows
Basic situation : Crystal Reports of a directory use in some cases Stored Procedures as the Data Source, if I try to locate a Stored Procedure that exists say "My_procedure" by the Windows Find in…
-
1
votes1
answer1710
viewsA: GROUP BY and SUM at Crystal Report
One way to do it in the Cor is : Enter a Group CLIENT Enter the field of the name of Client Enter the lines of detail Ins1ra a Total (Button "sum" - Inseir Summary) , totals by group of the CLIENT…
-
2
votes1
answer649
viewsQ: How to Import a . er1 File into CA Data Modeling
I need to import an . er1 file from an older version of Erwin in CA Data Modeling (Community Edition). This is possible ? I tried to open direct and the options of "Import" unsuccessfully. Grateful…
-
1
votes2
answers2053
viewsA: List in each position, the employees who most had change of position
I think it works , without a BD to test Funcionario (cod_func, nome, cod_cargo) Cargo (cod_cargo, descricao) Historico (cod_cargo, cod_func, dataInicio, dataFim) --history of employees select…
-
0
votes2
answers872
viewsA: What is the best way to create a conditional foreign key Constraint?
Another way in this case would be a validation TRIGGER checking whether in the required condition the column meets the rule in question. I would still review the model because it may be a modeling…
-
0
votes1
answer324
viewsA: Error of consultation per month
It’s actually a formula, isn’t it? 2/L of the month stock ? Try SELECT V.CODFORNEC, V.FORNECEDOR, ROUND(V.JANEIRO, 2) JANEIRO, ROUND(V.FEVEREIRO, 2) FEVEREIRO, ROUND(V.MARCO, 2) MARCO,…
-
2
votes1
answer157
viewsA: How to get the records found and not found via IN
As the data do not exist one should resort to a "artifice" SELECT MATRICULA , NOME FROM ALUNOS WHERE ID_ALUNO IN (1,2,3,4,5,6) union SELECT MATRICULA,NOME FROM (SELECT ROWNUM MATRICULA, '' NOME FROM…
-
0
votes2
answers59
viewsA: Additional field in a Database table for removal
Create a DATA_DA_DEMISSAO field, signal its output and keep the history.
-
1
votes2
answers835
viewsA: Select only columns with values
As gmsantos suggested the clean solution is to normalize , but a dirty (and much) solution is simply to concatenate (converting the string) the columns the result will be only the columns with…
-
1
votes3
answers906
viewsA: Can a primary key be used on a foreign key?
Yes , an example : Nota_fiscal Numero (pk) Valor Date Nota_fiscal_item Number (pk) (fk nota_fiscal.numero) Num_item (pk) Desc_item Valor Understanding clear that the model is a great simplification.…
-
1
votes3
answers2134
viewsA: Compare Mysql Select Values
SELECT VALOR FROM TABELA WHERE ((TIPO = 1) AND (0.3 BETWEEN MINIMO AND MAXIMO))
-
0
votes2
answers460
viewsA: Extract Firebird Database with Predefined Layout
http://www.ibexpert.net/ibe/index.php?n=Doc.SystemObjects Broad question , but based on the metadata tables it is possible to create applications that assemble queries and based on these queries…
-
1
votes1
answer1292
viewsA: Fill a parameter field in Crystal Reports
This is not done in the CR but in the interface layer (when it is the case), in it before you can mount a "form" and in it you do searches in the BD for the parameter assembly, queries to typing…
-
0
votes4
answers2220
viewsA: How to select "1" or "2" depending on the column value, in PL/SQL?
Select decode(tipo,1,cpf,2,cnpj,null) campo From tabela A solution with DECODE. Helped!?
-
1
votes3
answers1971
viewsA: Records with recurring dates filtered for a period
A possible solution would be a procedure returning cursors or even views assembling these virtual tables, depending on the reuse of this information. But I do not see many inconveniences in…
-
5
votes2
answers114
viewsA: Do not register in the time range if it already exists
select * from compromisso where ( ('2014-08-23 19:00' between inicio and termino or '2014-08-23 23:00' between inicio and termino) or (inicio BETWEEN '2014-08-23 19:00' AND '2014-08-23 23:00' or…
-
2
votes2
answers3093
viewsQ: PLSQL know the amount of records within a "for cursor"
Inside the example below : Begin For r in (select * from tabela) Loop ..... ; End loop; End; Is there any direct way to know inside the loop how many records the cursor has ?…
-
1
votes2
answers654
viewsA: Performance of query LIKE in Mysql
Depending on the application an alternative could be made. Imagine a book sales website Let’s have a clear table of BOOKS (code, name, author, etc.) A table could be created LIVROS_PALAVRA_CHAVE…
-
6
votes3
answers13600
viewsA: How to get the value of a column corresponding to the maximum of another column?
select * from tabela t1 where data = (select max(t2.data) from tabela t2 where t2.codigo = t1.codigo) A solution using subselect (there are others)…