|
|
|
Junior Member
 
Group: Forum Members
Last Login: 11/11/2009 1:12:32 PM
Posts: 14,
Visits: 39
|
|
Hello i need help on creating a Stored procedure from the following
here is the view that is available please help me to create store procedure out of it......
Replace view view_name
as
sel col_1, col_2..........column_15
from
(
sel col_12, ...............col_15
from
tablename A1
left outer join
(
sel col1,col2............col15
from tablenme_2 A2
inner join
table_12221 A3
on A2.condition_1 = A3.cond_1
)
left outer join
(
...
.........
.........)
Goes on further......with left outer joins.... and sub queries joinin some more tables...
-Thank you
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
Hi,
Can you specify what exactly you are trying to do with stored procedure..?
Following is the simple syntax for SP:
CREATE/REPLACE PROCEDURE Database_Name.Procedure_Name
(IN In_Var1 INTEGER, OUT Out_Var1 CHAR(30))
BEGIN
---
---
---
END ;
To call a SP use "Call Database_Name.Procedure_Name(1,var3);"
If you like to explore more on SP do refer teradata PDFs.
Regards,
Balamurugan
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
Hi,
Yes it should be included anywhere between BEGIN and END..
You can use view in a SP similar to any table...
like...
Select col1 from DB_name.view_name
You can also assign return value of a select statement into a local variable in procedure by using SELECT INTO statement...
Refer "SQL Stored Procedures and Embedded SQL" PDFs in http://www.info.teradata.com/DataWarehouse/eTeradata-BrowseBy-Results.cfm?pl=&PID=&title=%25&release=&kword=TDBS13.0&sbrn=7&nm=Teradata+Database+13.0
or in any teradata version at www.info.teradata.com
Regards,
Balamurugan
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
Hi,
It seems you are expecting to include your View DDL statement in SP.
Following are the few examples of DDL statements in various Mode:
1. Implicit Transaction Mode:
REPLACE PROCEDURE Proc_Name()
BEGIN
REPLACE VIEW View_Name as
(
--------
);
END;
2.Explicit Transaction Mode:
REPLACE PROCEDURE Proc_Name()
BEGIN
BEGIN TRANSACTION;
REPLACE VIEW Vie_Name as
(
--------
);
END TRANSACTION;
END;
3. ANSI Mode:
REPLACE PROCEDURE Proc_Name()
BEGIN
REPLACE VIEW Vie_Name as
(
--------
);
COMMIT WORK; /* This is required in ANSI MODE */
END;
Generic rule is DDL statement should be the last statement in transaction, but in normal Teradata mode this is not a problem, because each request by default is a stand alone transaction.
Regards,
Balamurugan
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/11/2009 1:12:32 PM
Posts: 14,
Visits: 39
|
|
here i need to create a new stored procedure ...for which the view is available...
in that case do i need to create a new sp??
i think what have u given is including a DDL statement in a already existing Stored procedure..? isnt so...
please correct me if im wrong!
-Thank you
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
Hi,
The 'REPLACE PROCEDURE Proc_Name' command will create a new Procedure if the Proc_Name is not exist, if exist it will replace with the new procedure code with the same object name. It works similar to CREATE/REPLACE VIEW commands.
So the sample which I gave can also be used to create a new SP.
To make it simple you can rename 'REPLACE' with 'CREATE'.
please be more clear on what you required, I couldn't get you.
Regards,
Balamurugan
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/11/2009 1:12:32 PM
Posts: 14,
Visits: 39
|
|
Ok sure i will get back sooner with exactly what i need....even i got doubts at my end..wil get back once im clear about it ...
thanks for your tremendous help...appreciate it!
-Thank you
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/11/2009 1:12:32 PM
Posts: 14,
Visits: 39
|
|
since the view has already been created..can i do this..
REPLACE PROCEDURE Proc_Name()
BEGIN
sel * from view_name
END;
-Thank you
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
You can do like this.. but by just doing 'select *' in SP you will not be able to see any return values unless you have any output variable... but you can try with other operation within SP.
Add ';' at the end of your select statement.
do execute your SP in your environment, so that u can see the result and correct your code then and there...
|
|