Shouldn't this be:
$yth = $dbh->prepare("select nolo \"Batch Number\",
decode(etho,
'1', 'BUILT',
'2', 'READY TO PRINT',
'3', 'PRINTING IN PROGRESS',
'4', 'PRINTED',
'5', 'CONTROL IN PROGRESS',
'6', 'CONTROL STOPPED',
'7', 'VALIDATED')\" STATUS\"
from lot_dr
where etho in ('01', '02', '03', '04', '05', '06')
order by nolo")
or die "Couldn't prepare 05:" . $dbh->errstr;
At the end of your statement you didn't close your quoted session,
either. I.e. each " should be paired with another ". In you roriginal
statement you only had 5 "s in it.
-----Original Message-----
From: deadtwoU wishUknew [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 11:32 AM
To: [EMAIL PROTECTED]
Subject: DECODE in(DBI)
Hi
Was wondering if anyone has a working example / code
of a perl DBI connection to an ORACLE db, where the select
statement for eg. contains the SQL function DECODE.
I've been working with my code for a while, and it seems,
perl DBI does not seem to like my query when combined with
SQL DECODE.
--- sample code of my attempt with the necessary parts ---
--snip snippy--
### Initialisation and preparation ###
$yth = $dbh->prepare(q{"select nolo "Batch Number",
decode(etho,
'1', 'BUILT',
'2', 'READY TO PRINT',
'3', 'PRINTING IN PROGRESS',
'4', 'PRINTED',
'5', 'CONTROL IN PROGRESS',
'6', 'CONTROL STOPPED',
'7', 'VALIDATED')" STATUS"
from lot_dr
where etho in ('01', '02', '03', '04', '05', '06')
order by nolo})
or die "Couldn't prepare 05:" . $dbh->errstr;
---- snip snippy ---
################### Retrieve the data #################
$yth->execute # Execute the query
or die "Couldn't execute statement: " .
$yth->errstr;
print "\nNumber of Batches and their Status> \n";
# Retrieve the returned rows of data
while (my @row = $yth->fetchrow_array() ) {
print "@row\n";
}
warn "Problem in fetchrow_array(): ", $yth->errstr(), "\n"
if $yth->err();
$yth->finish;
print <<EndNL;
<br>
EndNL
/\/\/\/\/\/\/\/\/\/\ THE END /\/\/\/\/\/\/\/\/\/\/\/\
Error dump below :
------------------
DBD::Oracle::db prepare failed: ORA-00900: invalid SQL
statement (DBD: error possibly near <*> indicator at char 1
in '<*>"select nolo "Batch Number", decode(etho,
'1', 'BUILT',
'2', 'READY TO PRINT',
'3', 'PRINTING IN PROGRESS',
'4', 'PRINTED',
'5', 'CONTROL IN PROGRESS',
'6', 'CONTROL STOPPED',
'7', 'VALIDATED')"
STATUS"
from lot_dr
where etho in ('01', '02', '03',
'04', '05', '06')
order by nolo') at x.pl line 30.
Couldn't prepare 05:ORA-00900: invalid SQL statement (DBD:
error possibly near <*> indicator at char 1 in '<*>"select
nolo "Batch Number", decode(etho,
'1', 'BUILT',
'2', 'READY TO PRINT',
'3', 'PRINTING IN PROGRESS',
'4', 'PRINTED',
'5', 'CONTROL IN PROGRESS',
'6', 'CONTROL STOPPED',
'7', 'VALIDATED')" STATUS"
from lot_pr
where etho in ('01', '02', '03', '04',
'05', '06')
order by nolo') at x.pl line 30.
----------------------------------
Anybody have any clue to this error,
it definitely seems that DBI is parsing the SQL statement
with poor syntax, as a result... OORA-00900 error.
Any assistance or help would be kindly appreciated :)
Gooday
Dead2U
==
Download ringtones, logos and picture messages at Ananzi Mobile Fun.
http://www.ananzi.co.za/cgi-bin/goto.pl?mobile
