The commonly known technique to pass parameters to SQL statements at the system or session level utilizes tables (permanent or global temporary tables).
In this blog post, I'll show better performing techniques for system and user level variables using views and QueryBanding.
These solutions are based on TD13, but can also be used pre-TD13, although performance might be worse.

Global parameters

Let's start with a permanent table providing global level parameters:

CREATE TABLE global_params_tbl(
  dummy INT NOT NULL DEFAULT 1, 
  DBName VARCHAR(30) CHARACTER SET UNICODE NOT NULL, 
  Mydate DATE
) UNIQUE PRIMARY INDEX(dummy);

REPLACE VIEW global_params AS 
SELECT DBName, MyDate 
FROM global_params_tbl 
WHERE dummy = 1;

GRANT SELECT ON global_params TO PUBLIC;

INSERT INTO global_params VALUES('dbc', DATE '2011-03-01');

This is a single row table with one column per parameter. The dummy UPI (default = 1) is important for the optimizer, it provides uniqueness information and enables single-AMP access, which is forced by the view definition's "where UPI = 1" clause.

Any usage of the global_params view requires a cross join or a subquery.

SELECT d.* FROM dbc.DatabasesV AS d
CROSS JOIN global_params AS p
WHERE DatabaseName = p.DBName;

SELECT * FROM dbc.DatabasesV
WHERE DatabaseName = (SELECT DBName FROM global_params);

Both queries will show similar explains: a "single-AMP RETRIEVE step" into a spool which is then duplicated and finally joined.
There's only a difference when you need to access several parameters:
Each additional subquery results in new steps whereas the former still uses a single access.
 

New TD13 syntax

In TD13 there's the new "Scalar Subqueries everywhere" feature, which allows a different syntax variation.

Previously the Scalar Subquery had to be on the right side of the comparison, but now it will be recognized by the optimizer be on the left side, too:

SELECT * FROM dbc.DatabasesV
WHERE (SELECT DBName FROM global_params1) = DatabaseName;

This results in a better (simpler) explain, a "single-AMP RETRIEVE step" followed by a "DISPATCHER RETRIEVE step", which returns the value back to the dispatcher. There's no longer a join, the value is simply passed to the next step:

We do an all-AMPs RETRIEVE step from DBC.DBase in view
DatabasesV by way of an all-rows scan with a condition of (
"DBC.DBase in view DatabasesV.DatabaseName = :%SSQ14") ...

You might get the same explain using the old syntax, too, but the new syntax forces this plan; thus it's recommended to rewrite your queries in TD13.
 

Replacing the table with a view

Now let's have a closer look at this table, there's only a single row in it, hmmm...
Hey, you don't need a view on a table returning a single row; this can be done directly in the view definition, too:

REPLACE VIEW global_params AS 
SELECT 'dbc' AS DBName, DATE '2011-03-01' AS myDate;

When you use that view in a subquery, you'll get an error message:

Failure 3991 A FROM clause is missing for a SELECT subquery or view used in join.

But explaining the cross join reveals that the optimizer automagically removed the view access:

We do an all-AMPs RETRIEVE step from DBC.DBase in view
DatabasesV by way of an all-rows scan with a condition of
("DBC.DBase in view DatabasesV.DatabaseName = 'dbc'")

This is better than the previous plan; it's like a hard-coded value now utilizing existing statistics (this also works in TD12).

An example i especially like is a view based on today's date, which directly pre-calculates all possible start and end dates to simplify range based queries. 

REPLACE VIEW today AS
SELECT
  CURRENT_DATE AS today,
  today - 1 AS yesterday,
  first_of_month - 1 AS last_day_prev_month,
  today - (EXTRACT(DAY FROM today)-1) AS first_day_curr_month,
  ADD_MONTHS(first_of_month, 1) - 1 AS last_day_curr_month,
  ADD_MONTHS(first_of_month, -12) AS ly_first_day_curr_month,
  etc.

Maybe you already got a similar one using a "where calendar_date = current_date" condition on sys_calendar.calendar or your own calendar table. 

