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?
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?
ASKER
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hmm, nope... as i said, i actually need this to update info (and that info is not fixed)
ASKER
Hmm, if there's no such built-in construction, custom VBA function would be the best sollution (if it can be used in SQL)
ASKER
Found it:
select switch(id=1, "one", id=2, "two", id=3, "three", true, id) from artists
select switch(id=1, "one", id=2, "two", id=3, "three", true, id) from artists
Dave:
according to these links, the select case syntax can be used ???
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
http://mysqld.active-venture.com/Control_flow_functions.html
according to these links, the select case syntax can be used ???
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
http://mysqld.active-venture.com/Control_flow_functions.html
misread the question.
>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.
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.
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
What's this update all about? Example please..
Dave
ASKER
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
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
ASKER
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)"
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)"
ASKER
Hmm, even if sollution is good, MS Access is bad :| "Expression too complex in query"...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Nop, the same - works fine with up to 14 pairs of values
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?
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?
(Or indeed - just a linked table without a PK?)
ASKER
>> 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)?
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)?
ASKER
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...
"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...
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.
Yes - I agree.
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.
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).
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).
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
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
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).
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).
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
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
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