Re: Referencing a column from another tables data
- From: zed500 <zed500@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 10 Oct 2005 10:56:01 -0700
Thanks Hugo. Your help is much appreciated.
"Hugo Kornelis" wrote:
> On Fri, 7 Oct 2005 10:13:04 -0700, zed500 wrote:
>
> >The problem is that the database was developed by an outside vendor and is
> >used by software that they also developed. So that handcuffs me from changing
> >the database design at all. The whole thing is poorly designed and
> >unfortunately I have to find work arounds to create queries.
>
> Hi zed500,
>
> You *could* consider making a normalized design, porting over the data,
> then create some views to present the data in the current, bad design.
> You will probably have to design some INSTEAD OF triggers to translate
> any changes the 3rd party app makes to the appropriate changes in the
> underlying data. (But do check if this doesn't violate the license terms
> first!)
>
> But you'll have to consider how much time and energy this takes, and
> balance that against the gain you'll have from it. If you're just adding
> a few reports to a big app, working around the limitations will probably
> be better.
>
> > Here is the work
> >around that I figured out.
> (snip)
> >Remember, I am a newbie. Is there anything that could be written better in
> >that statement? Thanks Hugo
>
> Hardly. The only possible improvements I see are:
>
> a) use a simple case instead of a searched case, i.e. convert this:
>
> > CASE
> > WHEN QCC_DefectCodes.Page = 1 THEN QCC_Groups.Page1Title
> > WHEN QCC_DefectCodes.Page = 2 THEN QCC_Groups.Page2Title
> (...)
> > ELSE 'Not Completed'
>
> to
>
> CASE QCC_DefectCodes.Page
> WHEN 1 THEN QCC_Groups.Page1Title
> WHEN 2 THEN QCC_Groups.Page2Title
> (...)
> ELSE 'Not Completed'
>
> And a similar change for the second CASE (based on Retired).
>
> b) Get rid of all the parentheses in the WHERE clause.
>
> c) Use short, mnemonic table aliases instead of retyping the complete
> table name each time:
>
> SELECT CASE dc.Page
> WHEN 1 THEN g.Page1Title
> WHEN 2 THEN g.Page2Title
> (...)
> ELSE 'Not Completed'
> END AS Pages,
> dc.PageOffset,
> dc.Description,
> CASE dc.Retired
> WHEN 0 THEN 'Active'
> WHEN 1 THEN 'Retired'
> ELSE 'Not Known'
> END AS Results
> FROM QCC_DefectCodes AS dc
> INNER JOIN QCC_Groups AS g
> ON dc.GroupID = g.GroupID
> WHERE dc.GroupID = 'B'
> ORDER BY dc.Page, dc.PageOffset;
>
> But all the three above improvements are all about readability, they
> don't change the actual query one bit. (And readability is very much a
> matter of personal taste - there are also many people who prefer to use
> the searched case exclusively, use many parentheses for clarity, and
> prefer full table names over mnemonic aliases.
>
> Bottom line is: use my ideas if you like them better, ditch them if you
> prefer your own style :-)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
.
- References:
- Re: Referencing a column from another tables data
- From: Hugo Kornelis
- Re: Referencing a column from another tables data
- From: zed500
- Re: Referencing a column from another tables data
- From: Hugo Kornelis
- Re: Referencing a column from another tables data
- Prev by Date: Re: Referencing a column from another tables data
- Next by Date: Re: Selecting random rows from a dataset
- Previous by thread: Re: Referencing a column from another tables data
- Next by thread: Re: create log file for a scrip
- Index(es):
Relevant Pages
|
|
