We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

in mysql -> "case n when x1 then y1 when x2 then y2 end"; in ms access -> ?

ZhaawZ
ZhaawZ asked
on
Medium Priority
660 Views
Last Modified: 2008-02-01
I'm just curious if this can be done (if yes - how) in MS Access by using SQL. 500pts (grade A) in case of positive answer ;]

In MySQL it looks like this:
[sql]
select
  case id
    when 1 then "one"
    when 2 then "two"
    when 3 then "three"
    else id
  end as mod_id
from artists;
[/sql]
The result will be "one", if id=1; "two", if id=2; "three", if id=3; otherwise - same as value of id.

This is very handy when some "manual" sorting is needed:
select * from table order by case val1 when 'some value' then 0 when 'other value' then 1 when 'another val' then 2 else 3 end;

It's also very handy when you have to change a field in several rows, but do not want to loop through the table several times:
update table1 set id = case id when 1 then 101 when 2 then 102 when 3 then 103 end where id in (1, 2, 3);

Now I tried it in MS Access and found that it does not work. It says 'syntax error (missing operator)' and shows that 'case ... end' expression.

So the question is -- is there any construction in MS Access that works the same way as 'case ... end' in MySQL?
Comment
Watch Question

Commented:
IIF() is the closest thing you're going to find, unless you write a custom VBA function I guess.

IIF(Expression, If True, If False)

So:

SELECT IIF(ID = 1, "one", IIF(ID = 2, "two", IIF(ID=3, "three", ID))) AS mod_ID
FROM Artists;

Would the the Jet equivalent of your CASE WHEN THEN ELSE END.

Dave
ZhaawZSoftware Developer

Author

Commented:
hmm, using IIF() in 20+ "levels" (this may vary) wouldn't be very handy...
what do you mean with "Jet equivalent"?

P.S. I need this to update info from VBA (by calling DoCmd.RunSQL or smth like that)
Commented:
Not very handy at all.

How about using a lookup table?

SELECT Nz(t2.Mod_ID, t.ID)
FROM Artists t LEFT JOIN tblLookupTable t2 ON t.ID = t2.ID

Example tblLookupTable

ID  : mod_ID
1   : one
2   : two
3   : three

This will now return the equivalent of your first query.

Dave

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
ZhaawZSoftware Developer

Author

Commented:
hmm, nope... as i said, i actually need this to update info (and that info is not fixed)
ZhaawZSoftware Developer

Author

Commented:
Hmm, if there's no such built-in construction, custom VBA function would be the best sollution (if it can be used in SQL)
ZhaawZSoftware Developer

Author

Commented:
Found it:
select switch(id=1, "one", id=2, "two", id=3, "three", true, id) from artists
misread the question.

Commented:
>according to these links, the select case syntax can be used ???

That's the basis of the Q, how can the mySQL qeuery be used in Access :)

>select switch(id=1, "one", id=2, "two", id=3, "three", true, id) from artists

1. I don't even know if Switch will allow you to pass 400 paramters
2. If it does, it's going to be the slowest thing you'd even writen

Please consider using a lookup table.

Commented:
Well, it is going to allow you to pass all 400.. But it will be SSSSSSLLLLLLLOOOOOOOWWWWWW

What's this update all about?  Example please..

Dave
ZhaawZSoftware Developer

Author

Commented:
Hmm, there would be 15-25 cases usually in my project, but "built-in query builder" does not allow me to pass even 15 values...

This update is for switching values between rows. DB will contain info about artists, their songs and albums etc. One table holds data about albums (id, artist_id, album_id, song_id, number; artist_id is needed for albums that contain songs from several artists, for example, soundtracks of movies). "Number" is 1 for 1st song in album, 2 for 2nd song etc. I need a query that would allow to switch values in this "number" field, i.e., that would allow to change order of songs in album.

