an if clause in a sql query
hi all.
I'm just wondering if I can reduce the recordset size on my web page.
I have a query like:
select Top 50 ID, Field1,field2,field3 from tblRecords where....
Field1 in this example is very large text field, and since there are 50, the page is slow.
In fact, I don't need the text field to be returned, what I need is a true or false value to indicate if a substring is present in the field. And I do need the other fields. So I can't restrict this in the where clause.
What I need is:
Select Top 50 ID, Field2, Field3, (if field1 like '%MyPhrase%' Then True Else False) where....
So the top 50 records are returned, but instead of the big text field of data, a True or False is returned per record.
Is that possible? Cast the field type somehow?
Anybody know this? Thanks.
Wengang
Re: an if clause in a sql query
I believe your database is SQL Server, couldn't you use Stored Procedures?
Re: an if clause in a sql query
instr function (not sure if this is in Sql server) but it should do the job you want.
Alternatively there is the iif function.
Not sure about time problems when nusing, but worth a try.
Re: an if clause in a sql query
try this
Code:
Select Top 50 ID, Field2, Field3,
(CASE WHEN SUBSTRING(field1, 1, 8) = 'MyPhrase' THEN 'True'
ELSE 'False') where....
syntax :
SUBSTRING ( expression , start , length )
search for CASE and SUBSTRING in BOL for more information
PS : i assume it's in MSSQL, for Access try Ecniv's
regards
Re: an if clause in a sql query
Hey all.
This one is Access, Not SS
I tried with IIF but I guess i worded it wrong. the error is:
Syntax error (missing operator) in query expression '(iif(Instr(ArticleHTML, '<img') > 0,'True','False') as HaveImg)'.
basically the same error when using Case When.
Re: an if clause in a sql query
Since the field is text it's value cannot be converted to true or false, perhaps you would need another field for it.
Re: an if clause in a sql query
but this is not really a conversion, it is essentially creating a new field based on another field's value
Re: an if clause in a sql query
Quote:
Originally Posted by wengang
Hey all.
This one is Access, Not SS
I tried with IIF but I guess i worded it wrong. the error is:
Syntax error (missing operator) in query expression '(iif(Instr(ArticleHTML, '<img') > 0,'True','False') as HaveImg)'.
basically the same error when using Case When.
Close, try this:
Code:
(iif(Instr(1,[ArticleHTML], '<img') > 0,'True','False') as HaveImg)
Re: an if clause in a sql query
If its access then you can use IIF dont know about substring though
Re: an if clause in a sql query
The first argument to the Instr function is not required if you want to start at the beginning of the string.
The problem is that the placement of the brackets is incorrect.
(iif(Instr(1,[ArticleHTML], '<img') > 0,'True','False') as HaveImg)
should be
(iif(Instr(1,[ArticleHTML], '<img') > 0,'True','False')) as HaveImg
or Just
iif(Instr(1,[ArticleHTML], '<img') > 0,'True','False') as HaveImg
Re: an if clause in a sql query
yes they both work. The extra parenthesis set was my doing, just for clarity.
Also I changed 'True','False' to True, False. They were coming back as strings instead of boolean values.
oddly, though, the page didn't load faster, it loaded slower, took almost twice as long.
What I was doing before was loading the field ArticleHTML, which in some cases was pretty large, then in ASP doing InStr to see if the article had an image or not.
I thought the page would load faster if I didn't require that enormous data chunk returned in the RS.
Does it seem logical that it slowed down? Maybe a different syntax would be faster.
Thanks for the solution. In either case, it's good to know that SQL supports IIF()
Re: an if clause in a sql query
Just another quick note. I changed that to
(iif([ArticleHTML] like '%<img%',True,False)) as HaveImg
and no improvement on speed either, nearly the same
Re: an if clause in a sql query
Hmmm well you are checking each records field data each time. Perhaps if you saved a flag that was set as the data was stored, then you wouldn't need to do that check (instr or like) on the records... might speed up the return a little.
Re: an if clause in a sql query
That would be a good idea in terms of speed, as Like with % at each end of the string is the slowest possible comparison you can do.
The awkward part is making sure that the flag field is kept up to date, as i don't think that Access supports Triggers. Basically you would have to make sure it is updated each time a row of data is inserted/updated.
Re: an if clause in a sql query
Geez!
That was the solution to start with.
Okay, nobody look at me!