jjacksn
asked on
How to select cast to boolean
Does MS ACCESS support CASE statements like sql?
I basically want to do this:
select customerNo,
(select case count(*) from visits where visits.customerNo = customerNo
when 0 then false
else true
end)
from
Customers
basically, getting a true or false column if they have ever had a visit.
I basically want to do this:
select customerNo,
(select case count(*) from visits where visits.customerNo = customerNo
when 0 then false
else true
end)
from
Customers
basically, getting a true or false column if they have ever had a visit.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jim Horn
(too slow)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or, for that matter:
select customerNo,
customerNo In (select customerNo from visits) as HasVisits
from Customers
Cheers!
(°v°)
select customerNo,
customerNo In (select customerNo from visits) as HasVisits
from Customers
Cheers!
(°v°)
For speed, I think I'd vote with rockiroads and jimhorn's suggestions
But I think you should write the query like this:
select customerNo, iif(count(*) > 0 , true, false)
from Customers as c
inner join visits as v
on c.customerno = v.customerno
group by customerNo
or like this
select customerNo, iif(cc>0, true, false)
FROM (
select customerNo, count(*) as cc
from Customers as c
inner join visits as v
on c.customerno = v.customerno
group by customerNo
) as subquery
Evaluate for speed and let us know which is quicker for you.
select customerNo, iif(count(*) > 0 , true, false)
from Customers as c
inner join visits as v
on c.customerno = v.customerno
group by customerNo
or like this
select customerNo, iif(cc>0, true, false)
FROM (
select customerNo, count(*) as cc
from Customers as c
inner join visits as v
on c.customerno = v.customerno
group by customerNo
) as subquery
Evaluate for speed and let us know which is quicker for you.
I beg to differ, but
IIf( something > 0, True, False ) As Result
is exactly equivalent to
( something > 0 ) As Result
only slower. Why call a VB function to tell us that true is true and false is false?
(°v°)
IIf( something > 0, True, False ) As Result
is exactly equivalent to
( something > 0 ) As Result
only slower. Why call a VB function to tell us that true is true and false is false?
(°v°)
Also, ala_frosty, if you use an inner join, you will only have customers with visits, and hence the condition will always evaluate to true... It would be like saying: select distinct customerNo from visits.
(°v°)
(°v°)
There's a forest around here somewhere .. if I could only see 'round these pesky trees ...