By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,239 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

SQL's equivalent of CASE WHEN ... END CASE

P: n/a
Hi,

Here's what I'd like to do: table (Orders) has fields of Processed,
Paid (both of boolean yes|no) etc, I want to return a count of of
Total number of Orders, Number of Processed vs. Number of unProcessed
(processed value = 0), and Number of Paid vs. Number of unPaid (paid
value = 0), I can easily write a query for SQL Server with Case when
.... end case statement,
I just learned that Access (don't know above which version it
supports) has something similar SELECT CASE ... END CASE, but the code
like this won't do the job,

Select count(*) as OrderTotal,
Select Case(processed)
Case = 1 select processed as pYES
Case = 0 select processed as pNO
End Select
,
count(pYes) as processedYES, count(pNO) as processedNO
From Orders o, OrderDetail od
Where o.orderID = od.orderID
....

A variant like this

Select count(*) as OrderTotal,
Case(processed)
Case = 1 select processed as pYES
Case = 0 select processed as pNO
End Case
,
count(pYes) as processedYES, count(pNO) as processedNO
From Orders o, OrderDetail od
Where o.orderID = od.orderID
....

won't work neither. So, how exactly does SELECT CASE ... END SELECT
work in Access?

TIA
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
DaaaDaaa wrote:
Hi,

Here's what I'd like to do: table (Orders) has fields of Processed,
Paid (both of boolean yes|no) etc, I want to return a count of of
Total number of Orders, Number of Processed vs. Number of unProcessed
(processed value = 0), and Number of Paid vs. Number of unPaid (paid
value = 0), I can easily write a query for SQL Server with Case when
... end case statement,
I just learned that Access (don't know above which version it
supports) has something similar SELECT CASE ... END CASE, but the code
like this won't do the job,

Select count(*) as OrderTotal,
Select Case(processed)
Case = 1 select processed as pYES
Case = 0 select processed as pNO
End Select
,
count(pYes) as processedYES, count(pNO) as processedNO
From Orders o, OrderDetail od
Where o.orderID = od.orderID
...

A variant like this

Select count(*) as OrderTotal,
Case(processed)
Case = 1 select processed as pYES
Case = 0 select processed as pNO
End Case
,
count(pYes) as processedYES, count(pNO) as processedNO
From Orders o, OrderDetail od
Where o.orderID = od.orderID
...

won't work neither. So, how exactly does SELECT CASE ... END SELECT
work in Access?

TIA


I might do something like:
IIf([BooleanField],1,0) As YesItIs
IIf(Not [BooleanField],1,0) As NoItsNot

You could also do
Abs(True) As YesItIs
Abs(Not False) As NoItsNot

Count isn't the best in this situation. I use Sum.

Nov 12 '05 #2

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could use the IIf() & COUNT() functions like this:

SELECT
COUNT(IIf(processed=1,1) As Processed,
COUNT(IIf(processed=0,1) As Not_Processed
FROM ...

Count() doesn't count NULL values. Using the syntax IIf(expression,true
evaluation) will return NULL when the expression evaluates to false.
Therefore, the surrounding Count() will only count when the expression
evaluates to true.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJF0qYechKqOuFEgEQJ0dgCfZhPTPp4UYxtq77afq1zmh/kKYkIAnjYq
l9h1e+dMYwnh/BjCV47SbqLp
=ybru
-----END PGP SIGNATURE-----
DaaaDaaa wrote:
Hi,

Here's what I'd like to do: table (Orders) has fields of Processed,
Paid (both of boolean yes|no) etc, I want to return a count of of
Total number of Orders, Number of Processed vs. Number of unProcessed
(processed value = 0), and Number of Paid vs. Number of unPaid (paid
value = 0), I can easily write a query for SQL Server with Case when
... end case statement,
I just learned that Access (don't know above which version it
supports) has something similar SELECT CASE ... END CASE, but the code
like this won't do the job,

Select count(*) as OrderTotal,
Select Case(processed)
Case = 1 select processed as pYES
Case = 0 select processed as pNO
End Select
,
count(pYes) as processedYES, count(pNO) as processedNO
From Orders o, OrderDetail od
Where o.orderID = od.orderID
...

A variant like this

Select count(*) as OrderTotal,
Case(processed)
Case = 1 select processed as pYES
Case = 0 select processed as pNO
End Case
,
count(pYes) as processedYES, count(pNO) as processedNO
From Orders o, OrderDetail od
Where o.orderID = od.orderID
...

won't work neither. So, how exactly does SELECT CASE ... END SELECT
work in Access?

TIA


Nov 12 '05 #3

P: n/a
Goto Access Help and look up the IIF function (Immediate If).

Select IIF(Processed = 0, Processed, somethingelse) As Processed from
tbl1

You can also nest the IIF statements. Jet sql doesn't have as much
functionality as Transact Sql. But the workaround for the limitations
are to use either Jet Sql Functions (like IIF) or use VBA to make up
UDF's that you can embed in your Jet Sql Statements (UDF's in VBA way
easier than in Sql Server).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

