View based on two tables

Database
Enthusiast

View based on two tables

I am totally new to Teradata. I need help with SQL table syntax for creating, inserting tables and view.

I do have one requirement.

1) Create one table called : Employee contains Emp_ID, First_Name, Last_Name etc..

2) Create second table called: Sales - Emp_ID, Sales_amout, Date etc.

Primary Key: EMP_ID on employee table

Foreign key: EMP_ID on Sales table reference Employee table.

I need to create view based on above two tables. I also want to insert data directly from view.

Can anyone please? 

Its urgent.

Thanks all for your ehlp 

10 REPLIES
Senior Apprentice

Re: View based on two tables

You can't insert in a View with joins (at least in Teradata) and there's no workaround (there are no Instead Of triggers).

Enthusiast

Re: View based on two tables

Thanks Dnoeth for your reply.

Is there any way, if i insert/update records on sales table will automitically update Employee table as well?

Senior Apprentice

Re: View based on two tables

Of course there are Triggers, but they're hardly used in a DWH, this is usually done as a part of the loading process.

What exactly do you want to achieve?

Enthusiast

Re: View based on two tables

If i insert/udpate anything on Sales table, it should be populated into Employee table.

That's what i am exactly looking for. 

Senior Apprentice

Re: View based on two tables

"populated into Employee table" = aggregation?

Sales tables tend to be large with billions of rows, maintaining denormalized data will be a huge overhead. An Aggregate Join Index might help, then there's no need to update the employee table.

Enthusiast

Re: View based on two tables

Can you please paste the code?

I am not sure that can be achieve through Merge statement.

Thanks

Senior Apprentice

Re: View based on two tables

MERGE can't be used to process two tables at the same time.

Enthusiast

Re: View based on two tables

CREATE TABLE EMPLOYEE (
EMP_ID INTEGER,
EMP_NAME VARCHAR(200),
START_DATE DATE,

Primary Key (EMP_ID) );

CREATE TABLE Sales (
EMP_ID INTEGER,
SALES_AMT DECIMAL(10,2),
EMP_NAME VARCHAR(200)
);

ALTER TABLE Sales
ADD FOREIGN KEY (EMP_ID) REFERENCES Employee (EMP_ID);

MERGE INTO EMPLOYEE
USING SALES S
ON EMPLOYEE.EMP_ID = S.EMP_ID
WHEN NOT MATCHED THEN
INSERT (EMP_ID,EMP_NAME) VALUES (S.EMP_ID, S.EMP_NAME);

Does this work?

Senior Apprentice

Re: View based on two tables

This will not work, as a row must be inserted into Employee before it can be inserted into Sales.

It's a wrong approach, this should be done during ETL.