Okay, I've stumbled onto an odd problem. Our System Analyst here at Techsoft is an Oracle guy. He does all these things in Oracle, and then expects us to carry out similar activities in our applications and in SQL SERVER.

Usually it's not a problem.

Oh, but when it is....

He created a table, and populated it in both the Oracle and SQL Server versions of our database. It contained ID, CODE, Name, Description and PARENT_ID columns, and basically was designed to store/output hierarchical data, like you'd find in a TREE control.

Yeah.

So, I spent a good half-hour banging my head, trying to figure out how to generate a SQL statement that would produce the relevant datasource in an easy to use format. So I went to the guy who knows more about SQL Server than me. While we're hashing out possibilities, in walks Mr. Oracle. He seemed to think it was no problem, and produced this ORACLE query:

Select LPad ( ' ', 2 * ( Level - 1 ) ) || Code Code, Name, Description
From JCA
Start With ID = 0
Connect by Parent_ID = Prior ID;

Yeah, and that's where I really started banging my head. LPAD doesn't exist in Transact SQL. Nor does CONNECT BY, and I have my reservations about START WITH.

Of course, what annoys me the most is the misleading GROUP BY. Despite the name, GROUP BY does not group things. It aggregates things. It's an important difference which gives me migraines. If I wanted to aggregate things, I'd look for a function called AGGREGATE BY. If there's a clause called GROUP BY I would expect it to group things according to the column I pass in. After all, that's the way it would work in a programming language.

But nooo... this is databases... :|



Okay, my rant is over, no to once more try to figure out the TSQL version of that evil ORACLE query.