Hello, 

I have the following two questions please:

Hi ,

I would like to concatenate a column data (multi rows) into one string inside a Stored Procedure.

I’ve succeeded getting what I want using CTE (with recursive) but I can’t use it in my SP.

Is there a way to use CTE within a Stored Procedure?

Can anyone please help? Below code can finish running with no error, but how to make it to display the records, just so I know I'm getting what I want. Thanks!

replace PROCEDURE dlcna_cateam.sp_test3 

(

IN SRC_DB_NM VARCHAR(30)

, IN SRC_TBL_NM VARCHAR(30)

)

DYNAMIC RESULT SETS 1

BEGIN

Hello,

I have been struggling with this concept and any help would be highly appreciated. Do we need a stored procedure here? here is a small mock up example, however this will be implemented on a large scale.

Table 1

Hello,
 
I have been struggling with this concept and any help would be highly appreciated. Do we need a stored procedure here?
 

Table 1

Hallo all,
I have created a Java external procedure that returns a dynamic result set. I can successfully call it and it returns the correct data. Now how can I store this returned data to a new (temporary) table?

SELECT 
  vw_TPDDP.System_NME
  ,vw_TDAD.Date_YYYY_FMT
  ,vw_TDAD.Date_MM_FMT
  ,vw_TPDDP.Company_CDE
  ,SUM(vw_TPF.AMT) Amt
FROM 
  vw_TDAD 
   INNER JOIN vw_TPF 
   ON (vw_TPF.Date_DIM_ID=vw_TDAD.Date_DIM_ID)
   INNER JOIN vw_TPDDP 
   ON (vw_TPF.Profile_DIM_ID=vw_TPDDP.Profile_DIM_ID)
WHERE 
  (  vw_TDAD.Date_YYYY_FMT  =  '2015'

I want to extract meta data for stored procedure from dbc tables something like this.
 

select

a.name AS [ParameterName],

a.parameter_id AS [ParameterID],

TYPE_NAME(a.user_type_id) AS [ParameterDataType],

a.max_length AS [ParameterMaxBytes],

I want to implement a continue handler in the stored procedure for query that can spool out.

I check in pdcrinfo. SqlState_Codes there is no sqlstate associated to spool out error 2646. 

Query is in below form and it gets spooled out if values for  column are skewed.

INSERT INTO TAB1(col_value,row_count)

Is there a method, at compile time, to have the stored procedure resolve a volatile table that is not created within the stored procedure?

Hi,
I need to write a Stored Procedure to compare two Environment's(eg. Dev1_stg with Dev12_stg) Tables, Stored Procedure, UDF, Especially Views. Can any one suggest how can i approach this concept in Stored Procedure? 
I am able to do this comparison for Tables, using Dbc.ColumnsV. but for Stored Procedures, Views?
Thanks!
 

Hi ,

I am using a Stored procedure in teradata , in which multiple updates are written. I want to generate log file for Stored Procedure. so that i can come to know how many rows are getting updated by each update statement.

is there any why to implement this. 

Using TD 13.10.
Is there a way to grant use to user id for a Stored Procedure and effectively HIDE the tables below it?
Goal:

We have the column defined as below
beg_ts TIMESTAMP(6) WITH TIME ZONE.
The column when updated via BTEQ, MACRO gives the output as below
8/21/2015 23:22:55.320000-07:00 (at local timezone)
when updated via stored procedure gives the output
UTC Time - 8/22/2015 06:22:39.360000+00:00 (at gmt)

Hi,
I am very new to stored procedure and I need some clarification on the code which I have attached.

Hi,
 
I have createda simple stored procedure to truncate and load a target table and I am passing schema name as parameter.
When I run the procedure in SQL assitant it executes correctly, but when it is invoked through informatica, it gives following error.

Hi,
How do I create a cursor within a cursor inside a stored procedure? Is it possible? There should be a cursor A and cursor B wherein cursor B uses data fetched from cursor A. I have tried a WITH RECURSIVE inside a cursor but it fails.
Thanks in advance!

Hello,
I need to execute a Teradata stored procedure in SQL Server.
I tried so far the following:
1) exec ('call CIONESCU.stp_agg_3 ();') at [Teradata_CIONESCU]
2) select 1 from openquery (Teradata_CIONESCU,'call CIONESCU.stp_agg_3 ();')

Below is the statement that is a part of a stored procedure.
 
SET NOTNULLKEYLIST=' COALESCE('||OREPLACE(pkcollist,',',',''-1''),COALESCE(')||',''-1'') ';
 

I am working on a stored procedure for dropping all sessions for a specific user and I have written a SP to do this.  the SP creates but it fails when I run it with an id.  I can get the generated SQL out of DBQL and it runs but the SP fails.
 

Hi
   Is there a way by which we can declare variables in a loop dynamically. I would like to create a vaiables like Var1,Var2,Var3....as and when a new loop begins. If the loop executed 4 times, four such variables has to be created. Kindly help me on this. Thanks.
 
Regards
Arun

Hi
I have a requirement in in which I would like to call a stored procedure from Cognos, passing an ID
to the SP. The SP would retrieve data from a table and then loop through the data, formatting it.
Here is the basic idea:
Cognos calls SP with ID "ABC100".
SP retrieves following data:
100  Julus Caesar

Good day!
I've got several databases which contain tables of data; all these databases have the same table names and table structures, only the stored data differs.

The following procedure can be called with no errors when the Procedure input parameter is not used in the WHERE clause, but gives the following Syntax error when the input parameter is used:

3706: Syntax error: expected something between a string or a Unicode character literal and the 'AT' keyword.

Hi,
I am writing a stored procedure in which i need to drop a table recreate it and keep the defn of the table in an audit table. So, firstly, i am creating backup table as the original table with no data. then dropping the original table and recreating the original table with backup table. This is quite simple.

Hi Guys,
I need some info on looping in Teradata with below requirement. Anny help will be appriciated. Thanks in advance,
 
source field is string which is having below format,

Hi,
I was wondering if there are work-arounds for creating stored procedures in Teradata Aster without using SQL-MapReduce.
Thanks
Federico

Hi,

Can someone elaborate on the SQL SECURITY INVOKER option in stored procedures? I need to create a stored procedure and ensure the privilege(i.e. the privilege on the underlying database objects inside the procedure) of the user calling the procedure is always checked and I think the SQL SECURITY INVOKER option achieves that. 

Dear All
I cannot manage to get this stored procedure to work. I still get a compile error: SPL5000:W(L8), E(3807):Object 'TNAME' does not exist.

Is it possible to create stored procedures in Aster?  Or do anything like a SAS macro?  I would like to be able to call a section of code with a simple statement/reference.
Thank you in advance!

REPLACE Sample_Proc
(
  PRODUCT_NAME VARCHAR(20)
 
)
BEGIN
DECLARE vVar1 INTEGER;
/*OPERATION1*/
SET vVar1= 'VARIABLE'||PRODUCT_NAME
/*OPERATION 2*/
INSERT INTO LOG_Table||PRODUCT_NAME
SELECT 1,'Success'
END;
/*CALLING THE PROCEDURE*/

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 13031 StartFragment: 314 EndFragment: 12999 StartSelection: 314 EndSelection: 314

REPLACE PROCEDURE DBNAME.PROC(
IN PROD_NAME VARCHAR(50)
)
BEGIN
DECLARE SState INTEGER;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'

HI

When i execute the below code in a STORED PROCEDURE i get an syntax error saying "Expects somthing like an EXCEPT keyword UNION keyword or MINUS keyword between a between a String and YYYYMMDD". Please help me fix this syntax error. Thanks.
 
Replace Procedure Sample_Proc(
BEGIN

Hi,
Due to size of data (not to blow spool space) I need to chop that big dataset with cursor looping through X millions of rows.

Hi All,
 
Need to create a user using stored procedure. I am using the below code and getting an error
CREATE PROCEDURE abc.MyFirstProc(
   IN user_name VARCHAR(12),
    IN owner1 VARCHAR(12),
    IN password1 VARCHAR(12))
    
    BEGIN

Hi Folks,
 
I am new to Tera data. I am writing a stored procedure to generate an alert in alert table if certain condition is met. I am getting the below error when I am trying t execute the same.
 
5531: Named list is not supported for arguments of a procedure.
 

Hi Guys,
 
I need to create a function that reads its result from a table, so I need to use a SELECT statement to read the output. Something like that:

We have a requirement to mask sensitive information being loaded into the tables. Let's say we had the first three groups in place, the fourth one came in as a new requirement, and so had to make a coding change. We currently have logic similar to this.

Case when diagnosis_description like '%psych%' Then 'sensitive info'

We are recompiling stored procedures and some are erroing at execution because they use a different default date format. For example: The current default date format is YYYY-MM-DD, and the SP uses a literal defined as '11/02/2012'. The SP compiles ok but errors when executed. I am assuming the default date format was changed when this SP was compiled.

Hi,
I am not sure if this is the right category of the current issues we are facing right now. Unity Director had been implemented in our client together with the Teradata database implementation. I am not sure if this is a verified bug but below are the current scenario we are facing.

Dear colleges,
I had got some unexpected behavior from my stored procedure, probably because the lack of experience on my side . But could anyone help me explain.

Hi - I have Created it using SQL Assistant but getting error that ..'the proc. is in Teradta Mode' for some other user who was trying to execute it. 

After Changing View--> ANSI SQL and when i do a HELP PROCEDURE <<Proc_Name>> ATTRIBUTES, I see 'Transaction Semantics as 'TERADATA'.

Hi All,
I have the following SQL inside a stored procedure file and need some help.  I don't have access to the database so I can't test it out.

Hi all

I am using Teradata Express 13.10 & I am trying to write a stored procedure which uses multiple references to dataset defined using a 'common table expression.'

I have a test case below:

Hi everyone,
We are calling stored procedure from bteq, sometimes it exits with bteq exit code 8 but it doesn't give any error messages.
Here is the bteq log:
 

.logon TD_2700/TPTLoader,

 *** Logon successfully completed.

 *** Teradata Database Release is 14.00.03.03

Hello,
I am trying to parameterize on object referenced in my dynamic cursor. Let's say I have a table in three different databases called AuditLogs that I want to dynamically query the database to be able to check the row count based on the database name supplied in the stored procedure.
Something like this:
REPLACE PROCEDURE Test

Hi,
In Sybase, you can use IF EXISTS to validate the existance of a table, if it exists, a set of statements should be executed inside the stored procedure.
What is the equivalent code in Teradata SQL Assistant ?

And what is the issue in using if exists in teradata?

Thanks in advance.

I am new to Teradata and I am trying to convert and Oracle stored procedure.  I have been successful at getting the procedure to run but I need to know how to get the correct results from the results set.  In our Oracle procedure we run a set of queries to insert data into a table and then sent the results from that table in a ref cursor.