"Incoming data" would be, for example, '12, 18, 14, 19, 20' which is sequence of values in 'id' field (I'll get these values from a listbox control on a form). In MySQL it would look like this:
update songs_in_album set number = case id
  when 12 then 1
  when 18 then 2
  when 14 then 3
  when 19 then 4
  when 20 then 5
end where id in (12, 18, 14, 19, 20)

P.S. This is question is not about getting high performance, this is mainly for learning some new things
ZhaawZSoftware Developer

Author

Commented:
What would you say about something like this? How good or bad is this sollution?

Public Function MyCase(ByVal value As Variant, ParamArray values() As Variant) As Variant
Dim n As Byte
For n = 0 To UBound(values) Step 2
  If values(n) = value Then
    MyCase = values(n + 1)
    Exit Function
  End If
Next n
End Function

DoCmd.RunSQL "update test set s = MyCase(id, 1, 'one', 2, 'two', 3, 'three', 4, 'four', 5, 'five') where id in (1, 2, 3, 4, 5)"
ZhaawZSoftware Developer

Author

Commented:
Hmm, even if sollution is good, MS Access is bad :| "Expression too complex in query"...
Try executing sql directly, instead of using Access RunSQL command:

Dim strSQL As String

strSQL  = "update test set s = MyCase(id, 1, 'one', 2, 'two', 3, 'three', 4, 'four', 5, 'five') where id in (1, 2, 3, 4, 5)"

CurrentDb.Execute strSQL










Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
The function you've created is almost your own version of Switch.
But what you have shouldn't be a problem.

You'll be assigning the replacement values on the fly each time I guess?

If test, id and s are all valid then your example should be fine.
ZhaawZSoftware Developer

Author

Commented:
Nop, the same - works fine with up to 14 pairs of values
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
In this scenario is 'test' a query?
I can't see why else you'd get that error - rather than a more specifc one relating to the function or field use.

Is 'test' viewable?
Updateable?
Does it join local and linked tables?
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
(Or indeed - just a linked table without a PK?)
ZhaawZSoftware Developer

Author

Commented:
>> You'll be assigning the replacement values on the fly each time I guess?
Yes

'test' is a name of table. I created that table in design-mode only for testing. It's not linked with any other table.
If I specify 14 pairs of values, it works well, if I specify 15 or more pairs of values - it fails ands says that expression is too complex. Could it be so that there are some limits that do not allow to pass more than 30 values to function (in SQL)?
ZhaawZSoftware Developer

Author

Commented:
My current idea how to solve this is to prepare VBA data-array before calling a query. Then I'll just use:
  "update table_name set field_name = my_func(id) where id in ([list of id's])"
instead of specifying all values as parameters of my_func(). This MUST work...
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Well - since you're making your query calls in code - it makes sense to prepare the replacement values there too - and only accept the passed ID field as parameter.
Yes - I agree.
Leigh PurvisDatabase Developer
CERTIFIED EXPERT
Commented:
(As an aside - Wouldn't be massively dissimilar to passing a single string parameter from the query - which is a comma separated list of your parameter options - something like

DoCmd.RunSQL "update test set s = MyCase(id, "1, one, 2, two, 3, three, 4, four, 5, five") where id in (1, 2, 3, 4, 5)"

Public Function MyCase(ByVal value As Variant, strParamStringArray as string) As Variant
Dim values as variant
Dim n As Byte
strParamStringArray = Replace(strParamStringArray," ","")
values = Split(strParamStringArray,",")
For n = 0 To UBound(values) Step 2
  If values(n) = value Then
    MyCase = values(n + 1)
    Exit Function
  End If
Next n
End Function

But that would just be if this was all being done from the query.
As said before - with your control from VBA - you needn't do this - and prepare your values before executing the SQL.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You are overcomplicating this:

  SELECT
    *,
    Choose(id, "one", "two", "three") AS IdName
  FROM
    tblYourTable;

But I'll vote for having the separate lookup table with the id names.

/gustav
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Seems to me like Choose would be equally succeptable to the parameter limit.
However the lookup table is indeed the best way - as Dave said early on... (but I'm still unclear on the reson that it's not appropriate).
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It may be OK - if you use it as a kind of format: Black/Gray/White, Indoor/Outdoor, Cold/Hot/Very Hot etc.

Here, however, you may go to "six" or "seven" and then you need to redesign every single query. On a large scale it may turn into a nightmare.
Having a lookup table, you need to adjust one thing only.

And when you turn to internationalization - the day you need these labels in French for, say, Canada - you are hosed.

/gustav
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Indeed, the data does need to vary.
To quote an example:

String parameters
12, 1, 18, 2, 14, 3, 19, 4, 20, 5

Table would just be
12    1
18    2
14    3
19    4
20    5

I can't see it being any harder to contruct lookup values in a table.
The values need to be specified anyway, however the conversion is determined (that's not established anywhere so far as far as I can see).

Commented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Split: flavo {http:#16354108} & puppydogbuddy {http:#16354560} & LPurvis {http:#16354736} & cactus_data {http:#16355153}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

jjafferr
EE Cleanup Volunteer
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.