Perl DBI help

[lnr]

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

Comments

[simont]

My SQL is pretty basic, but my first instinct would be to try changing the identifier "committed" on the grounds that something in the big stack of confusing abstraction layers might be treating a keywordish word like that specially. Rename it "frogwobbler" and see if that does the same thing :-)

[simont]

Alternatively, I notice that the exact number of characters from the start of the select statement to the position of the <*> appears to be the suspiciously round number 384. Insanely small arbitrary length limit somewhere?

[lnr]

Thanks for the hints, both good ideas, but Paul has popped in and explained, I'll update.

[simont]

Ah! I hadn't realised you were talking to a different database backend in the two cases. I naturally assumed you were talking to the same db via different user interfaces, so I concentrated on things Perl might be doing to change the situation. If I'd spotted that the "Oracle" in the error message didn't match the "Access" in your initial description, I'd certainly have suggested that differences between the databases would be the most obvious culprit.

I suppose I'm just as glad neither of my suggestions was right; either would have been more hideous than the truth :-)

[lnr]

It is the same database, just using Perl as a frontend in one case and Access as a frontend in the other. Access just knows what syntax to use for that version of Oracle, when neither I nor Perl have a clue :)

[simont]

Ah, in that case I've misunderstood again. I was under the impression Access was (only) a database engine in its own right, and hadn't realised it could also act as a layer on top of other database backends. Learned something new today!

[arnhem.livejournal.com]

Surely it's that oracle supports left outer joins, but in earlier versions uses a bizarre and deranged syntax for them?

eg this description (http://www.dba-oracle.com/tips_oracle_left_outer_join.htm)

[lnr]

Paul's sent me the correct syntax, which I wasn't aware of, but yes thanks :)

Of course the syntax is pretty deranged, but it is nice and short in comparison :)

[keirf.livejournal.com]

I can't see the obvious flaw in the SQL (of course, it's SQL...).

So you're doing a left outer join on the committed table and the working table, where the engids match, and selecting based on the the working engid being null, but you're only selecting committed table entries. This seems rather inefficient. If you're joining on matching IDs, can't you just do SELECT where committed.id IS NULL?

[lnr]

I have two nominally identical tables (containing old and newer versions of the same data), and I want all the entries from the committed table where there *isn't* a matching entry in the working table. If you can tell me a more efficient way to do this I'd be very happy :)

The left outer join gives you a line for every line in the committed table, but where there's no matching line in the working table you get nulls in the half of the data that came from the working table. engid is in fact the key for each table.

[keirf.livejournal.com]

Oh yes, my mistake.

How about

SELECT committed.engid,
committed.crsid,
committed.uid_num,
committed.gid_num,
committed.name,
committed.college,
committed.path
FROM c_yj_yp_live_committed committed
WHERE NOT EXISTS
( SELECT * FROM c_yj_yp_live_working working
WHERE working.engid = committed.engid )

Mind you that's not great either, and I haven't got anything set up to test it.

Scarily, I will probably have to do some database programming for the first time in five years next week.

[lnr]

That's more or less the syntax I've used in the past (though you can just select engid rather than * in the second select) but I haven't found it any faster, if anything slower, than the outer join method. Which I only learned recently anyway, which is why I was rather sad when it didn't work :)

[mobbsy.livejournal.com]

Wow, you're still using Oracle 8i (or earlier)? ANSI join syntax has been supported since 9i, which was released in 2001.

[lnr]

I thought it was 9 we were still running, but I could be wrong. This is why migrating to 11 is Paul's next big project. And in fact I've got 11 installed on a machine ready for Paul to start migrating and testing. Although we've just had to remove several options because MISD tell us we're not licensed for them. Oops.

MS Access SQL differs from Oracle SQL

[(Anonymous)]

If you want to check your syntax in MS Access then set up a pass through query in Access. That way Access will send your SQL string directly to Oracle and you should get the same ORA-00933 error message. This will confirm that MS Access SQL is being translated into Oracle SQL when it is being passed via the Oracle ODBC driver. When you do the same thing in perl the DBI module does not to the equivalent translation. I have not looked at your SQL that carefully but I suspect the join syntax does not comply with what Oracle expects.