James Smith wrote:
>> I don't think that is a very good solution. Not only can this not use
>> any indexes, it will also not match "Friends Season 7 - Complete Series"
>> because the order of the words doesn't match.
>
> I was originally breaking the string and doing several 'and's. I do need to
> I don't think that is a very good solution. Not only can this not use
> any indexes, it will also not match "Friends Season 7 - Complete Series"
> because the order of the words doesn't match.
I was originally breaking the string and doing several 'and's. I do need to
look into "Full Text Inde
James Smith wrote:
> As a last resort this procedure does the following...
>
> SET @searchTerm = '%' + REPLACE(@searchTerm,' ','%') + '%';
> ..
> ..
> ..
> AND (Title LIKE @searchTerm OR ArtistName LIKE @searchTerm)
>
> So that is someone searches for 'friends series 7' it is first turned into
3 17:32:04 2007
Subject: Re: Conditional SQL
On 6/13/07, James Smith <[EMAIL PROTECTED]> wrote:
> As a last resort this procedure does the following...
>
> SET @searchTerm = '%' + REPLACE(@searchTerm,' ','%') + '%';
> ..
> ..
> ..
>
On 6/13/07, James Smith <[EMAIL PROTECTED]> wrote:
> As a last resort this procedure does the following...
>
> SET @searchTerm = '%' + REPLACE(@searchTerm,' ','%') + '%';
> ..
> ..
> ..
> AND (Title LIKE @searchTerm OR ArtistName LIKE @searchTerm)
>
> So that is someone searches for 'friends se
to
use a "full text" searching query ("CONTAINS") and not LIKE.
Mark
-Original Message-
From: James Smith [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 13, 2007 11:56 AM
To: CF-Talk
Subject: RE: Conditional SQL
As a last resort this procedure does the follo
As a last resort this procedure does the following...
SET @searchTerm = '%' + REPLACE(@searchTerm,' ','%') + '%';
..
..
..
AND (Title LIKE @searchTerm OR ArtistName LIKE @searchTerm)
So that is someone searches for 'friends series 7' it is first turned into
'%friends%series%7%' which will mat
> This seems like a really complicated way of saying "AND ((ProductGroupID
> = @productgroupid) OR (@productgroupid = 0))". Am I missing something?
Works perfectly...
Simplest solutions are always the best, cheers.
--
Jay
~|
On 6/13/07, Gaulin, Mark <[EMAIL PROTECTED]> wrote:
> This seems like a really complicated way of saying "AND ((ProductGroupID
> = @productgroupid) OR (@productgroupid = 0))". Am I missing something?
>
I think "really" complicated is a bit harsh...perhaps "more"
complicated...and the previous ver
This seems like a really complicated way of saying "AND ((ProductGroupID
= @productgroupid) OR (@productgroupid = 0))". Am I missing something?
-Original Message-
From: Jim Wright [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 13, 2007 7:31 AM
To: CF-Talk
Subject: Re: Condi
On 6/13/07, James Smith <[EMAIL PROTECTED]> wrote:
> > AND ProductGroupID = CASE WHEN @productgroupid > 0 THEN
> > @productgroupid ELSE ProductGroupID END
>
> This is the simplest to read but has a drawback, it the stored
> ProductGroupID is null then the statement becomes AND ProductGroupID =
> Pr
> AND ProductGroupID = CASE WHEN @productgroupid > 0 THEN
> @productgroupid ELSE ProductGroupID END
This is the simplest to read but has a drawback, it the stored
ProductGroupID is null then the statement becomes AND ProductGroupID =
ProductGroupID and for some reason NULL does not equal NULL so t
James Smith wrote:
> the query is actually very complex involving several loops and a few
> conditional clauses
Your query has several loops? You mean you actually use the new
hierargical query features of MS SQL 2005? Or does the CF code that
generates the query involve several loops and condit
the query is actually very complex involving several loops and a few
conditional clauses and I am hoping that by sticking it all into a
stored procedure it will be precompiled by MSSQL and therefore more
efficient.
On 12/06/07, Jochem van Dieten <[EMAIL PROTECTED]> wrote:
> James Smith wrote:
> >
James Smith wrote:
>
> SELECT Title, ProductID
> FROM aTable
> WHERE Stock > 0
>
> AND ProductGroupID = #val(productgroupid)#
>
>
> I wish to move this query into MSSQL server for performance reasons
Which performance reasons? Why do you expect an improvement of the
performance from moving
>
> I wish to move this query into MSSQL server for performance reasons, how do
> I go about running the conditional code? I have tried...
>
I'm not sure you are going to get the performance benefit you are
looking for. Doing the conditional processing in SQL isn't
necessarily going to be faster
You could use the CASE statement instead.
Or you could write a stored procedure which does exactly the thing you want.
DECLARE @productgroupid bigint;
SET @productgroupid = 5;
IF @productgroupid > 0 BEGIN
SELECT Title, ProductID
FROM aTable
WHERE Stock > 0
AND
You can do the following
and then run the query as
DECLARE @productgroupid int;
SET @productgroupid = #queryParams.productgroupid#;
SELECT Title, ProductID
FROM aTable
WHERE
Stock > 0
AND
m.ProductGroupID = coalesce(nullIf(@productgroupid,-10001),m.ProductGroupID)
W
Try this:
DECLARE @productgroupid bigint;
SET @productgroupid = 5;
SELECT Title, ProductID
FROM aTable
WHERE Stock > 0
AND (m.ProductGroupID = @productgroupid AND @productgroupid > 0)
Koen
~|
ColdFusion MX7 and Flex 2
Bu
I currently have a (very complex) query in the format...
SELECT Title, ProductID
FROM aTable
WHERE Stock > 0
AND ProductGroupID = #val(productgroupid)#
I wish to move this query into MSSQL server for performance reasons, how do
I go about running the conditional code? I have tried...
DE
Sent: Thursday, July 03, 2003 6:17 AM
To: CF-Talk
Subject: Conditional SQL Query Not Working
I have a form, that asks for a number of 'conditions' in order to find an
attendee for a course. The problem is, I've put together a query that
returns too many results / not the correct
Jillian Carroll wrote:
> I have a form, that asks for a number of 'conditions' in order to find an
> attendee for a course. The problem is, I've put together a query that
> returns too many results / not the correct results. Every row returned is
> returned multiple times. I'm wondering if I'm
I have a form, that asks for a number of 'conditions' in order to find an
attendee for a course. The problem is, I've put together a query that
returns too many results / not the correct results. Every row returned is
returned multiple times. I'm wondering if I'm needing a join somewhere...
or i
23 matches
Mail list logo
