Combining 4 tables and prioritize the data in them?

Analytics
Enthusiast

Combining 4 tables and prioritize the data in them?

Hi All,

I am new to Teradata world and in trouble. I have a requirement, hope I will get help from you guys. I will try to be as much clear as I can, further clarification will be provided if needed.

I have three tables. The data in these tables needs to be prioritize depending upon the data in the fourth table. Then the higest priority data will be loaded to fifth table and that should be deleted from the tables it came from(1st or 2nd or 3rd table). Then I will use this highest priority data for some Insert/Update and will delete it from 5th tables as well. Till then there may be or may not be new data in two of the three original tables. Need to do the prioritization again, Highest priority data to be deleted from the table it came and then will be used for the same purpose and so on.

all the tables having more than 30 columns except Priority table(4th one) which will have two columns depending upon which we need to decide the priority.

table 1:

LL     RCL     1234

ML    CVC     1234

table 2:

ML    RCL      5678

LL     CVC      7899

table 3:

GT    ABC      7865

LL    RCL      0123

Table 4:

LL    RCL

LL    CVC

ML    RCL

ML    CVC

GT     ABC

so as per the table 4: LL  RCL is the highest priority and hence a row from Table 1 and a row from table 3 should come to table 5 and should be deleted from the respective tables. Table 5 data will be used for Update/insert and will be deleted from there and then we will start the process again with the remaining or any new data came in.

Thanks a lot in advance.

Thanks,

Terankit

8 REPLIES
Senior Supporter

Re: Combining 4 tables and prioritize the data in them?

Hi,

from which info do you derive that LL RCL has highest priority?

relational ables are not ordered....

Do you know from the values in table 4 in which table these are stored? for example LL RCL -> table 1 - is this allways the table for LL RCL or coud it be a different table?

What will be the volume to process?

Why do you load into three tables? Do all have the same layout?

Enthusiast

Re: Combining 4 tables and prioritize the data in them?

Terankit - How do you differentiate the priority by looking at the table-4?

Enthusiast

Re: Combining 4 tables and prioritize the data in them?

Hi,

Thanks for the reply and questions. I worked more on the requirement and the points given below will give the answer to your queries:

1)The priority table structure is now having 11 columns.Field_name(1-5),Field_value(1-5) and Priority.

2)Field_name will have the column name which will be available in all the three tables. All the columns need not to have values.

3)Field_value will be having the values corrosponding to Field_name and Priority will have priority in numbers.

ex.

F_n1 f_n2..f_n5 F_v1 F_v2..F_v5 priority 

A1    A2..space   TT      ABC..space 1

A1    A2..space   MM     BCD..space 2

A1    A2..space   TT      BCD..space 3

Where A1 and A2 are the column names in the three tables.

User can change the field name and their values. We need to check the priority 1 fields and values. Then serach those values in the three tables in respective columns. Put them in the fourth table,delete them from three tables.Do the stuff and delete them from fourth table.

Now,come back,check the priority table(value may change),check the first priority data in the three tables.If found,do the same else go for the second priority and so on untill all the data from the three table gets deleted.

There may be instance that we have some data in the three tables which are not in the priority table. Then we can take the entire data from all the three tables and process them in one shot.

The structure of the three tables are more or less same but not identical. The data will be loaded to the fourth table which can be derived from the three tables.

Thanks a lot. It may be confusing but I have tried to make it as clear as can. Will clear more if required. Thanks again for the help.

Terankit

Enthusiast

Re: Combining 4 tables and prioritize the data in them?

Hi Ulrich,

The answers to your questions:

From table4 we will get the information that depending upon what columns and what values under columns the priority is defined. That data can be anywhere in the three tables. These three tables are getting loaded from different applications.

Thanks,

Terankit

Senior Supporter

Re: Combining 4 tables and prioritize the data in them?

a possible way to do it

1. check if fourth table is not empty - which means something is to do.

    if not exit 4

