If there are (?), then what you need to do is parse the thing from left to right, collecting parts as you go, instead of trying to parse it all at once. A recursive algorithm would be good, or you could just go use existing parser-creators, like Parse::RecDescent or Parse::Yapp.

C.

I am going to executing this through DBI. I needed the column aliases to write out to a flat file for use by a third party application. This application is very picky about column headings forcing us to use aliases. I didn't know about  $sth->{NAME_lc}, I will look into that now.

Any thoughts of an efficient method of obtaining these results?

For the first pass, I would not recommend that you worry about an efficient method of parsing these results. A slow method that works is surely better than a fast method that doesn't, yes?

Right off the bat, it looks like your SQL is ugly enough that you need a proper parser. I would recommend you look at Parse::RecDescent. If that turns out to be too slow, turning to Parse::Yapp or something similar might help.

However, then I got curious and decided to write a "rough draft" of the problem for you. You'll need to clean up the output, but this should pretty much print what you want.

#!/usr/bin/perl 
use strict;
use warnings;

my $sql = <<END_SQL;
select the_date as "date",
round(months_between(first_date,second_date),0) months_old
,product,extract(year from the_date) year
,case
  when a=b then 'c'
    else 'd'
      end tough_one
      from XXX
END_SQL

my $lparen  = qr/\(/;
my $rparen  = qr/\)/;
my $keyword = qr/(?i:select|from|as)/; # this is all this problem need
+s
my $comma   = qr/,/;
my $text    = qr/(?:\w+|'\w+'|"\w+")/;
my $op      = qr/(?:=>|<=|[=+\-*\/])/;

sub lexer {
  my $sql = shift;
  return sub {
    LEXER: {
      return ['KEYWORD', $1] if $sql =~ /\G ($keyword) /gcx;
      return ['COMMA',   ''] if $sql =~ /\G ($comma)   /gcx;
      return ['OP',      $1] if $sql =~ /\G ($op)      /gcx;
      return ['PAREN',    1] if $sql =~ /\G $lparen    /gcx;
      return ['PAREN',   -1] if $sql =~ /\G $rparen    /gcx;
      return ['TEXT',    $1] if $sql =~ /\G ($text)    /gcx;
      redo LEXER             if $sql =~ /\G \s+        /gcx;
    }
  };
}

my $lexer = lexer($sql);
my @tokens;
my $nested = 0;
while (my $token = $lexer->()) {
  $nested += $token->[1] if 'PAREN' eq $token->[0];
  next if $nested or $token->[1] eq 'PAREN';
  last if 'KEYWORD' eq $token->[0] && 'FROM' eq uc $token->[1];
  push @tokens => $token;
}

foreach my $i (0 .. $#tokens) {
  my $token = $tokens[$i];
  next unless 'TEXT' eq $token->[0];
  print $token->[1], $/ if $i == $#tokens;
  print $token->[1], $/ if 'COMMA' eq $tokens[$i + 1][0];
}
__END__
# prints out:

"date"
months_old
product
year
tough_one
[download]

I'm tired and I need to hit the sack, so I can't explain this too much. Let's just say that I turned the SQL into a series of easy to parse tokens. Then I discarded all tokens that were inside of parentheses and after the FROM clause. Then I assumed that any TEXT token at the end or before a comma is what you were looking for. This works for the sample SQL you provided, but it's very fragile. I would write lots of tests to explore edge cases.

This code is not perfect, but it's a start.

Note that SQL::Statement cannot be used here because SQL::Parser does not recognize CASE statements.