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 Indexing
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
look
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 the
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 =
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: Conditional SQL
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 version
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
~|
CF
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
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 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 series 7' it
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,' ','%') + '%';
..
..
..
AND (Title LIKE @searchTerm OR ArtistName LIKE @searchTerm)
So that is someone
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
I currently have a (very complex) query in the format...
SELECT Title, ProductID
FROM aTable
WHERE Stock 0
cfif len(trim(queryParams.productgroupid)) GT 0
AND ProductGroupID = #val(productgroupid)#
/cfif
I wish to move this query into MSSQL server for performance reasons, how do
I go
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
You can do the following
cfparam name=queryParams.productgroupid default=-10001
and then run the query as
cfquery
DECLARE @productgroupid int;
SET @productgroupid = #queryParams.productgroupid#;
SELECT Title, ProductID
FROM aTable
WHERE
Stock 0
AND
m.ProductGroupID =
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
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
James Smith wrote:
SELECT Title, ProductID
FROM aTable
WHERE Stock 0
cfif len(trim(queryParams.productgroupid)) GT 0
AND ProductGroupID = #val(productgroupid)#
/cfif
I wish to move this query into MSSQL server for performance reasons
Which performance reasons? Why do you expect an
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:
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
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
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
: 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 results. Every row
23 matches
Mail list logo
