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

[DaaaDaaa]

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

[Salad]

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.

[MGFoster]

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

[Rich P]

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!

[david epsom dot com dot au]

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

[DaaaDaaa]

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.