All Forums Database
taruntrehan 43 posts Joined 10/12
02 Oct 2014
Variables In Custom SQL Block

Hi All,
I am working on a application that prepares a dynamic sql blocks for execution.
The application is written in perl which prepares the set of sql statements.
Here is an example :

insert xyz
SELECT distinct p_val FROM abc c, def b where b.p_val = c.p_val and (c.create_dt between cast((select val_dt from custom_vars where name = 'v_min_p_val') as date format 'MM/DD/YYYY') and cast((select val_dt from custom_vars where name = 'v_max_p_val') as date format 'MM/DD/YYYY'));

For abc , the p_val variable is PI and create_dt is PPI. p_val is PI for def. However, this dynamic SQL does not leverage PPI because it is being dynamically fetched.
custom_vars is a table where i am keeping the variables and its values generated dynamically.
Is there a way that i can declare a variable in the sql block and replace the select statement for date ranges. That should be able to leverage PPI.
Let me know if more inputs are required.
 

Regards, Tarun Trehan http://allzhere.in
frnewbrough 41 posts Joined 03/08
02 Oct 2014

This should work. Put your parms in a derived table. 

INSERT	xyz
SELECT	DISTINCT p_val 
FROM	abc c, def b,
(SELECT 
*
(SELECT CAST(val_dt AS DATE FORMAT 'MM/DD/YYYY') AS val_dt1
FROM	custom_vars 
WHERE	name = 'v_min_p_val') a,
(SELECT	CAST(val_dt AS DATE FORMAT 'MM/DD/YYYY') AS val_dt2
FROM	custom_vars 
WHERE	name = 'v_max_p_val') AS DATE FORMAT 'MM/DD/YYYY') b) parms
WHERE	b.p_val = c.p_val 
	AND	(c.create_dt BETWEEN parms.val_dt1
	AND	 parms.val_dt2);

You could also simply select INTO two new variables which would work.

 
 

frnewbrough 41 posts Joined 03/08
02 Oct 2014

 
This may be a little clearer.

WITH
 min_val(val_dt) AS  (SELECT CAST(val_dt AS DATE FORMAT 'MM/DD/YYYY') FROM custom_vars WHERE	name = 'v_min_p_val' ),
 max_val(val_dt) AS  (SELECT CAST(val_dt AS DATE FORMAT 'MM/DD/YYYY') FROM custom_vars WHERE	name = 'v_max_p_val' )
 
SELECT	DISTINCT p_val 
FROM	abc c, def b,min_val,max_val
WHERE	b.p_val = c.p_val 
	AND	(c.create_dt BETWEEN min_val.val_dt
	AND	 max_val.val_dt);

 
 

taruntrehan 43 posts Joined 10/12
02 Oct 2014

Hi,
Thanks for your response.
1. I cannot declare a new variable because i am not in a stored procedure. It is a series of SQL statements called from perl script.
2. The derived tables concept and "with" option does not leverage PPI.
 

Regards,
Tarun Trehan
http://allzhere.in

frnewbrough 41 posts Joined 03/08
03 Oct 2014

Sorry you did point that out you were using pearl. What version of Teradata are you using? I tested a similiar construct on teradata 14.0 and it DID use partition elimination. Please post your table definition so I can see how your partitioning is defined. 
There are a few other options. Create a volatile table and insert the values. If your partitioning is defined correctly it should work.
 

taruntrehan 43 posts Joined 10/12
03 Oct 2014

Hi,
1. The PPI gets leveraged if the same variables are hard coded.
2. Even when i put my sql statements in a stored procedure and "declare variables and set them" , PPI is leveraged.
3. It is with these sql set of statements that issue occurs. Traditionally, in sybase i used to run the same queries in begin-end block and was able to declare and set variables. I am looking for something similar here.
Let me know if you still want to see the table definition.
Thanks.

Regards,
Tarun Trehan
http://allzhere.in

frnewbrough 41 posts Joined 03/08
03 Oct 2014

Yes please post the the table definition or more importantly the partioning definition. Did you try creating a volatile table with the values and then joining to it? Also you should be able to retrieve the variables into a variable in pearl and then reuse it to create your sql. I'm not a perl expert but I do that with bash all the time.

taruntrehan 43 posts Joined 10/12
03 Oct 2014

Hi,
1. I tried the volatile table and then joining it in my query. Doesn't leverage PPI.
2. doing back and forth in perl/bash. Certainly do able. Concern with that is i have around 150-200 sql statements and 5-6 queries that need to leverage PPI for better performance. It will clutter up the perl. Keep it as a low prioritized option for now.
3.

PRIMARY INDEX ( p_val )
    PARTITION BY RANGE_N(create_dt  BETWEEN DATE '2011-01-01' AND DATE '2014-12-31' EACH INTERVAL '7' DAY )

All my queries have date ranges between this partitioning range.

Regards,
Tarun Trehan
http://allzhere.in

taruntrehan 43 posts Joined 10/12
07 Oct 2014

Following approach to pull variables in perl and substitute to prepare next statement and execute.
Couldn't find any other option here.
Afraid that TD was unable to resolve PPI in such cases.

Regards,
Tarun Trehan
http://allzhere.in

You must sign in to leave a comment.