documents9900 has asked for the wisdom of the Perl Monks concerning the following question:

Hi Team, My Program is below 
use DBI;
use DBD::Oracle;
use diagnostics;
use DBIx::Report::Excel;

my $ReportDate ='ABC';

my $SheetName='Test_'.$ReportDate.'.xls';
my $report = DBIx::Report::Excel->new($SheetName);

$sql2='select  T.Name from TABLENAME T where T.Type = \'MASTER\' and e
+xists (select 1 from TABLENAME T2 where T2.Name = T.Name and T2.Name 
+like \'Data%\')';
print $sql2;

$report->dbh(DBI->connect($odbc_dsn,$username,$password)) or die "Coul
+d not connect to DB with configuration provided";

print "Retrieving from Database is Starting\n";
$report->sql('
/* ---
title:  Columns
--- */
'.$sql2);
$report->write();
$report->close();

[download]
-------------- Note : I have removed the information of Database details and other relavant details.

Above program works fine but the problem is I need to use not exists to get my desired data. If I change the code and make it not exists then this error comes

Bad table or column name: 'exists ^0^' has chars not alphanumeric or underscore!

I checked on google and several posts on couple of websites and found out that I need to remove non alphanumeric data from string and I added this in my code 

$sql2 =~ s/[^a-zA-Z0-9 _,-='()]//g

[download]
But same error was still coming. Can anyone please suggest what could be the real issue. Also, I have removed several lines of code, so please ignore if some information is missing (like missing variables, declaration, connection info)

Please help me!!!

Replies are listed 'Best First'.
Re: Error "'exists ^0^' has chars not alphanumeric or underscore in perl"
by NetWallah (Canon) on Jan 25, 2013 at 05:14 UTC
    The error seems to be emanating from SQL::Parser - which looks like it is getting confused by the complex SQL syntax.

    Try re-designing your SQL statement using UNION or INTERSECTION - maybe that will get past the Parser.

    Sorry - I'm not a SQL guru, and dont know the syntax well enough to actually provide the alternative.

    BTW - your attempt to clear the SQL of funny characters has a bug - it deletes the "%" after "Data%" - leading to potentially undesirable results.

    The SQL string shown is clean to start with - it does not need the regex you have attempted to use.

                 Most people believe that if it ain't broke, don't fix it.
            Engineers believe that if it ain't broke, it doesn't have enough features yet.

[reply]
      I am getting the same error when trying to parse the SQL with SQL::Parser. However, it work with DBD::SQLite, for example: 
      #!/usr/bin/perl
use warnings;
use strict;

use DBI;
use DBD::SQLite;

my $sql = DBI->connect('dbi:SQLite:dbname=1.db', q(), q());

$sql->do('create table TABLENAME        (type varchar, name varchar)')
+;
$sql->do(q(insert into TABLENAME values ('MASTER',     'm1')));
$sql->do(q(insert into TABLENAME values ('MASTER',     'Data1')));
$sql->do(q(insert into TABLENAME values ('SLAVE',      's1')));

my $sth = $sql->prepare(q(select T.Name from TABLENAME T
                          where T.Type = 'MASTER'
                              and not exists (select 1 from TABLENAME 
+T2
                                              where T2.Name = T.Name
                                                  and T2.Name like 'Da
+ta%')));
$sth->execute;
my $aref = $sth->fetchall_arrayref;
print "@$_\n" for @$aref;

      [download]
      لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
[d/l]
Re: Error "'exists ^0^' has chars not alphanumeric or underscore in perl"
by parv (Parson) on Jan 25, 2013 at 14:00 UTC
[reply]
      Thanks all for the help. As of now, I have re-wrote my sql's to get the desired results however the sql is not effective (though giving the correct result set) My Data was 
      C1      C1
C2      C2 (Modified)
        C3 (New)

      [download]
      My requirement was to find only New records. So i got my result by running a small query of records present in table 2 which are not present in Table 1. However same was not working fine in perl.

      I rewrote my sql in such a way that I used minus first which gave me C2 and C3 records and then i again minus those records which are present in both. In short find out those records which are changed or new and then removed the changed records. So i got the new records.

      Now with respect to solutions provided by all of you, I got to know the Report::Excel will not be to handle the complex queries. Also, I cannot use any other thing as I have a huge program which will require more time to rewrite if I use different module. Also, I didn't get much time to read on interpolate variables as suggested, but still the point is the query works fine if it is exists clause and it doesn't if it is not exists clause. Probably I will read first and then will comment. Thanks all for your help

[reply]
[d/l]
Re: Error "'exists ^0^' has chars not alphanumeric or underscore in perl"
by roboticus (Chancellor) on Jan 25, 2013 at 11:58 UTC

    documents9900:

    choroba is correct. I managed to see the % in the SQL string and had a knee-jerk reaction.

    The problem is that you're trying to interpolate variables into SQL rather than using placeholders. Read up on placeholders in DBI and convert your code to use them. It'll simplify things for you, make your code more robust, and prevent odd SQL syntax problems.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
      you're trying to interpolate variables
      Are you sure? I do not see any variables.
      لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
Re: Error "'exists ^0^' has chars not alphanumeric or underscore in perl"
by Rahul6990 (Beadle) on Jan 25, 2013 at 04:58 UTC
    Can you show where are you using not exists
[reply]

Back to Seekers of Perl Wisdom