Ryan,
Rewriting the WHERE clause of the query into "IF" logic would be something like
this:
public boolean ShouldGrabRecord(string @Region)
{
if(m.Region == @Region)
{
// grab this record because we have a match!
// continue and look at the next record
return true;
}
if(@Region == "Southwest" && m.Region == "N/L")
{
// grab this record since @Region came in as
// Southwest and the m.Region was 'N/L'
return true;
}
// there were no matches for anything
// do not get current record
return false;
}
---------- Original Message -----------
From: Ryan Everhart <[EMAIL PROTECTED]>
To: Dallas/Fort Worth ColdFusion User Group Mailing List <[email protected]>
Sent: Thu, 8 Dec 2005 17:19:34 -0600
Subject: Re: [DFW CFUG] T-SQL Where Statement
> Thanks David, I appreciate the feedback!
>
> What does this part of the statement do though?
>
> (
> @Region = 'Southwest'
> AND m.Region = 'N/L'
> )
>
> Does the @Region = 'Southwest' act like some IF logic?
>
> Ryan
>
> On 12/8/05, David L. Penton <[EMAIL PROTECTED]> wrote:
> >
> > Keep in mind that when you have an IN clause in your predicate list, it
> > unrolls:
> >
> > WHERE col IN ('a', 'b', 'c')
> >
> > is the same as
> >
> > WHERE (
> > col = 'a'
> > OR col = 'b'
> > OR col = 'c'
> > )
> >
> > So, instead of trying to make an IN clause work (which would have been a
> > bit messing
> > given your requirements) it was easier to consider the exploited view of
> > "IN"
> >
> > hth...
> >
> > - dave
> >
> > ---------- Original Message -----------
> > From: Ryan Everhart <[EMAIL PROTECTED]>
> > To: Dallas/Fort Worth ColdFusion User Group Mailing List <
> > [email protected]>
> > Sent: Thu, 8 Dec 2005 16:41:38 -0600
> > Subject: Re: [DFW CFUG] T-SQL Where Statement
> >
> > > Thank you David, I took you first suggestion and it worked. ALthough
> > I'm
> > > not sure how it's working.
> > >
> > > Ryan
> > >
> > > On 12/8/05, David L. Penton <[EMAIL PROTECTED]> wrote:
> > > >
> > > > If you were building dynamic sql then that would be *almost*
> > > > sufficient. Since I don't
> > > > think you are:
> > > >
> > > > select
> > > > m.* -- always write out column names
> > > > from
> > > > tbl m
> > > > where
> > > > m.Region = @Region
> > > > OR (
> > > > @Region = 'Southwest'
> > > > AND m.Region = 'N/L'
> > > > )
> > > >
> > > > You can alternatively do things like this (hopefully this doesn't
> > confuse
> > > > matters):
> > > >
> > > > select m.* from tbl m
> > > > where
> > > > m.Region = @Region
> > > >
> > > > UNION ALL
> > > >
> > > > select m.* from tbl m
> > > > where
> > > > @Region = 'Southwest'
> > > > AND m.Region = 'N/L'
> > > >
> > > > Or if this is a stored proc you can:
> > > >
> > > > IF @Region = 'Southwest'
> > > > BEGIN
> > > > select m.* from tbl m
> > > > where
> > > > m.Region IN ('Southwest', 'N/L')
> > > > END
> > > > ELSE
> > > > BEGIN
> > > > select m.* from tbl m
> > > > where
> > > > m.Region = @Region
> > > > END
> > > >
> > > > ---------- Original Message -----------
> > > > From: Ryan Everhart <[EMAIL PROTECTED]>
> > > > To: DFWCUG <[email protected]>
> > > > Sent: Thu, 8 Dec 2005 16:23:00 -0600
> > > > Subject: [DFW CFUG] T-SQL Where Statement
> > > >
> > > > > Hey all,
> > > > > I ned to do a where statement like this....
> > > > >
> > > > > WHERE M.Region IN (
> > > > > CASE @region
> > > > > WHEN 'Southwest' THEN '''Southwest''' +
> > ','
> > > > +
> > > > > '''N/L'''
> > > > > ELSE @Region
> > > > > END
> > > > > )
> > > > >
> > > > > Where if the @region variable is Southwest i need to get all values
> > IN
> > > > > ('Southwest','N/L') but if it's any other region I just need to get
> > that
> > > > > region's values. Does that make sense?
> > > > >
> > > > > The above code validates, but it does not return any results. Any
> > > > thoughts?
> > > > >
> > > > > Ryan
> > > > >
> > > > > --
> > > > > Ryan Everhart
> > > > > [EMAIL PROTECTED]
------- End of Original Message -------
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://lists1.safesecureweb.com/mailman/private/list
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.HostMySite.com
www.teksystems.com/
