Archives of the TeradataForum

Message Posted: Fri, 20 Mar 2003 @ 21:18:13 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

You don't say what platform you are running on (which can be helpful).

Assuming you have access to perl (typically an every unit item on Unix systems, and freely available on windows and other platforms) you could try something like this.

#!/usr/bin/perl

$i = 1;

open (BTEQ, "| bteq") || die "Can't open destination\n$!\n";
while ($i <= 20) {
    $number = sprintf ("%02d", $i);
    print BTEQ ".logon cm3_u$number, cm3_u$number\n";
    print BTEQ "modify user cm3_u$number as default database =
cm3_u${number}base;\n";
    print BTEQ ".remark other sql goes here\n";
    print BTEQ ".logoff\n";
    $i++;
}
close (BTEQ);

The above script is a script I used to log on to 20 different users and reset there environments prior to a training class.

The output of the above script is
.logon cm3_u01, cm3_u01
modify user cm3_u01 as default database = cm3_u01base;
.remark other sql goes here
.logoff

.logon cm3_u02, cm3_u02
modify user cm3_u02 as default database = cm3_u02base;
.remark other sql goes here
.logoff

And so on up to 20. Each line is sent to bteq and submitted for processing.

I know you want to access different databases, but the principle is still valid. You might replace my print BTEQ statements with something like this:

if (somecondition) {
    $prefix = "first";
}
else {
    $prefix = "sustain";
}

$db_A = "$prefix_core";
$db_B = "$prefix_lookup";
print BTEQ "select a.c1, b.c2\n";
print BTEQ "  from $db_A.some_table a join $db_B.some_table b on\n";
print BTEQ "         b.c1 = b.c2;\n";

Or whatever query you want. To make it even more readable you could use the following print to get the query to bteq

print BTEQ <<"EOI";
select a.c1, b.c2
  from $db_A.some_table a join $db_B.some_table b on
         b.c1 = b.c2;
EOI

Both examples send something like this to bteq

select a.c1, b.c2
  from first_core.some_table a join first_lookup.some_table b on
         b.c1 = b.c2;

I hope this helps

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>