P: n/a
For multiple cases, the functional equivalent are the
SWITCH and CHOOSE functions.

select SWITCH(0,[p0],1,[p1],2,[p2],8,[pEight]) as processed

(david)

"DaaaDaaa" <da**@rock.com> wrote in message
news:21*************************@posting.google.co m...
Hi,

Here's what I'd like to do: table (Orders) has fields of Processed,
Paid (both of boolean yes|no) etc, I want to return a count of of
Total number of Orders, Number of Processed vs. Number of unProcessed
(processed value = 0), and Number of Paid vs. Number of unPaid (paid
value = 0), I can easily write a query for SQL Server with Case when
... end case statement,
I just learned that Access (don't know above which version it
supports) has something similar SELECT CASE ... END CASE, but the code
like this won't do the job,

Select count(*) as OrderTotal,
Select Case(processed)
Case = 1 select processed as pYES
Case = 0 select processed as pNO
End Select
,
count(pYes) as processedYES, count(pNO) as processedNO
From Orders o, OrderDetail od
Where o.orderID = od.orderID
...

A variant like this

Select count(*) as OrderTotal,
Case(processed)
Case = 1 select processed as pYES
Case = 0 select processed as pNO
End Case
,
count(pYes) as processedYES, count(pNO) as processedNO
From Orders o, OrderDetail od
Where o.orderID = od.orderID
...

won't work neither. So, how exactly does SELECT CASE ... END SELECT
work in Access?

TIA

Nov 12 '05 #5

P: n/a
Salad, it's delicious :) = it works, and everyone else, thanks.

Salad <oi*@vinegar.com> wrote in message news:<HW******************@newsread1.news.pas.eart hlink.net>...
DaaaDaaa wrote:
Hi,

Here's what I'd like to do: table (Orders) has fields of Processed,
Paid (both of boolean yes|no) etc, I want to return a count of of
Total number of Orders, Number of Processed vs. Number of unProcessed
(processed value = 0), and Number of Paid vs. Number of unPaid (paid
value = 0), I can easily write a query for SQL Server with Case when
... end case statement,
I just learned that Access (don't know above which version it
supports) has something similar SELECT CASE ... END CASE, but the code
like this won't do the job,

Select count(*) as OrderTotal,
Select Case(processed)
Case = 1 select processed as pYES
Case = 0 select processed as pNO
End Select
,
count(pYes) as processedYES, count(pNO) as processedNO
From Orders o, OrderDetail od
Where o.orderID = od.orderID
...

A variant like this

Select count(*) as OrderTotal,
Case(processed)
Case = 1 select processed as pYES
Case = 0 select processed as pNO
End Case
,
count(pYes) as processedYES, count(pNO) as processedNO
From Orders o, OrderDetail od
Where o.orderID = od.orderID
...

won't work neither. So, how exactly does SELECT CASE ... END SELECT
work in Access?

TIA


I might do something like:
IIf([BooleanField],1,0) As YesItIs
IIf(Not [BooleanField],1,0) As NoItsNot

You could also do
Abs(True) As YesItIs
Abs(Not False) As NoItsNot

Count isn't the best in this situation. I use Sum.

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.