Hi,
Stumbled over some small gotchas regarding SQL syntax and Oracle 9
today. Here's the error message:
ORA-00933: SQL command not properly ended (DBD ERROR: error possibly
near <*> indicator at char 26 in 'SELECT st.id FROM ticket <*>as st
WHERE st.ticket_state_id IN ( 7, 8 ) '), SQL: 'SELECT st.id FROM
ticket as st WHERE st.ticket_state_id IN ( 7, 8 ) '
The problem is the "From column as foo" construct. The "as" in there
is optional in PostgreSQL and MySQL (according to the web
documentation), but not allowed in Oracle. So I removed them:
Index: bin/UnlockTickets.pl
===================================================================
--- bin/UnlockTickets.pl (revision 75)
+++ bin/UnlockTickets.pl (revision 76)
@@ -84,7 +84,7 @@
print " Unlock all tickets:\n";
my @Tickets = ();
my $SQL = "SELECT st.tn, st.ticket_answered, st.id, st.user_id FROM " .
- " ticket as st, queue as sq " .
+ " ticket st, queue sq " .
" WHERE " .
" st.queue_id = sq.id " .
" AND " .
@@ -118,7 +118,7 @@
my $SQL = "SELECT st.tn, st.ticket_answered, st.id, st.timeout, ".
" sq.unlock_timeout, user_id ".
" FROM " .
- " ticket as st, queue as sq " .
+ " ticket st, queue sq " .
" WHERE " .
" st.queue_id = sq.id " .
" AND " .
Index: bin/PendingJobs.pl
===================================================================
--- bin/PendingJobs.pl (revision 75)
+++ bin/PendingJobs.pl (revision 77)
@@ -78,7 +78,7 @@
if (@PendingAutoStateIDs) {
my @TicketIDs = ();
my $SQL = "SELECT st.id FROM " .
- " ticket as st " .
+ " ticket st " .
" WHERE " .
" st.ticket_state_id IN ( ${\(join ', ', @PendingAutoStateIDs)} ) ";
$CommonObject{DBObject}->Prepare(SQL => $SQL);
@@ -141,7 +141,7 @@
if (@PendingReminderStateIDs) {
my @TicketIDs = ();
my $SQL = "SELECT st.tn, st.id, st.user_id FROM " .
- " ticket as st, ticket_state tsd " .
+ " ticket st, ticket_state tsd " .
" WHERE " .
" st.ticket_state_id = tsd.id " .
" AND " .
Regards,
--
Kristoffer.
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
