Hi,

that's a hint that your line

my $sth = $dbh->prepare($sql);
[download]

results in an error assigning undef to $sth.

Without handle attribute RaiseError you have to check EVERY DBI call for errors. So in this case it's probably a simple SQL error.

Change the prepare line to

my $sth = $dbh->prepare($sql) or die "Prepare Error: $DBI::errstr";
[download]

Regards
McA

I know, the issue is how I use this kind on SQl statement with out causing any errors, if I use a simple
"select * from my_table"
it works, but because of the nature of the SQL statement DBI does not like it, and it gets worse by trying to add place holders to it.

Which database are you using ? Your code works fine for me on MS SQL Server Express.
poj

Sorry, I misuderstood your question.

In such cases I try these SQL statements in a database dependant client to find out what is wrong. Any hints from that?

McA

No problem at all for DBI. I ran your SQL without problem in the postgres dialect:

#!/bin/env perl

use strict;
use warnings;
use Data::Dumper;
use DBI;

#  table my_table;
#  first | middle | last 
# -------+--------+------
#  joe   | john   | sean
#  joe   | john   | sean
#  joe   | john   | sean
#  pet   | joe    | sean
#  pet   | zoe    | joe
#  ken   | zoe    | joe
# (6 rows)

# (picking up dsn from environment:)
my $dbh = DBI->connect or die "oops - no db connection\n";
$dbh->{RaiseError} = 1;

my $sql = "
with data_count as (
    select
         sum(case when FIRST  = 'joe' then 1 else 0 end) as a_count
       , sum(case when MIDDLE = 'joe' then 1 else 0 end) as b_count
       , sum(case when LAST   = 'joe' then 1 else 0 end) as c_count
    from my_table
    where FIRST like 'joe%'
      or MIDDLE like 'joe%' or LAST like 'joe%'
)  
          select 'Search by: ' || 'joe'
union all select 'Found ' || cast(a_count as integer) || ' ' || 'joe' 
+|| ' for First'  from data_count
union all select 'Found ' || cast(b_count as integer) || ' ' || 'joe' 
+|| ' for Middle' from data_count
union all select 'Found ' || cast(c_count as integer) || ' ' || 'joe' 
+|| ' for Last'   from data_count
" ;

#         ?column?        
# ------------------------
#  Search by: joe
#  Found 3 joe for First
#  Found 1 joe for Middle
#  Found 2 joe for Last
# (4 rows)

my $sth = $dbh->prepare($sql);
   $sth->execute() or die "SQL Error: $DBI::errstr\n";

my $data = $sth->fetchall_arrayref({});

warn Dumper $data;
[download]

which results in the expected output (proving that neither DBI nor the general SQL is at fault):

$VAR1 = [
          {
            '?column?' => 'Search by: joe'
          },
          {
            '?column?' => 'Found 3 joe for First'
          },
          {
            '?column?' => 'Found 1 joe for Middle'
          },
          {
            '?column?' => 'Found 2 joe for Last'
          }
        ];
[download]

(A further variant with placeholders also gave no problem (in postgres). )

What database are you using? What is the error you get? Is there anything in the database logfile?

If $sth is undefined, it is because $dbh->prepare($sql) failed and returned undef.

If $dbh->prepare($sql) failed, it is because the database returned an error.

If the database returned an error, it is probably because the query in $sql is not valid in its dialect of SQL, but it could be something else. Checking for a DBI error on the prepare is the only way to find out:

my $sth = $dbh->prepare($sql) or die "SQL Error: $DBI::errstr\n";
   $sth->execute() or die "SQL Error: $DBI::errstr\n";
[download]