Stored Procedures, Dynamic SQL, Funky Range Scans, and GETINTRANGE

Database
N/A

Stored Procedures, Dynamic SQL, Funky Range Scans, and GETINTRANGE

Hello,

I have a current situation with a stored procedure and could use some guidance / advice. Currently we have a stored procedure that generates dynamic SQL which is based on customer inputs. Specifically, I'm having problems with a column being referenced in the SP that is undergoing funky range scans. I will refer to this column as ABC. Here are the 3 rangescan scenarios for ABC:

1) User enters the following: ABC = 100-105

The SP will build the where clause, looking like: WHERE ABC IN (100, 101, 102, 103, 104, 105). This is fine and dandy until the user puts in a range like 1-100000 (I believe you're limited to 65,535 values in an IN statement). We'd like to change it so it dynamically builds the statement as: WHERE ABC BETWEEN 100 and 105.

2) User enters the following: ABC = 100, 101, (105-107), 110

The SP will build the where clause looking like: WHERE ABC IN (100, 101, 105, 106, 107, 110). We'd eventually like to change it to be a mix of INs, ORs, and Betweens.

3 - The problem scenario) User enters the following: ABC = (100, 110, 2)
That new parameter (2) is refering to a column jump when processing. So for example, this would look something like: WHERE ABC IN (100, 102, 104, 106, 108, 110). If the third parameter was a (5), it would be: WHERE ABC IN (100, 105, 110).

So the problem lies in tieing up all of this logic into a single SP. Our first solution was to have JAVA do the work for scenario 3. Some java developers told me that they use a function called GETINTRANGE, however the timing it takes to process the numbers using this function isn't allowing us to meet our SLAs.

That being said, I have two questions:

1) Is there anything similar to the GETINTRANGE function in Teradata? I suspect if I can do that type of work at the database side, it would be much faster than having java do it. It would also allow me to easily build the dynamic logic to satisfy scenarios 1 and 2.

2) Is there another way I can approach this? I'm worried that there isn't an equivilant to GETINRANGE for TD database, so I will need to exercise new options and approaches to handle this problem.

Any helps is very much appreciated; thank you for taking the time to read my topic.

-TCGE
5 REPLIES
N/A

Re: Stored Procedures, Dynamic SQL, Funky Range Scans, and GETINTRANGE

"That new parameter (2) is refering to a column jump".

I meant to say "row" jump.
N/A

Re: Stored Procedures, Dynamic SQL, Funky Range Scans, and GETINTRANGE

#1 and #2 could be easily covered when the user changes the input format.

100, 101, 105 TO 107, 110
can be used as-is in an IN-condition, the range using TO is a hardly known (and hardly used) extension in Teradata SQL.

#3 is tough, first you have to determine if the last number is just a number or that "jump" value.
And then create the actual range.
Of course you could that in a loop wihtin an SP, but covering all three scenarios (and faulty user input) would result in lots of SUBSTRING/POSITION/IF/CASE.

I would consider a UDF doing all the fancy stuff and finally returning a valid string.

Dieter
N/A

Re: Stored Procedures, Dynamic SQL, Funky Range Scans, and GETINTRANGE

Dieter,

That's pretty cool about the TO command, that will make things easier. I've already played a little with the Substring/position/if/case stuff, and it's getting rough to say the least; I'll look to see if a UDF can do what we're looking for in #3.

Thanks for the response!

Re: Stored Procedures, Dynamic SQL, Funky Range Scans, and GETINTRANGE

Hi,

I want to write a query which gives me count of all tables in a database. I tried to make a query but it only generetes sql for each table. Below is the query;

sel 'sel' "'"c || trim(Tablename) || "'"c ', Count(*) from ' || trim(databasename) ||  '.' || trim(tablename) || ';' from dbc.tables where databasename='p0_edw';

result for this query is multiple sql statements given below;

sel'PARTY_ADDRESS', Count(*) from P0_EDW.PARTY_ADDRESS;

sel'ACCESS_METHOD_SUBSCRIPTION', Count(*) from P0_EDW.ACCESS_METHOD_SUBSCRIPTION;

sel'SUBSCRIPTION_STATUS', Count(*) from P0_EDW.SUBSCRIPTION_STATUS;

sel'LOCATOR_DEF706', Count(*) from P0_EDW.LOCATOR_DEF706;

sel'SUBSCRIPTION', Count(*) from P0_EDW.SUBSCRIPTION;

sel'NETWORK_ACTIVITY_OSP', Count(*) from P0_EDW.NETWORK_ACTIVITY_OSP;

Though its useful as it generates the count sql for each table in the given database but i would like the result of these sql in a single table which i guess is one step next to this.Can anyone help out here..Thanks in advance

Re: Stored Procedures, Dynamic SQL, Funky Range Scans, and GETINTRANGE

@ilf:

I am new to Teradata, so my syntax might be off, but this conceptually should do what you are asking:

create table test_count(

table_name varchar(30),

table_size int

)

sel 'insert into test_count(table_name, table_size) sel ' "'"c || trim(Tablename) || "'"c ', Count(*) from ' || trim(databasename) ||  '.' || trim(tablename) || ';' from dbc.tables where databasename='itm_base';