tag:blogger.com,1999:blog-43975714917745196072024-03-08T14:46:33.425-08:00David Korb's SQL weblogDavidhttp://www.blogger.com/profile/05948936335943076933noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-4397571491774519607.post-13968582603006706392010-08-03T13:39:00.000-07:002010-08-03T13:54:20.917-07:00SAS date in a pass-through queryDates 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.<br /><br />data _null_;<br /> call symput( 'ystrdt', put(date() - 5, yymmdd10.) );<br />run;<br /><br />%let q=%BQUOTE(');<br /><br />PROC SQL;<br /> CONNECT TO TERADATA (connection information);<br /> EXEC(COMMIT) BY TERADATA;<br /><br /> EXEC<br /> (<br /> INSERT INTO db_name.test_table<br /> SELECT ...some_list...<br /> FROM db_name.some_table<br /> WHERE some_date > &q&ystrdt&q<br /> ) BY TERADATA;<br /> EXEC(COMMIT) BY TERADATA;<br /><br /> DISCONNECT FROM TERADATA;<br />QUIT;Davidhttp://www.blogger.com/profile/05948936335943076933noreply@blogger.com0tag:blogger.com,1999:blog-4397571491774519607.post-13878709497612651912010-02-16T09:03:00.000-08:002010-02-16T09:35:29.542-08:00Variables in TeradataAs 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.<br /><br />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.<br /><br />-- Create the temp table.<br />CREATE VOLATILE TABLE process_date (<br /> batch_date DATE NOT NULL FORMAT 'yyyy-mm-dd'<br />)<br />UNIQUE PRIMARY INDEX (batch_date)<br /> ON<br />COMMIT<br />PRESERVE ROWS;<br /><br />-- Insert a value.<br />INSERT INTO process_date( batch_date )<br />SELECT DATE;<br /><br /><br />-- Query the previous 90 days of data using the date filter.<br />SELECT tbl.field_name<br />FROM database_name.table_name AS tbl<br />WHERE EXISTS (<br /> SELECT *<br /> FROM process_date AS PDT<br /> WHERE tbl.date_name<br /> BETWEEN PDT.batch_date - 90 AND PDT.batch_date - 1<br />);Davidhttp://www.blogger.com/profile/05948936335943076933noreply@blogger.com0tag:blogger.com,1999:blog-4397571491774519607.post-85551051492423165532009-08-17T10:35:00.001-07:002009-08-17T10:38:32.814-07:00Create a tally table using a CTEcreate table dbo.t_tally (<br /> i int not null<br /> ,constraint pk_t_tally primary key clustered (i)<br />)<br /><br />;with digits (i) as(<br /> select 1 as i union all select 2 as i union all select 3 union all<br /> select 4 union all select 5 union all select 6 union all select 7 union all<br /> select 8 union all select 9 union all select 0)<br /> ,sequence(i) as (<br />select<br /> d1.i<br /> + (10*d2.i)<br /> + (100*d3.i)<br /> + (1000*d4.i)<br /> + (10000*d5.i)<br />from digits as d1<br /> ,digits as d2<br /> ,digits as d3<br /> ,digits as d4<br /> ,digits as d5<br />)<br />insert into dbo.t_tally<br />select i<br />from sequence<br />where i < 65537<br />order by iDavidhttp://www.blogger.com/profile/05948936335943076933noreply@blogger.com0tag:blogger.com,1999:blog-4397571491774519607.post-6927633165395550832009-08-13T19:43:00.000-07:002009-08-13T22:22:36.149-07:00SQL Server 2005 string search utilityA 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.Davidhttp://www.blogger.com/profile/05948936335943076933noreply@blogger.com1