Re: Query Help

On Jan 16, 9:13 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jan 16, 7:29 pm, spamb..._at_milmac.com (Doug Miller) wrote:
>
> > In article <ca0b7d13-f686-4233-8700-13316c17c..._at_e25g2000prg.googlegroups.com>, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> > >If you are famailar with BASIC programming, a similar construct might
> > >look like this:
> > >  IF (AGE <> 65) OR (AGE =3D 65 AND RETIRED =3D "Y") THEN
>
> > There's no point in examining AGE in the second clause.
> > IF (AGE <> 65) OR (RETIRED = "Y") THEN
> > has exactly the same effect.
>
> > --
> > Regards,
> >         Doug Miller (alphageek at milmac dot com)
>
> > It's time to throw all their damned tea in the harbor again.
>
> You are correct, I probably should have continued the discussion to
> simplify the pseudo code.  However, my response was intentially
> constructed that way to demonstrate to the OP how one might translate
> a generic written requirement into basic pseudo code, and from there
> into a WHERE clause.
>
> The OP wrote:
>
> "I would like to find all instances where the Food_ID does not have a
> Cat_ID of 30 or has a Cat_ID of 30, but it is Inactive. In the
> example
> shown above, I would see Food_ID of 302, but not 301."
>
> Unfortunately, my pseudo code headed in the wrong direction, as
> pointed out in a later post by the OP.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Just a minor correction to my previous post... additional thoughts shortly after submitting the previous post. These two logic checks are not necessarily equivalent when dealing with data stored in a database, but are equivalent when dealing only with Boolean logic: IF (AGE <> 65) OR (AGE = 65 AND RETIRED = "Y") THEN

IF (AGE <> 65) OR (RETIRED = "Y") THEN

The first expression does not permit NULL values for the AGE, while the second does. In databases, NULL values often represent an unknown value, or a value that does not apply to the record. The NULL value might represent 65, 42, or something else.

For example, assume the following request for data. Find all routers that are gigabit, or those that are not gigabit and have a 1/2" collet. The majority of Cisco, 3COM, HP, Dell, Linksys, DLink, and Linux routers likely have a concept of gigabit or megabit, but have no concept of collet size, so the COLLET column (field) might be left blank (NULL) in the database for those manufacturers. The majority of Milwaukee, Porter Cable, Bosch, and Dewalt likely have a concept of collet size, and may or may not have a concept of gigabit (that column might be left blank). In such a case, the following two checks will likely return different records:
IF (GIGABIT = TRUE) OR (GIGABIT = FALSE AND COLLET = 0.5) THEN IF (GIGABIT = TRUE) OR (COLLET = 0.5) THEN The first check might only return those routers that direct bits, while the second might include routers that both direct bits and those that make direct use of bits.

Bool vs. Codd

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Jan 17 2008 - 05:33:27 CST