Consult a segmented field

Question

Consult a segmented field

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have two tables which one is created from the data of the other fields:

CREATE TABLE1(
CAMPO1 VARCHAR2(15),
CAMPO2 VARCHAR2(15),
CAMPO3 VARCHAR2(5),
CAMPO4 VARCHAR(20));

CREATE TABLE2(
CAMPO1 VARCHAR(20),
CAMPO2 VARCHAR(30));

I want to insert table 1, table 1 in the following way:

table1.field1 inside table2.field1
table1.field2, table1.field3 and table1.field4 within table2.field2.

The problem is that table2.field2 only accepts 30 characters (and I can not directly alter with alter table the size of this field since I can not modify the table) and I want to put 40. I found a way to solve-it and is with dynamic querys:

querySql in varchar2;
queryInsert in varchar2;
queryValues in varchar2;
queryValuesTab1 in varchar2;
queryValuesTab2 in varchar2;

queryInsert := 'INSERT INTO TABLE2(campo1,campo2)';
queryValuesTab1 := 'SELECT campo1, campo2||campo3 from TABLE1';
queryValuesTab2 := 'SELECT campo1, campo4 from TABLE1';

v_num_reg in number := 1;
queryValues := queryValuesTab1;
querySql := queryInsert || ' ' || queryValues;
execute immediate querysql;
rc := sql%rowcount;

v_num_reg in number := 2;
queryValues := queryValuesTab2;
querySql := queryInsert || ' ' || queryValues;
execute immediate querysql;
rc := sql%rowcount;

Now the next step I would like to make would be to consult table2. The problem is that when the data has been entered in table2.field2 these have been entered segmented. How can I recover all this data (the 40 characters) and not just the 30 characters accepted by table2.field2?

Many thanks !!!

sql oracle oracle-sqldeveloper

asked by moch 22.11.2016 в 17:30

source

2 answers

Perform FIltro by rowIndex JSF Image Management MVC VB.NET

score 0 · Answer 1

First, there was no need to use dynamic queries. The inserts you made are equivalent to executing the following 2 sentences:

INSERT INTO TABLE2(campo1,campo2)
SELECT campo1, campo2||campo3 from TABLE1;

INSERT INTO TABLE2(campo1,campo2)
SELECT campo1, campo4 from TABLE1;

Now the problem is that when you say that the data is segmented, what you mean is that you have created 2 records in the table TABLE2 for each record in the table TABLE1 .

And although it is possible to join the 2 records using campo1 as a reference to be able to reconstruct campo2 with the 40 characters, the query will be extremely fragile, for at least the 2 following reasons :

There is no way to determine the order of the 2 records in order to collect campo2||campo3 before campo4 .
There is no guarantee that only 2 records share the same value for campo1 .

If you do not mind these problems, you can use the following query:

select campo1, min(campo2) || max(campo2) as campo2
  from table2
 group by campo1

... but do not be surprised if the values do not come out 100% as you expect.

The truth is that what you are doing by segmenting the data seems a very bad idea that you will regret later. The best thing is to modify your tables to accommodate the data you need in the right way. I know you said:

and I can not modify the size of this field directly with alter table since I can not modify the table

But the truth is that there is always a way to do it if you talk to the right person who has access and if you explain why you need it.

Additional note

I see that the definition of your tables uses a mixture of the types varchar and varchar2 . In Oracle, it is recommended that you favor the type varchar2 .

score 0 · Answer 2

If you do not want to request that the table be altered, which makes me dull, anyway. Observing the example of the structure of the tables, I suppose that in each of them the field1 is an id which in table1 is 15 and in table2 it is 20, what you have to do is a smoke but it will work for you. Regards; I hope it serves you.

... queryInsert := 'INSERT INTO TABLE2(campo1,campo2)'; queryValuesTab1 := 'SELECT TRIM(campo1)||00001, campo2||campo3 from TABLE1'; queryValuesTab2 := 'SELECT TRIM(campo1)||00002, campo4 from TABLE1'; ...