String vs Numeric Type (2)

[Follwing up on String vs Numeric Type
<URL:http://groups.google.com/group/comp.databases.ibm-db2/browse_frm/thread/77ece2ac84980feb/3c58dd32c336fdd7?q=17-way&rnum=1#3c58dd32c336fdd7>]
[color=blue]
>A) If you have a 17-way join where the aliases are all one letter,
>you should fire all your programmers and get people who think.[/color]

My DBA slammed this at me when he saw i did that with a 14-way join.
Ha! :)

My excuse is:

One PROCEDURE that has one query, that SELECTs from one TABLE and gets
names off of FKs to 13 lookup TABLEs, so i put it in a join (as opposed
to 13 sub-SELECTs). Originally, i used all the TABLE names, but given
the size of their names, i couldn't see the entire thing on one screen
(in notepad) so i changed the main TABLE to be aliased as Main, and the
lookups as A, B, C, etc.. So my query (basically) looks like

SELECT Main.col1, Main.Col2, Main.A, A.text, Main.B, B.Text etc...
FROM ...
WHERE A.Id = Main.A AND B.Id = Main.B AND etc...

I think it is justified because it is a simple query and this way it is
easier to read (and therefore understand). But feel free to point and
laugh. :)

B.

re: String vs Numeric Type (2)

Brian Tkatch wrote:
[color=blue]
> One PROCEDURE that has one query, that SELECTs from one TABLE and gets
> names off of FKs to 13 lookup TABLEs, so i put it in a join (as opposed
> to 13 sub-SELECTs). Originally, i used all the TABLE names, but given
> the size of their names, i couldn't see the entire thing on one screen
> (in notepad) so i changed the main TABLE to be aliased as Main, and the
> lookups as A, B, C, etc.. So my query (basically) looks like
>
> SELECT Main.col1, Main.Col2, Main.A, A.text, Main.B, B.Text etc...
> FROM ...
> WHERE A.Id = Main.A AND B.Id = Main.B AND etc...[/color]

Gosh, that looks ugly and clutters the statement making its meaning less
obvious. I would have used the sub-selects to make the structure of the
query clear, i.e. only data from the main table is requested and
restrictions are imposed based on the other 13 tables. The rest is up to
the optimizer to straighten out.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

re: String vs Numeric Type (2)

Well, when i format it, it looks real nice. :)

Normally, i would use sub-selects, but in DB2 i have generally noticed
that joins are more effective, or at least i bumped into a few queries
that were like that, so by default in DB2 i use a join.

B.