Re: Switching in the Where Clause

[James Womeldorf <jwomeldo@FASTENAL.COM>]

Oops!  My mistake. Thanks Denise, you are correct.
Jim

-----Original Message-----
From: Denise White [mailto:dewhite@VICR.COM]
Sent: Tuesday, September 12, 2000 8:22 AM
To: SQR-USERS@list.iex.net
Subject: Re: Switching in the Where Clause


Norman,

I think that the parentheses are misplaced in James' message.  I believe it
would give you all rows with RowType='B', regardless of the other
conditions,
which are all joined with 'and'.  You need parentheses around the conditions
you
want to join with 'or'.  I also don't think you can have the comment INSIDE
the
parentheses; the close parentheses would be considered part of the comment.
Since you indicate that all rows are either 'A' or 'B', the following should
work:

Select Blah...
>From MyTable
Where field1 > 6
     and field2 > 'm'
     and (RowType = 'B' or field3 <> 'sold')  !CONTAINS A QUOTED REFERENCE

John's suggestion of using 'union' should also work.

HTH,
Denise White
Sr. Programmer/Analyst
Vicor

------------------------------

Date:    Mon, 11 Sep 2000 16:09:06 -0500
From:    James Womeldorf <jwomeldo@FASTENAL.COM>
Subject: Re: Switching in the Where Clause

Try

Select Blah...
>From MyTable
Where field1 > 6
     and field2 > 'm'
     and (RowType = "A"
     and field3 <> 'sold'  !CONTAINS A QUOTED REFERENCE)
     or
     (RowType = "B")

------------------------------

Date:    Mon, 11 Sep 2000 14:10:51 -0700
From:    John Doel <john_doel@PEOPLESOFT.COM>
Subject: Re: Switching in the Where Clause

Try a union for simple readability:

Select Blah...
>From MyTable
Where field1 > 6
     and field2 > 'm'
     and field3 <> 'sold'  !CONTAINS A QUOTED REFERENCE
AND ROWTYPE = 'A'

UNION

Select Blah...
>From MyTable
Where field1 > 6
     and field2 > 'm'
AND ROWTYPE = 'B'


John M. Doel
Consultant
PeopleSoft Consulting USA
Phone: (877) 339-2982

-----Original Message-----
From: NEDOLPH@AOL.COM [mailto:NEDOLPH@AOL.COM]
Sent: Monday, September 11, 2000 3:57 PM
To: SQR-USERS@list.iex.net
Subject: Switching in the Where Clause


Assume I have a MyTable of 1500 rows; some of RowType = "A" and some of
RowType = "B"...

For the "A" rows I want to:

Select Blah...
>From MyTable
Where field1 > 6
     and field2 > 'm'
     and field3 <> 'sold'  !CONTAINS A QUOTED REFERENCE

for the "B" rows, I want to suppress the last line of the WHERE clause to
make:

Select Blah...
>From MyTable
Where field1 > 6
     and field2 > 'm'

I tried switching the last clause in and out with an IF statement (IF
Rowtype = 'A' ...) but that failed miserably.  What am I doing wrong?

Sincerely,
Norman Dolph