Thanks David, I appreciate the feedback!
What does this part of the statement do though?
(
@Region = 'Southwest'
AND m.Region = 'N/L'
)
@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
> >
> >
> >
> > --
> > David L. Penton, Microsoft MVP
> > JCPenney Application Specialist - Lead
> > "Mathematics is music for the mind, and Music is Mathematics for the
> > Soul. - J.S. Bach"
> > Blog @ http://blogs.SqlAdvice.com/dpenton/
> >
> > Do you have SQL BOL installed? If not, why not?
> > New SP3 SQL BOL: http://www.davidpenton.com/sqlbol
> >
> > ---------- 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 -------
_______________________________________________
List mailing list
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/
--
Ryan Everhart
[EMAIL PROTECTED]
_______________________________________________ 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/
