| Subject: | Table aliases in Oracle must not use "AS" |
In oracle, this is how you create a table alias:
SELECT u.username AS login FROM users u WHERE u.id=1234
but using SQL::Yapp ...
sub uppercase { uc($_[0]) }
use SQL::Yapp
dbh=>sub{dbConnect()},
dialect => 'oracle',
xlat_table => \&uppercase,
xlat_column => \&uppercase
;
my $uid=1234;
my $login=sqlFetch{ SELECT u.username AS login FROM users AS u WHERE u.id=$uid };
But this results in:
DBD::Oracle::db prepare failed: ORA-00933: SQL command not properly ended (DBD ERROR: error possibly near <*> indicator at char 41 in '
SELECT "U"."USERNAME" AS "LOGIN" FROM "USERS" <*>AS "U" WHERE "U"."ID" = 1234
'
On Yapp.pm line 5079:
str_append_join ($str, sep => ' AS ', never_empty => 1);
If I change the sep to ' ' then Oracle works fine. Since the SQL spec says that "AS" is optional, you could simply omit the "AS" in this case, or make it dependent on write_dialect.
See: http://savage.net.au/SQL/sql-99.bnf.html#table primary