And this is how a where-condition will look like:

SELECT * FROM mytable AS m, today AS t
WHERE datecol BETWEEN t.first_of_month and t.yesterday;

Explain will show literal dates and skips the join.

 

Session parameters

Changing the previous table definition to Global Temporary Table permits session level variables:

CREATE GLOBAL TEMPORARY TABLE session_params_tbl(
  dummy INT NOT NULL DEFAULT 1, 
  DBName VARCHAR(30) CHARACTER SET UNICODE NOT NULL, 
  Mydate DATE
) UNIQUE PRIMARY INDEX(dummy)
ON COMMIT PRESERVE ROWS;

REPLACE VIEW session_params AS 
SELECT DBName, MyDate 
FROM session_params_tbl 
WHERE dummy = 1;

GRANT SELECT, INSERT ON session_params TO PUBLIC;

INSERT INTO session_params VALUES('dbc', DATE '2011-03-01');

Now it would be nice to have a class of "Temporary Views" on a session level, but that doesn't exist.
 

Applying QueryBanding in TD13

TD12 implemented QueryBanding which attaches name-value pairs to sessions or transactions, which is exactly what we need to pass parameters to SQL:

SET QUERY_BAND = 'dbname=dbc;MyDate=2011-03-01;' UPDATE FOR SESSION;

SELECT * FROM dbc.DatabasesV AS d
WHERE DatabaseName = getQueryBandValue(0,'dbname');

Explain looks promising:

