Oracle SQL

[TheFaQQer]

I always work with Oracle Applications...and I structure my code to reflect theirs. And they NEVER put joins in the FROM clause! Ever!

Are there any efficiency benefits? (As thats all I'm really bothered about).

Don't think there are any performance implications one way or the other.

Usually, I would expect to be given project development standards, and conform to those, rather than expect to know during a technical test what their standards are. That said, I don't do technical tests any more - never been asked, apart from for permie roles, and they were Q&A rather than coding exercises.

I hope you write your code better than some of the crap that is in eBusiness Suite, SA!

[MobileCheese]

My tech test was an hour long over the phone to the US, didnt work out how I planned ended up having to take the call on the mobile. Alot of questions involved me having to reel off an SQL statement, PLSQL from my head and speak it down the phone, not as easy as it sounds as it wasnt just simple SELECT statements, had to use inline views, subquery, cursors, I was abit taken back, normally they just want real basics like difference between UNION and UNION ALL etc

In the end it came down to the fact the other candidate was already using the same standard as them at his current gig, so understandable really.

Ahh well Ive learnt something.

[TheFaQQer]

My tech test was an hour long over the phone to the US, didnt work out how I planned ended up having to take the call on the mobile. Alot of questions involved me having to reel off an SQL statement, PLSQL from my head and speak it down the phone, not as easy as it sounds as it wasnt just simple SELECT statements, had to use inline views, subquery, cursors, I was abit taken back, normally they just want real basics like difference between UNION and UNION ALL etc

In the end it came down to the fact the other candidate was already using the same standard as them at his current gig, so understandable really.

Ahh well Ive learnt something.

Sounds like you're best off out of it, to be honest.

[DBA_bloke]

ANSI SQL99-compliance ain't always as yummy as it looks.

[oracleslave]

crap that is in eBusiness Suite, SA!

Which is not a bad thing....keeps us in work!

[Cowboy Bob]

ANSI SQL99-compliance ain't always as yummy as it looks.

WTF? From your linked article:-

SELECT dept.dname,emp.ename,emp.sal
FROM dept JOIN emp USING(deptno)
WHERE emp.sal > 1000;

That's not what I'd call standard sequel either. I'd write it as:-

SELECT dept.dname, emp.ename, emp.sal
FROM dept, emp
WHERE dept.deptno = emp.deptno
AND emp.sal > 1000;

The above code will run on any database known to man (or at least me). The topmost example is obviously an Oracle "fanboi" trying to make the standard look worse than it actually is.

[css_jay99]

I've never use them...I always use the notation you described MC in the where clause.

I'm sorry to hear this (as you wanted the gig) but I think if they were that pedantic they'd be utter c*cks to work for! You've probably had a narrow escape mate!
I mean, why couldn't they just tell you they prefered it done that way once you joined them? Why would they not give you the gig because of that?!!

I agree with you here, I never used ANSI standard. I was on a gig last year that required a lot of work on SQL SERVER and ANSI literally did my head in. I still detest it.

I tried to use it in my last gig which was datawarehouse hence complex queries/joins .... in the end told client i cant guarantee the results even if they come out right.

cant teach an old dog new tricks

[barely_pointless]

I agree with you here, I never used ANSI standard. I was on a gig last year that required a lot of work on SQL SERVER and ANSI literally did my head in. I still detest it.

I tried to use it in my last gig which was datawarehouse hence complex queries/joins .... in the end told client i cant guarantee the results even if they come out right.

cant teach an old dog new tricks

They do sound like a bunch of pedants, like a little learning is a dangerous thing, probably some clown running the show who is inexperienced, but really think he/she knows it all , besides Oracle is going ANSI anyway, I did about 2 years in Terrordata about 4 odd years ago and found the Terror-data sql extensions very intuitive and easy to use, I guess it's what you are used to. Some of the olap oracle stuff is way ahead of what terrordata has currently, but then some of the date handling and join syntax in ansi terrordata is often more succinct, it all depends, play whatever tune the client wants is my advice!

[Spacecadet]

WTF? From your linked article:-

SELECT dept.dname,emp.ename,emp.sal
FROM dept JOIN emp USING(deptno)
WHERE emp.sal > 1000;

That's not what I'd call standard sequel either. I'd write it as:-

SELECT dept.dname, emp.ename, emp.sal
FROM dept, emp
WHERE dept.deptno = emp.deptno
AND emp.sal > 1000;

The above code will run on any database known to man (or at least me). The topmost example is obviously an Oracle "fanboi" trying to make the standard look worse than it actually is.

personally I would use

SELECT dept.dname, emp.ename, emp.sal
  FROM dept 
 INNER JOIN emp
    ON dept.deptno = emp.deptno
 WHERE emp.sal > 1000;

[TheFaQQer]

WTF? From your linked article:-

SELECT dept.dname,emp.ename,emp.sal
FROM dept JOIN emp USING(deptno)
WHERE emp.sal > 1000;

That's not what I'd call standard sequel either. I'd write it as:-

SELECT dept.dname, emp.ename, emp.sal
FROM dept, emp
WHERE dept.deptno = emp.deptno
AND emp.sal > 1000;

I would write it the second way. However, if you use the ANSI standard, then it immediately becomes clear which part of the WHERE clause is used for data selection and what is used for joining between the tables.

If the client says that the first one is the project standard, then that's how I'd write it. If they don't specify, then I'd write it the second way, since that is the method that I am most familiar with.