An Oracle coder in a Teradata world

My blog goes through quiet patches. The current one is because I'm not doing any Oracle work, but am actually using Teradata. There's not much blogging about Teradata. I can't even recall a mention in Pythian's Log Buffer. Maybe they just don't talk much.

Using a different SQL syntax does make you think a little.
For example, Teradata doesn't have DUAL. Instead you can just write "SELECT current_date" and leave it at that. [Yeah, it doesn't have SYSDATE either.] But you can also write "SELECT table.column" too, rather than "SELECT column FROM table". You can even do "SELECT table1.column from table2" which is equivalent to "select table1.column from table1, table2" in Oracle.

So what you DON'T want to do is create a few working tables with very similar names and then set off a query like
"SELECT oct23.val, ref.desc FROM oct22 JOIN ref ON oct22.ref_id = ref.ref_id".

Like SQL Server, a teradata "database" is more like a schema in Oracle. Rather than do an ALTER SESSION SET CURRENT_SCHEMA=..., you can do a SET DATABASE ...
And just to add to the spice of it, you can actually set up a sort of path of databases in which it will look for the tables. So if you mistype your table name or alias, it can go looking for that name in all sorts of places.

I'll follow the practice of the terrible programmer mentioned by Tom Kyte, and program defensively. No SET DATABASE for me. I'll specify the schema (or database) myself, and use aliases for all the tables.

There's no ROWNUM in Teradata either. Instead you do a "SELECT TOP 5 * FROM table". But if you do "SELECT TOP 5 * FROM table ORDER BY column" it does the ORDER BY first, then returns the first five rows.

Like pretty much every-one but Oracle, Teradata differentiates between a zero-length string and a null value.

It does have a few niceties that I'd like Oracle to adopt. Firstly you can specify an expression alias in the GROUP BY clause.
"SELECT CASE
WHEN column1 BETWEEN column2 AND column3 THEN column4 ELSE column5 END val,
COUNT(*)
FROM table
GROUP BY val"
Boy, does that save some COPY/PASTE.

There's also a QUALIFY clause. You know how HAVING is like a WHERE for a GROUP BY ? Well QUALIFY is like a WHERE for an analytic function, so you can do something like
SELECT id, start_date, end_date
FROM table
QUALIFY rank() OVER (PARTITION BY id ORDER BY start_date desc) = 1
rather than making the whole thing an inline view.

Remember, the "S" in SQL stands for Structured, not Standard. It just ain't that portable.

The Teradata documentation is all PDF and I haven't found it as easy to navigate as the Oracle documentation set, but that might be more familiarity with where to look. I did upload all the PDFs into the XE database on my laptop so I could use Oracle Text to search them (using the Apex Document Library application). Is that cheating ?