470,594 Members | 1,444 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

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>]
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.


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.

Jan 24 '06 #1
2 1233
Brian Tkatch wrote:
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...


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
Jan 24 '06 #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.

Jan 25 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

24 posts views Thread by MU | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.