SQL 3.5 CE IF Len Syntex

[Dashley]

Hi,

SQL Server 3.5 Compact DB

I have a customer tbl (Cust) lastname, firstname, company etc..

the data im trying to select is:

If the length of the cust.lastname > 2 then select the cust.lastname, else select cust.company.

I've tried if exists, len, and case clauses. I'm not enjoying this Compact Sql Lite. Humm

-thanks

 

[SQLBill]

You aren't providing us with what you tried and the errors you are getting so we can't tell what you are doing wrong. Can you provide some of your 'attempts' and what errors you got?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Dashley]

Hi SqlBill,


select case when len(lastname) > 2
then lastname
else
company end
from cust

The SQL was working it was the data returned that was giving me a fit.
The client had multiple lastname,firstname and company fields with just "blank spaces" as data.
Sorry about the missing "attempts". They are going to get a supprise next time they try to push blank space in.

Sigh...

 

[flapeyre]

Try this:

SELECT
	CASE
	WHEN LEN([COLOR=#CC0000]LTRIM(RTRIM([/color]LastName)[COLOR=#CC0000])[/color] > 2
		THEN LastName
		ELSE company
	END
	FROM cust

-- Francis
Francisus ego, sed non sum papa.

 

[Dashley]

That will work too flapeyre. I've trapped it at the application input level too.
Thanks