Prev: DDL file usage
Next: DB2 transaction problem
From: The Boss on 7 Dec 2009 16:25
--CELKO-- wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. If you know how, follow ISO-11179 data element naming
> conventions and formatting rules. Temporal data should use ISO-8601
> formats. Code should be in Standard SQL as much as possible and not
> local dialect. It ought to be formatted so humans can read it.

While I agree with your sentiment in general, I don't think it is a sin to
use DB2's dialect in a DB2 dedicated group

>
> Sample data is also a good idea, along with clear specifications. It
> is very hard to debug code when you do not let us see it. If you want
> to learn how to ask a question on a Newsgroup, look at:
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> 1) Unless you have only ONE node, then you want to name that table
> Nodes.
> 2)Why did you think alphabetical aliases are a good idea?
> 3)Did you really need SELECT DISTINCT? If you have identifiers in the
> select list, this is not needed � unless the schema is a mess
> 4)How is a �wf_node_xx� data elemen5s logically different from a plain
> old �node_xx�? I have a feeling that you might have attribute
> splitting in the schema.
> 5)Think about what UPPER{'%') means. Think what �x LIKE '%'� means.
> To quote Homer Simpson, �DOH!�
> 6)SQL Programmers use IN() and not a string of ORs; this is not BASIC,
> FORTRAN or COBOL.
> 7)Never use UPPER() like that; put this into the DDL with CHECK()
> constraints
>
> I tried to clean it up, but without DDL, there is no way to help you.
> All we can do is guess. Here is mine:
>
> SELECT N.wf_node_id, N.wf_node_dt, H.wf_seq, N.node_like_desc,
> H.wf_user_name
> FROM Panton.WFI_Nodes AS N,
> Panton.Fmedia AS FM,
> Panton.WF_Hndr AS H,
> Panton.Nodes AS N
> WHERE N.wf_dat_node_id = F.fm_node_id
> AND N.wf_dat_node_dt = F.fm_node_dt
> AND N.wf_dat_graph_id = F.fm_graph_id
> AND FM.fmfo_fname LIKE 'RL_SP0%'
> AND N.wf_node_id = H.wf_node_id
> AND N.wf_node_dt = H.wf_node_dt
> AND H.wf_status IN ('R', 'S', 'Q', 'V')
> AND H.wf_node_id = N.node_id
> AND H.wf_node_dt = N.node_dt;

Why did you think duplicate aliases are a good idea? ...
Or, for the same table, using a different alias in the WHERE-clause as
opposed to the FROM-clause?

;-)

--
Jeroen


First  |  Prev  | 
Pages: 1 2
Prev: DDL file usage
Next: DB2 transaction problem