• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

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

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?
0
ZhaawZ
Asked:
ZhaawZ
  • 10
  • 7
  • 4
  • +3
4 Solutions
 
flavoCommented:
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
0
 
ZhaawZSoftware DeveloperAuthor 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)
0
 
flavoCommented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ZhaawZSoftware DeveloperAuthor Commented:
hmm, nope... as i said, i actually need this to update info (and that info is not fixed)
0
 
ZhaawZSoftware DeveloperAuthor 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)
0
 
ZhaawZSoftware DeveloperAuthor Commented:
Found it:
select switch(id=1, "one", id=2, "two", id=3, "three", true, id) from artists
0
 
puppydogbuddyCommented:
0
 
puppydogbuddyCommented:
misread the question.
0
 
flavoCommented:
>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.
0
 
flavoCommented:
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
0
 
ZhaawZSoftware DeveloperAuthor 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
0
 
ZhaawZSoftware DeveloperAuthor 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)"
0
 
ZhaawZSoftware DeveloperAuthor Commented:
Hmm, even if sollution is good, MS Access is bad :| "Expression too complex in query"...
0
 
puppydogbuddyCommented:
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










0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
ZhaawZSoftware DeveloperAuthor Commented:
Nop, the same - works fine with up to 14 pairs of values
0
 
Leigh PurvisDatabase DeveloperCommented:
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?
0
 
Leigh PurvisDatabase DeveloperCommented:
(Or indeed - just a linked table without a PK?)
0
 
ZhaawZSoftware DeveloperAuthor 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)?
0
 
ZhaawZSoftware DeveloperAuthor 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...
0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
Leigh PurvisDatabase DeveloperCommented:
(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.
0
 
Gustav BrockCIOCommented:
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
0
 
Leigh PurvisDatabase DeveloperCommented:
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).
0
 
Gustav BrockCIOCommented:
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
0
 
Leigh PurvisDatabase DeveloperCommented:
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).
0
 
jjafferrCommented:
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 10
  • 7
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now