On Mon, 20 May 2002 10:56:24 -0800 Brian Avis <[EMAIL PROTECTED]> wrote:
> So is there any reason why I can't run this through DBI?
>
> SELECT ecls_code || '-' || ecls_long_desc AS class,
> id_number,
> last_name || ', ' || first_name AS name
> FROM ap_employee_profile
> WHERE empl_status = 'A'
> AND class = $quotedCLASS
> ORDER BY class, id_number, name
>
> Other than the fact that it complains about class being an unkown
> column? Or is this more sqlplus stuff that is catching me off guard?
Aliases aren't supported in the WHERE clause in Oracle 7. Also, you should
use a placeholder instead of pasting $quotedCLASS directly into the SQL.
Unless it is essential for telling the classes apart, you don't need
ecls_long_desc in the WHERE clause.
I often find it easier for other processing if I take each column
separately and paste them together in the Perl script instead of in the
SQL. This isn't SQL*Plus, so you can do a lot of other processing before
building your output lines.
# Untested example
$dbh -> {RaiseError} = 1; # ALWAYS check for errors
my $sth = $dbh -> prepare( <<END_SQL );
SELECT ecls_code, ecls_long_desc,
id_number,
last_name, first_name
FROM ap_employee_profile
WHERE empl_status = ?
AND ecls_code = ? AND ecls_long_desc = ?
END_SQL
my ( $ecls_code, $ecls_desc, $id_num, $last_name, $first_name );
$sth -> execute( "A", $cls_code, $cls_desc );
$sth -> bind_columns(
\( $ecls_code, $ecls_desc, $id_num, $last_name, $first_name ) );
my ( $ecls, $name );
my $fmt = "%-30s %5s %s\n";
printf $fmt, "Class", "ID", "Name";
while ( $sth -> fetch ) {
$ecls = "$ecls_code-$ecls_desc";
$name = join ", " grep { $_ } $last_name, $first_name;
printf $fmt, $ecls, $id_num, $name;
}
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
