Re: Referencing a column from another tables data

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

Relevant Pages

  • C# programmer looking for a job
    ... Software Development including Desktop, Client/Server and Database ... Practical skills in object oriented design and design patterns ... XML, Oracle, CVS, VSS, Delphi, bug tracking. ... Developed in Delphi5; ...
    (misc.immigration.usa)
  • Re: O/R Mapper
    ... | - create E/R model from niam model ... classes that contain, not only data, but also functionality as OO design is ... a database where they do not exist in the object model is corrupting the ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Date range on reports
    ... > box to your report with a control source like: ... >> In the Database window (Database window: The window that appears when you ... >> In the New Form dialog box, click Design View, and click OK. ... >> Begin by clicking Macro Names to display the Macro Name column. ...
    (microsoft.public.access.reports)
  • Re: limit of lines?
    ... And this is the fundamental mistake in your design. ... a objects from a database, this usually leads to very poor design. ... Framework in which all properties are backed, not by a simple field, but by ... we have major size projects that use the Delphi template ...
    (borland.public.delphi.non-technical)
  • Re: O/R Mapper
    ... > as OO design is not just about data in isolation. ... the proper tooling/methods are used which fit with the approach chosen. ... >> of the reality to come to the proper result. ... > constraints into a database where they do not exist in the object ...
    (microsoft.public.dotnet.languages.csharp)