We do an all-AMPs RETRIEVE step from DBC.DBase in view
DatabasesV by way of an all-rows scan with a condition of
("DBC.DBase in view DatabasesV.DatabaseName =
(SYSLIB.getQueryBandValue (0,'dbname'")

But when you run a query with QueryBanding against a large table you'll notice a large increase in CPU usage, so it's actually worthless for large data sets.
 

Replacing getQueryBandValue

The reason for this CPU boost is simple, the function is effectively called once per row, of course this is silly as a QueryBand never changes within a transaction.

A SHOW FUNCTION reveals: getQueryBandValue is defined as NOT DETERMINISTIC.
The dipdem.bteq script used for installing the system functions never specifies it because in older Teradata releases this option didn't have any effect. It was documentary only (defaulting to NOT DETERMINISTIC based on Standard SQL rules), but this changed in TD13.

Dipdem.bteq will be fixed in a coming patch level, but until that happens it's safe to REPLACE the functions manually as DETERMINISTIC.

And then it works perfectly:

We do an all-AMPs RETRIEVE step from DBC.DBase in view
DatabasesV by way of an all-rows scan with a condition of
("DBC.DBase in view DatabasesV.DatabaseName = 'dbc'")

Philip Edelsberg recently wrote about using QueryBanding in security views:
http://developer.teradata.com/applications/reference/using-teradata-query-banding-to-handle-security-views
 

Caution

This approach is only recommended on tiny tables or on TD13 with GetQueryBandValue redefined as DETERMINISTIC.
Explain must be checked if getQueryBandValue was replaced by the actual value if there's any expression based on it.
E.g. a type cast will work, but a CASE statement might prevent that.

 

P.S.

This is my first blog post and i would like to thank Richard B. for suggesting that ambigous blog title "Über SQL".
In german it's just "about SQL" ;-)

Discussion
Q 6 comments Joined 10/09
15 Mar 2011

Nice contribution to the site Sir

CarlosAL 6 comments Joined 04/08
16 Mar 2011

Not sure if the code provided is 'pseudocode' only for the explanation, but the view definition is declared with a WHERE clause 'WHERE UPI = 1;' and the real name for the column seems to be 'dummy':

BTEQ -- Enter your SQL request or BTEQ command:
CREATE TABLE global_params_tbl(
dummy INT NOT NULL DEFAULT 1,
DBName VARCHAR(30) CHARACTER SET UNICODE NOT NULL,
Mydate DATE
) UNIQUE PRIMARY INDEX(dummy);

*** Table has been created.
*** Total elapsed time was 1 second.

BTEQ -- Enter your SQL request or BTEQ command:

REPLACE VIEW global_params AS
SELECT DBName, MyDate
FROM global_params_tbl
WHERE UPI = 1;

*** Failure 5628 Column UPI not found in global_params_tbl.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

Cheers.

Carlos.

dnoeth 70 comments Joined 11/04
16 Mar 2011

Hi Carlos,
you're correct. This happens, when you modify code and do cut&paste.
I changed it, thanks.

Dieter

Dieter

robpaller 16 comments Joined 05/09
27 Mar 2011

Dieter,

It's great to see you contributing here. You bring a lot to the community and it will be great to follow along here. I look forward to your next post.

Rob

pabbaskhan 6 comments Joined 02/11
28 Mar 2011

HI can i know abt the ROWNUMBER AND ROWID in TERADATA .
That two functions are available in teradata or not.please let me know as soon as possible.

For example:
i have one table and that name is EMP

eno ename
1 A
1 A
1 A

So i want to update any one of them.

so please tell me abt that functions.

Thanks and Regards,
Abbas

dnoeth 70 comments Joined 11/04
28 Mar 2011

Hi Abbas,
you should post questions like this in the forum.

Standard SQL ROW_NUMBER exists, but can't be used in UPDATEs.
ROWID could be used, but it's use is disabled by default and only the dba can switch it on.

There is a reason why duplicate rows are considered evil :-)

Dieter

Dieter

vau 1 comment Joined 03/11
29 Mar 2011

post the teradata questions plese let me know

bdwebman 3 comments Joined 03/11
31 Mar 2011

Dieter: Great post. Any clues on how I might modify the following to work for TD 12 (Left Joins?)

select
id,
(select
case
when Category_1 = b.MaxScore then 'Category_1'
when Category_2 = b.MaxScore then 'Category_2'
when Category_3 = b.MaxScore then 'Category_3'
else 'NA'
end
from YourTable t where t.id = b.id
) as MaxScoreCategory,
MaxScore
from (
select
id,
max(Score) as MaxScore
from (
select id, 'Category_1', Category_1 from YourTable union all
select id, 'Category_2', Category_2 from YourTable union all
select id, 'Category_3', Category_3 from YourTable
) a(id, Category, Score)
group by id
) b

dnoeth 70 comments Joined 11/04
01 Apr 2011

Please post questions like this in the forum.

Answered in
http://forums.teradata.com/forum/analytics/select-failed-3706-syntax-error-expected-something-between-and-the-select-keyword

Dieter

Dieter

bdwebman 3 comments Joined 03/11
04 Apr 2011

thanks Dieter. Your suggestions worked to a T. Now just for a 14 category solution.. Yikes.. but thanks you so much!

bdwebman 3 comments Joined 03/11
04 Apr 2011

thanks Dieter. Your suggestions worked to a T. Now just for a 14 category solution.. Yikes.. but thanks you so much!

elenalee 1 comment Joined 05/11
16 May 2011

your suggestions are good
i use SQL a lot
still a want to know more about it
===========================
webdesign

pabbaskhan 6 comments Joined 02/11
20 May 2011

Hi ,

i want to learn Teradata MDM from where i have to start,currently iam working as a developer.
So can suggest me how to start and can you share any stuff.

Abbas

goldminer 9 comments Joined 05/09
29 Nov 2011

Hi Deiter,

I replaced GetQueryBandValue as deterministic and then ran the following:

SET QUERY_BAND = 'dbname=dbc;MyDate=2011-03-01;' UPDATE FOR SESSION;

explain SELECT * FROM dbc.DatabasesV AS d WHERE DatabaseName = getQueryBandValue(0,'dbname');

-----------------------------------------------------------

This is the explain:

1) First, we lock DBC.DB2 in view DatabasesV for access.
2) Next, we do an all-AMPs RETRIEVE step from DBC.DB2 in view
DatabasesV by way of an all-rows scan with no residual conditions
into Spool 3 (all_amps), which is duplicated on all AMPs. The
size of Spool 3 is estimated with low confidence to be 3,584 rows
(372,736 bytes). The estimated time for this step is 0.01 seconds.
3) We do an all-AMPs JOIN step from DBC.DBase in view DatabasesV by
way of an all-rows scan with a condition of ("DBC.DBase in view
DatabasesV.DatabaseName = 'dbc'"), which is joined to Spool 3
(Last Use) by way of an all-rows scan. DBC.DBase and Spool 3 are
left outer joined using a product join, with condition(s) used for
non-matching on left table ("NOT (DBC.DBase.LastAlterUID IS NULL)"),
with a join condition of ("DBC.DBase.LastAlterUID = DatabaseId").
The result goes into Spool 2 (all_amps), which is built locally on
the AMPs. The size of Spool 2 is estimated with no confidence to
be 23 rows (9,591 bytes). The estimated time for this step is
0.01 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 2 are sent back to the user as the result of
statement 1. The total estimated time is 0.02 seconds.

---------------------------------------------------------------------------

Is this the expected behavior after replacing the function as deterministic?

Thanks,

Joe

dnoeth 70 comments Joined 11/04
29 Nov 2011

Hi Joe,
yep, that's it.
Explain doesn't show SYSLIB.getQueryBandValue (0,'dbname'") anymore, but the actual value.

Dieter

Dieter

goldminer 9 comments Joined 05/09
30 Nov 2011

Perfect... just what I was looking for.... Thanks!

I'm in the process of developing a row and column level security scheme in our EDW. I believe I am going down the path of creating a security table by userid and roleid with dynamic views. I do have one question that you may be able to help me with....I have read in several pieces of documentation that macros can be used for column level security but I have never seen any more of an explanation or example of how this is accomplished. Do you know of any documentation anywhere that goes into this concept in more detail? Have you ever used macros for this purpose? If so, I would love if you could share your experiences with me. Actually any kind of knowledge dump around row and column level security tactics would be very helpful. If this blog is not the place for this, please let me know and I can share my email address.

I actually attended one of your compression sessions at Partners a few years back and really enjoyed it! I used your xcel compression for quite some time until we purchased CompressTool from Atana.

Thanks,

Joe

dnoeth 70 comments Joined 11/04
30 Nov 2011

Hi Joe,
you probably confuse me with someone else :-)
I never did a compression session at Partners (i just wrote a basic SP to calculate the most common values a few years ago).

