June 26th, 2009


Perl DBI help

Argh, I've got an SQL query which works fine if I run it from Access, and which I think has no errors, but if I try run it from Perl I'm getting an error from the database. Can anyone tell me what obvious thing I'm doing wrong?

Collapse )

Any clues? (Apologies for some long lines, I do intended to split it up a bit more eventually, but at the moment I think it slightly aids readability)

Ah, it seems that in fact the oracle database does *not* support left outer joins, and that the reason it works in Access is because Access translates the query into a form that this version of oracle does support. Many thanks to Paul for popping in to explain (I could have just gone and asked him obviously, but he's been rather busy with exam marking recently). What a nuisance, since this is obviously a much nicer way to do the query than faffing about with a "where not in (subquery)" or similar. (We suspect it is supported in the new version of Oracle Paul's working on migrating to, but sadly I think my code is needed before then).

Double correction: it does support them, but with weird syntax. The following code works:

Collapse )