Select query with embedded if

General
1

Trying to get the following query to run but keep getting an error.

Select field1, field2, if(length(field3) >10,“Red”, “Black”) as misc from table1

I want the 3rd field of the resulting query to be based on the length of field3

Thanks

2

What DB backend? What error?

3

What database engine are you using? Is “length” the correct function? SQL server I think uses Len for example.

4

REALSQLdb
I also tried “len”

5

I heard it was an ancient SQLite, so maybe this works there

SELECT field1, field2, CASE WHEN LENGTH(field3) > 10 THEN 'Red' ELSE 'Black' END AS misc FROM table1
6

length(field3) as misc works

it fails when I include the “if”

7

Rick, trying it now…

8

Alternatively try “iif” function. Short for inline if. That’s supposed to work with SQLite but I’m not sure which version it started with.

9

All

Thanks for your interest and quick response.

Rick A - Thanks!! it worked.

I’ve been using my REALSQLdb for many many years. Maybe it’s time to investigate an upgrade.

Jim

1 Like
10

SQLite is the engine behind RealSQLdb. It would likely be a direct replacement.

11

Ian
Thanks
Will start looking into updating today