Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Problem with case in select statement

by SamueD2 (Novice)
on Mar 21, 2003 at 15:32 UTC ( [id://244895] : perlquestion . print w/replies, xml ) Need Help??

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

The Select works fine in Sybase but in the code below it gives me errors. When I striped out the Case When from the select statement it worked fine. Does anyone know how to write it with the case when. Thanks
use strict; use warnings; use DBI; #use Win32::ODBC; use DBD::ODBC; # Global Variables my @statement; # Array of sql statements that will be used for Sql my @data; # Array of table rows read from Sybase my @message; # Array of error messages used for debugging Sql # SQL Connection my $dbh = DBI->connect( "dbi:ODBC:RETDEV_SQL", "User", "Pass", {RaiseError => 1, PrintError => 1, AutoCommit => 1} ) or die "Unable to connect: ";#. $DBI::errstr . "\n"; # Sybase Connection my $dbh2 = DBI->connect( "dbi:ODBC:BPSA", "User", "Pass", {RaiseError => 1, PrintError => 1, AutoCommit => 1} ) or die "Unable to connect: ";#. $DBI::errstr . "\n"; #Create the sql statement for Sybase and execute it $sth = $dbh2->prepare("SELECT (dbo.tacat_trnfr.branch_cd + dbo.tacat_t +rnfr.acct_cd) As AccountNo,case when (dbo.tacat_trnfr.dlvr_nbr= '0044 +') then 'RCV_NBR' when (dbo.tacat_trnfr.rcv_nbr = '0044')then 'DLVR _ +NBR' end As ContraFirmNo, case when (dbo.tacat_trnfr.trnfr_type_cd = 'FUL' or dbo.tacat_trnfr. +trnfr_type_cd = 'PTD')then 'ACT' when (dbo.tacat_trnfr.trnfr_type_cd += ' PTR') then 'PCT' end as TransferType, case when (dbo.tacat_trnfr.dlvr_nbr= '0044') then 'DLR' when (dbo.taca +t_trnfr.rcv_nbr = '0044')then 'TRI'end As TransferFlag, case when (dbo.tacat_trnfr.stts_cd = '140') then 'S' when (dbo.tacat_t +rnfr.stts_cd = '300'or dbo.tacat_trnfr.stts_cd = '310') then 'C' when + (dbo.tacat_trnfr.stts_cd = '400' or dbo.tacat_trnfr.stts_cd = '500' +or dbo.tacat_trnfr.stts_cd = '600') then 'H'end as RejectType, dbo.tacat_trnfr.cmnt_txt as Comment FROM dbo.tacat_trnfr WHERE dbo.tacat_trnfr.stts_cd in ('140','300','310','400','500','600') +"); $sth->execute; my $insh=$dbh->prepare("INSERT INTO TestClearTransferData(AccountNo,Co +ntraFirmNo,TransferType,RejectType,Comment) VALUES ( ?,?,?,?,?)"); eval { while((@data) = $sth->fetchrow_array) { $insh->execute(@data); } }; if($@) { print STDERR "The insert died: $@"; } #Close the sql DB Connection $dbh->disconnect; #Close the Sybase DB connection $sth->finish; $dbh2->disconnect;

Replies are listed 'Best First'.
Re: Problem with case in select statement
by zby (Vicar) on Mar 21, 2003 at 15:42 UTC
    There is no Case When in standard SQL (ANSI SQL 92) so your question is really something for a Sybase forum. It might be that in the Sybase SQL there is no such construct as well.

    You could make the query a bit indented by the way.

      The Select statement works in Sybase
        The Select statement works in Sybase

        Perhaps, but you're going through ODBC to get there,. I'll bet the error that's getting coughed up (the one you haven't shown) is from ODBC.

        Show us the error message.

        It might be possible that the select statement is just too long for the library.
Re: Problem with case in select statement
by iburrell (Chaplain) on Mar 21, 2003 at 17:23 UTC
    What database does it give errors with? That is important information to diagnose the failure. I know that SQL Server supports CASE statements (not surprising since it is based on Sybase). PostgreSQL also supports CASE. MySQL doesn't. I don't know about Oracle.

    BTW, it is helpful to us and you if you format the SQL statement to make it more readable. I don't really like using indentation for SQL but always put each clause on a separate line. I tend to use here-docs to assign to a $sql variable and then prepare with the variable.

    my $sql=<<END_HERE; SELECT foo FROM bar WHERE baz = ? END_HERE my $sth = $dbh->prepare($sql);
Re: Problem with case in select statement
by dws (Chancellor) on Mar 31, 2003 at 18:04 UTC
    The Select works fine in Sybase but in the code below it gives me errors.

    My info on ODBC may be stale -- it's been a while since I've used it -- but doesn't ODBC define a subset of SQL, and then map between that subset and vendor-specific SQL? That is, arbitrary non-standard constructs like CASE aren't necessarily supported by ODBC.

    I suggest getting your hands on some good ODBC documentation.

Re: Problem with case in select statement
by mpeppler (Vicar) on Mar 31, 2003 at 17:58 UTC
    As others have mentioned - it would help if you could show us the actual error message you are getting.

    I looked at your SELECT statement, and noticed that you are missing spaces between some symbols in your query - that may be what is causing the problem.

    Michael