2. have a tmp table - permanent or volatile - to store your highest prio values. Use OLAP functions to get the highest prio values.

3. Use the temp table and join it against the three source tables and insert / select into the 5th work table. 

4. Process further with you logic.

5. Delete via the first work table from the three source tables

    Delete from fourth table via work table.

Run the script as long as you not get exit 4.

Enthusiast

Re: Combining 4 tables and prioritize the data in them?

Hi Ulrich,

Table 4 is the priority table.Ven if it is empty means there is no priority then we can process the data in the first three tables in any order.(this is not an issue because its simple).

1)How will we get the highest priority data from the three tables?The priority will be defined by the fourth table depending upon the field names and corrosponding values in the 4th table. Please explain.

Thanks again for the response.

Thanks,

Terankit

Enthusiast

Re: Combining 4 tables and prioritize the data in them?

And just to summarize all the tables once again:

Table1,2,3-having all kind of data

Table4-defining priority.It contains the priority number,field name(which are present in all the three table) and the values in the fields

Table5-In this table we need to move the highest priority data from the three tables depending upon the 4th table.

Then we will do some operations.will delete the data from 1,2,3 and 5th table.not from the 4th table.

The process will be on till we will finish all the data from table 1,2 and 3 whether it is defined as priority 1,2 or nothing.

The aim is to process the data in the priority.

Thanks,

Terankit

Senior Supporter

Re: Combining 4 tables and prioritize the data in them?

I don't know if I understand all of your requirements and as you don't share concrete DDLs and SQL it remains a lot of guessing...

It would save a lot of time if you share ddls etc...

Suppose you are on 13.10 you might play arround with the followin code and get some ideas how it can be handled.

Not best SQL style I have to admit...

table setup:

create table priority_data 
( pk integer ,cola char(3) not null, colb integer not null , colc integer not null , valuesA decimal(15,2))
unique primary index (pk);

create table priority_ref
( cola char(3), colb integer, colc integer , priority integer)
unique primary index (cola,colb,colc);

create table tmp_priority
(
id integer, cola char(3), colb integer, colc integer, priority integer
)
unique primary index (id)
;

insert into priority_data values (1,'yes',1,1,15.3);
insert into priority_data values (2,'No',1,1,15.3);
insert into priority_data values (3,'No',2,1,15.3);
insert into priority_data values (4,'may',2,2,15.3);
insert into priority_data values (5,'may',2,1,15.3);
insert into priority_data values (6,'yes',2,1,15.3);

insert into priority_ref values ('yes',NULL,NULL,2);
insert into priority_ref values ('No',Null,1,3);
insert into priority_ref values ('MAY',2,1,1);

One cycle logic (for one table but handling 3 of them would be no difference).

I used only a select * you would do an insert / select into table 5...

Execute this code several times and you should see the impact on the results...

delete from tmp_priority;

insert into tmp_priority
select cast(1 as integer) as id, cola, colb,colc,priority
from priority_ref
qualify min(priority) over () = priority
;

select * from tmp_priority
;

select *
from priority_data
where (((select cola from tmp_priority where id = 1) is null or (select cola from tmp_priority where id = 1) = cola)
and ((select colb from tmp_priority where id = 1) is null or (select colb from tmp_priority where id = 1) = colb)
and ((select colc from tmp_priority where id = 1) is null or (select colc from tmp_priority where id = 1) = colc)
) or (select count(*) from tmp_priority where id = 1) = 0
;

delete
from priority_data
where (((select cola from tmp_priority where id = 1) is null or (select cola from tmp_priority where id = 1) = cola)
and ((select colb from tmp_priority where id = 1) is null or (select colb from tmp_priority where id = 1) = colb)
and ((select colc from tmp_priority where id = 1) is null or (select colc from tmp_priority where id = 1) = colc)
) or (select count(*) from tmp_priority where id = 1) = 0
;

delete from priority_ref
where (priority)
in (select priority from tmp_priority where id = 1)
;