Eleanor Blair (lnr) wrote,
Eleanor Blair
lnr

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?

The relevant code snippet is:

my $sth = $dbh->prepare("SELECT committed.engid,
                                committed.crsid,
                                committed.uid_num,
                                committed.gid_num,
                                committed.name,
                                committed.college,
                                committed.path
                         FROM c_yj_yp_live_committed committed LEFT OUTER JOIN c_yj_yp_live_working working ON committed.engid = working.engid
                         WHERE working.engid is null");

And this gives the following error:

DBD::Oracle::db prepare failed: ORA-00933: SQL command not properly ended (DBD ERROR: error possibly near <*> indicator at char 384 in 'SELECT committed.engid,
                                committed.crsid,
                                committed.uid_num,
                                committed.gid_num,
                                committed.name,
                                committed.college,
                                committed.path
                         FROM c_yj_yp_live_committed committed <*>LEFT OUTER JOIN c_yj_yp_live_working working ON committed.engid = working.engid
                         WHERE working.engid is null') [for Statement "SELECT committed.engid,
                                committed.crsid,
                                committed.uid_num,
                                committed.gid_num,
                                committed.name,
                                committed.college,
                                committed.path
                         FROM c_yj_yp_live_committed committed LEFT OUTER JOIN c_yj_yp_live_working working ON committed.engid = working.engid
                         WHERE working.engid is null"] at ./yj_correlate.pl line 33.

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:

SELECT committed.engid,
       committed.crsid,
       committed.uid_num,
       committed.gid_num,
       committed.name,
       committed.college,
       committed.path
FROM c_yj_yp_live_committed committed, c_yj_yp_live_working working
WHERE committed.engid = working.engid (+)
      and working.engid is null
Tags: perl, sql, work
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 15 comments