But at Partners 2011 there was a presentation "An Efficient Row Level Security Implementation" by some guys from Intel.

I don't have much experience in implementing row level security, but when you use query banding you must take care that nobody can modify the queryband to switch to a more privileged user.

I never used macros for this purpose, i don't know how they should be more versatile than views.

Dieter

Dieter

goldminer 9 comments Joined 05/09
01 Dec 2011

When I execute a view with a function it appears as though the view results get cached and do not change unless(1) a certain amount of time passes, or (2) the view gets replaced. I am executing the following as one of the fields:

case when User = 'bo_rpt'
then case when (sel userid from core_t.security_table where userid = oreplace(GetQueryBandValue(0,'UserID'),'''')) = oreplace(GetQueryBandValue(0,'UserID'),'''')
then oreplace(GetQueryBandValue(0,'UserID'),'''')
else 'Restricted'
end
else User
end as id

When I reset the query band I do not pick up the new value in the function call unless one of the two scenarios exists that I outlined above. Do you know if the view result set gets cached and if there is a way to circumvent? I know this is very summarized but just thought I would throw out a question about view caching and if it exists... I may have to set up a ticket with the GSC to get to the bottom of this.

Thanks again,

Joe

dnoeth 70 comments Joined 11/04
01 Dec 2011

Hi Joe,
this is strange, Teradata doesn't cache any results, it's not MySQL :-)

Is the queryband set on transaction or session level?
Is the old value actually visible in explain after changing it?

If it was only (2) i'd assume that it's a parser bug, resolving GetQueryBandValue to the current value when the view is created. You can check this by a SHOW QUALIFIED SEL * FROM viewname.

Dieter

Dieter

goldminer 9 comments Joined 05/09
02 Dec 2011

Thanks deiter!

This is really interesting now... I decided to replace the getquerybandvalue as 'NOT DETERMINISTIC' instead of 'DETERMINISTIC'. Believe it or not, this eliminated the behavior described above. I am able to replicate this now at will. Open up a SQLA session then execute the following:

SET QUERY_BAND = 'Userid=UserA;' for session;

sel getquerybandvalue(0,'Userid');

sel getqueryband();

sel * from Table(getquerybandpairs(0)) as t1;

call syslib.GetQueryBandValueSP(0,'UserID',qbval);

The first time through the behavior will be as expected. Now execute the following:

SET QUERY_BAND = 'Userid=UserB;' update for session;

sel getquerybandvalue(0,'Userid');

sel getqueryband();

sel * from Table(getquerybandpairs(0)) as t1;

call syslib.GetQueryBandValueSP(0,'UserID',qbval);

The function GetQueryBandValues() behaves differently depending on if it is replaced as 'DETERMINISTIC' or 'NOT DETERMINISTIC'. When it is replaced as 'NOT DETERMINISTIC' the function does not pick up the new updated query band immediately. When it is replaced as 'DETERMINISTIC' it picks it up immediately. If you play around with it you will see what i am talking about.

Joe

dnoeth 70 comments Joined 11/04
02 Dec 2011

Hi Joe,
it's even worse, i tried it on a VMWare 13.10.00.14 with a single PE:
When i logoff and logon again, it's still using the old value from the previous session (without setting the queryband).

When i did the same with a BTEQ, it was remembering a different zombie value, so it's not based on the PE.

Also weird, both GetQueryBand and GetQueryBandPairs work as expected when DETERMINISTIC.

Dieter

Dieter

goldminer 9 comments Joined 05/09
02 Dec 2011

That is the same behavior that I experienced! The GetQueryBandValueSP also seems to return the correct value. Can you give me a good explanation of what deterministic and not deterministic actually mean? I have not been able to find a good explanation but have not checked all the function manuals yet.

Joe

dnoeth 70 comments Joined 11/04
02 Dec 2011

Hi Joe,
DETERMINISTIC simply means: when you call the function with the same input value multiple times, it will always return the same result.
NOT DETERMINISTIC e.g. when the UDF uses some random function or returns a timestamp based on an OS call.

Dieter

Dieter

manojchakri 5 comments Joined 03/10
19 Dec 2011

Hi Dieter,

The above post was very helpful. However, when I run some thing similar which will return more than one value from the sub query, it throws error 3669 on Teradata 13. Its syntax is giving error 3706 in Teradata 12. Any idea on how do we use this feature for more than one value returned from the subquery. This will be really helpfu. Thanks.

dnoeth 70 comments Joined 11/04
19 Dec 2011

If the subquery returns more than a singlke row you have to switch to IN instead of =, but this will always require a join.

Regarding the syntax error in TD12:
"In TD13 there's the new "Scalar Subqueries everywhere" feature"

Dieter

Dieter

manojchakri 5 comments Joined 03/10
21 Dec 2011

Thanks Deiter. So are you saying that we can not use the above sub query if we have more than one row getting returned from the sub query?
My example is this way:

SELECT * FROM MY_PARTITIONED_TABLE MPT
WHERE (SELECT MY_DATE FROM MY_PARAMETER_TABLE GROUP BY 1) = PARTITION_DATE ;

As per your suggestion we can only do the below if the sub query returns more than one row?
SELECT * FROM MY_PARTITIONED_TABLE MPT
INNER JOIN MY_PARAMETER_TABLE MPR
ON MPR.MY_DATE = MPT.PARTITION_DATE

dnoeth 70 comments Joined 11/04
21 Dec 2011

You can use IN (or "= ANY"), but the optimizer has to rewrite it as a join:
SELECT * FROM MY_PARTITIONED_TABLE MPT
WHERE PARTITION_DATE IN (SELECT MY_DATE FROM MY_PARAMETER_TABLE GROUP BY 1);

Dieter

Dieter

manojchakri 5 comments Joined 03/10
21 Dec 2011

Thanks Deiter for the quick reply. But I dont see the explain plan doing the "Dispatcher Retrieve step" for this.

dnoeth 70 comments Joined 11/04
21 Dec 2011

If you use "=" in TD13 you should see this step.
Could you post the actual query and the actual explain?

Dieter

Dieter

manojchakri 5 comments Joined 03/10
21 Dec 2011

Hi Deiter,

Here are the set of queries.

CREATE MULTISET TABLE MY_PARTITIONED_TABLE ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
col1 INTEGER FORMAT '9(5)' NOT NULL,
col2 SMALLINT NOT NULL,
partition_DATE DATE FORMAT 'YY/MM/DD' NOT NULL
)
PRIMARY INDEX ( col1, partition_date)
PARTITION BY RANGE_N(partition_date BETWEEN DATE '2011-09-18' AND DATE '2012-12-31' EACH INTERVAL '1' DAY ,
NO RANGE);

CREATE SET TABLE MY_PARAMETER_TABLE ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
col1 INTEGER,
my_date DATE FORMAT 'YY/MM/DD' NOT NULL
)
PRIMARY INDEX ( col1, my_date);

INSERT INTO MY_PARAMETER_TABLE
(COL1, MY_DATE)
VALUES (1, DATE '2011-09-18')
;

INSERT INTO MY_PARAMETER_TABLE
(COL1, MY_DATE)
VALUES (1, DATE '2011-09-19')
;

SELECT * FROM MY_PARTITIONED_TABLE MPT
WHERE (SELECT MY_DATE FROM MY_PARAMETER_TABLE GROUP BY 1) = PARTITION_DATE
;

SELECT * FROM MY_PARTITIONED_TABLE MPT
WHERE (SELECT MY_DATE FROM MY_PARAMETER_TABLE GROUP BY 1) in PARTITION_DATE
;

The last two queries failed with 3669 on TD 13.

SELECT * FROM MY_PARTITIONED_TABLE MPT
WHERE PARTITION_DATE = (SELECT MY_DATE FROM MY_PARAMETER_TABLE GROUP BY 1)
;

Even the above query failed with same error. So I ran the below query which did not use Dispatcher Retrieve.

SELECT * FROM MY_PARTITIONED_TABLE MPT
WHERE PARTITION_DATE IN (SELECT MY_DATE FROM MY_PARAMETER_TABLE GROUP BY 1)
;

Explain plan for the above:
Explanation
1) First, we lock a distinct ."pseudo table" for
read on a RowHash to prevent global deadlock for
.MY_PARAMETER_TABLE.
2) Next, we lock a distinct ."pseudo table" for
read on a RowHash to prevent global deadlock for
.MPT.
3) We lock .MY_PARAMETER_TABLE for read, and we
lock .MPT for read.
4) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from .MPT
by way of an all-rows scan with no residual conditions into
Spool 2 (all_amps), which is duplicated on all AMPs. Then we
do a SORT to order Spool 2 by the hash code of (
.MPT.partition_DATE). The size of Spool
2 is estimated with low confidence to be 5,184 rows (119,232
bytes). The estimated time for this step is 0.07 seconds.
2) We do an all-AMPs RETRIEVE step from
.MY_PARAMETER_TABLE by way of an all-rows
scan with no residual conditions into Spool 3 (all_amps),
which is redistributed by the hash code of (
.MY_PARAMETER_TABLE.my_date) to all AMPs.
Then we do a SORT to order Spool 3 by row hash and the sort
key in spool field1 eliminating duplicate rows. The size of
Spool 3 is estimated with low confidence to be 72 rows (
1,800 bytes). The estimated time for this step is 0.07
seconds.
5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to Spool 3 (Last Use) by way of an
all-rows scan. Spool 2 and Spool 3 are joined using an inclusion
merge join, with a join condition of ("partition_DATE = my_date").
The result goes into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with no confidence
to be 72 rows (2,232 bytes). The estimated time for this step is
0.22 seconds.
6) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.29 seconds.

Please let me know if you need more details.

dnoeth 70 comments Joined 11/04
21 Dec 2011

"So are you saying that we can not use the above sub query if we have more than one row getting returned from the sub query?"

YES.

You will never be able get this dispatcher retrieve step when there are multiple values returned

Dieter
How do you expect that "1 = (1,2)" is resolved?
! can't be 1 and 2 at the same time.

Dieter

manojchakri 5 comments Joined 03/10
21 Dec 2011

Ok. I am right now working around the same like below (but it may not be effective for long range of dates or longer gap between these partition dates).

SELECT * FROM MY_PARTITIONED_TABLE MPT
WHERE
(
(SELECT MIN(MY_DATE) FROM MY_PARAMETER_TABLE ) <= (PARTITION_DATE)
AND
(SELECT MAX(MY_DATE) FROM MY_PARAMETER_TABLE ) >= (PARTITION_DATE)
)
;

It would have been good if we had this Dispatcher Retrieve step implemented for multiple values returned for smaller volumes atleast.

Thanks again.

karyd 1 comment Joined 02/12
08 Feb 2012

Hi

Very good article!

My current concern is to get partition elimination to take place. I am new to Teradata.

So for Partition Elimination to take place, you cannot join a table unless it provides only one row as shown in your view example?

If I had a partitioned, massive table (partitioned over month) and a small table with a subset of months, I will not get partition elimination by joining them? (We run Teradata 12).

Best regards

Karl

TD_Raj 2 comments Joined 05/10
25 Jul 2013

feeling little more learned now. :D
Thanks for this awesome post.

teradatauser2 19 comments Joined 04/12
02 Aug 2013

Hi Diether,
I found a reference to this link in a post : make use of ppi partition elimination without a hardcoded date.

However, i am not clear as to how the approach mentioned here solves this.

 

I have a similar post : http://forums.teradata.com/forum/database/query-tuningppi-not-being-used-in-select and i am seeking a solution.

 

Could you please help me understand this ?

 

Thanks

dnoeth 70 comments Joined 11/04
02 Aug 2013

This approach only works for single-row tables holding those parameters.
 
Dieter

Dieter

05 Aug 2013

Hi to all, can anyone let me know that is there any website or place from where I can get freelancing work of teradata and I know this is not a right place to post.
Any help or clue will be appreciated.
 

dnoeth 70 comments Joined 11/04
07 Aug 2013

If you know it's not the right place, why don't you post in the right place instead?
 
Dieter

Dieter

08 Aug 2013

Hi Dnoeth,
                 Please give me your email id. I need some guidance or expert advice.

suhailmemon84 52 comments Joined 09/10
22 Oct 2013

Hi Dieter,
I have a question regarding query banding in macros. I have a project specific requirement where I need to set a query band dynamically based on input parameters. I'm trying to achieve this via macros.
But the issue is that if I'm passing parameter to a macro that has only the "set query band" statement, I can only do it if the query is "for transaction" and not "for session". Why so?
Eg, the below code works fine(where I hardcode the query band value inside the macro):
create macro sysdba.mac1( QBIN varchar(60))
as
(
SET QUERY_BAND = 'org=Finance;report=Fin1234;' FOR session;

)

 exec sysdba.mac1('message');
 select getqueryband(); /*runs successfully and gives the output: "=S> org=Finance;report=Fin1234;"  */
But this statement fails:
create macro sysdba.mac1( QBIN varchar(60))
as
(
SET QUERY_BAND = :QBIN FOR session;

 )
with error: "A syntax error was found in the QUERY_BAND"
whereas this statement succeeds:
create macro sysdba.mac1( QBIN varchar(60))
as
(
SET QUERY_BAND = :QBIN FOR TRANSACTION;

 )

Regards,
Suhail

KVB 2 comments Joined 09/12
07 Feb 2014

It worked!Thanks Dieter.

You must sign in to leave a comment.