Variables In Custom SQL Block

Database
Enthusiast

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.

8 REPLIES
Teradata Employee

Re: Variables In Custom SQL Block

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.


Teradata Employee

Re: Variables In Custom SQL Block

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);
Enthusiast

Re: Variables In Custom SQL Block

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.

Teradata Employee

Re: Variables In Custom SQL Block

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.

Enthusiast

Re: Variables In Custom SQL Block

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.

Teradata Employee

Re: Variables In Custom SQL Block

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.

Enthusiast

Re: Variables In Custom SQL Block

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.

Enthusiast

Re: Variables In Custom SQL Block

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.