an if clause in a sql query

[wengang]

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

[dee-u]

I believe your database is SQL Server, couldn't you use Stored Procedures?

[Ecniv]

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.

[erickwidya]

try this

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

[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.

[dee-u]

Since the field is text it's value cannot be converted to true or false, perhaps you would need another field for it.

[wengang]

but this is not really a conversion, it is essentially creating a new field based on another field's value

[Ecniv]

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:

(iif(Instr(1,[ArticleHTML], '<img') > 0,'True','False') as HaveImg)

[FishGuy]

If its access then you can use IIF dont know about substring though

[brucevde]

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

[wengang]

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()

[wengang]

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

[Ecniv]

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.

[si_the_geek]

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.

[wengang]

Geez!

That was the solution to start with.
Okay, nobody look at me!