David Korb's SQL weblog

SAS date in a pass-through query

2010-08-03T13:39:00.000-07:00

Dates have to be passed into Teradata as quoted strings and you can't just concatenate single quotes to the macro variable using the native RDBMS syntax. What you need is the %BQUOTE function to mask a quote as you assign it to a macro variable. Here is an example where you want the last 5 days of data.

data _null_;
call symput( 'ystrdt', put(date() - 5, yymmdd10.) );
run;

%let q=%BQUOTE(');

PROC SQL;
CONNECT TO TERADATA (connection information);
EXEC(COMMIT) BY TERADATA;

EXEC
(
INSERT INTO db_name.test_table
SELECT ...some_list...
FROM db_name.some_table
WHERE some_date > &q&ystrdt&q
) BY TERADATA;
EXEC(COMMIT) BY TERADATA;

DISCONNECT FROM TERADATA;
QUIT;

Variables in Teradata

2010-02-16T09:03:00.000-08:00

As of this writing, I am managing batch scripts that hit Teradata servers. Unlike MS SQL Server, Teradata does not allow functionality such as branching logic or variables. Unfortunately, this means the programmer has to bend over backwards to get work done and the code becomes less readable.

For this example, what would be a simple date variable is replaced with a volatile table containing a date. This allows dynamic filtering so that the programmer does not have to edit the script on a daily basis.

-- Create the temp table.
CREATE VOLATILE TABLE process_date (
batch_date DATE NOT NULL FORMAT 'yyyy-mm-dd'
)
UNIQUE PRIMARY INDEX (batch_date)
ON
COMMIT
PRESERVE ROWS;

-- Insert a value.
INSERT INTO process_date( batch_date )
SELECT DATE;

-- Query the previous 90 days of data using the date filter.
SELECT tbl.field_name
FROM database_name.table_name AS tbl
WHERE EXISTS (
SELECT *
FROM process_date AS PDT
WHERE tbl.date_name
BETWEEN PDT.batch_date - 90 AND PDT.batch_date - 1
);

Create a tally table using a CTE

2009-08-17T10:35:00.001-07:00

create table dbo.t_tally (
i int not null
,constraint pk_t_tally primary key clustered (i)
)

;with digits (i) as(
select 1 as i union all select 2 as i union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 0)
,sequence(i) as (
select
d1.i
+ (10*d2.i)
+ (100*d3.i)
+ (1000*d4.i)
+ (10000*d5.i)
from digits as d1
,digits as d2
,digits as d3
,digits as d4
,digits as d5
)
insert into dbo.t_tally
select i
from sequence
where i < 65537
order by i

SQL Server 2005 string search utility

2009-08-13T19:43:00.000-07:00

A utility for finding strings in compiled objects on an instance of SQL Server 2005. Compiled objects being: user-defined functions, stored procedures, triggers, views, check constraints.