Solved Using Switch with Case - Possible? (1 Viewer)

palmer.rp

New member
Local time
Today, 05:44
Joined
Oct 10, 2019
Messages
6
Hi,
In a single table I am updating the field Route_Class. Pretty straight forward.

UPDATE workingtable SET workingtable.route_class = Switch(
WorkingTable.Stop_ID = '04434MAXXIS', 'MAX-04434',
WorkingTable.Stop_ID = '04434MICHELIN', 'MIS-04434',
WorkingTable.Stop_ID = '04434TOYO TIRE', 'TOY-04434',
WorkingTable.Stop_ID = '04434CMCCLA', 'CMC-WED4434')

In the same table, there is different criterion for the WorkingTable.Stop_ID value '04434CMCCLA',
where IF 'Date_Field' is the Day of week is 1 (Tues) I want to make the route_class 'CMC-TUES4434' else leave it as is. I tried using a Case statement

CASE (WHEN (WorkingTable.Stop_ID = '04434CMCCLA') AND (WEEKDAY, workingtable.ScheduledArrival= 1)) THEN 'CMC-TUES4434' ELSE 'CMC-WED4434' END

Any help is appreciated.
RP
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:44
Joined
Oct 29, 2018
Messages
21,358
Hi. CASE is TSQL. You could try using an IIf() expression instead. Assuming this is in Access.
 

plog

Banishment Pending
Local time
Today, 04:44
Joined
May 11, 2011
Messages
11,613
In the same table, there is different criterion for the WorkingTable.Stop_ID value '04434CMCCLA',
where IF 'Date_Field' is the Day of week is 1 (Tues) ...

With that I think you need to make a function in VBA and call it from the SQL:

Code:
UPDATE workingtable SET workingtable.route_class = get_RouteClass(WorkingTable.Stop_ID)

So get_RouteClass() contains all your logic and values and it determines what value gets used per your algorithm.
 

June7

AWF VIP
Local time
Today, 01:44
Joined
Mar 9, 2014
Messages
5,425
Expression is getting rather long but still doable without function. There are only the 5 conditions to test?

UPDATE workingtable SET workingtable.route_class = Switch(
WorkingTable.Stop_ID = '04434MAXXIS', 'MAX-04434',
WorkingTable.Stop_ID = '04434MICHELIN', 'MIS-04434',
WorkingTable.Stop_ID = '04434TOYO TIRE', 'TOY-04434',
WorkingTable.Stop_ID = '04434CMCCLA' AND WEEKDAY([ScheduledArrival])= 1, 'CMC-TUE4434',
WorkingTable.Stop_ID = '04434CMCCLA' AND WEEKDAY([ScheduledArrival])<> 1, 'CMC-WED4434')
 

ebs17

Well-known member
Local time
Today, 10:44
Joined
Feb 7, 2020
Messages
1,883
Case cascades are mostly dispensable. Queries can handle tables very well, table contents are easier to maintain than codes.
So you can create an additional table and store the value pairs there.
SQL:
UPDATE workingtable INNER JOIN replacementtable ON workingtable.Stop_ID = replacementtable.An_ID
SET workingtable.route_class = replacementtable.ThisValue

Eberhard
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:44
Joined
Feb 19, 2013
Messages
16,553
WorkingTable.Stop_ID value '04434CMCCLA'

if there is consistence with your code - always 5 chars, then 3 chars then some more after that. And if the 'trigger' is 'CMC' then you can simplify your switch statement to an iif statement

mid(WorkingTable.Stop_ID, 6,3) & "-" & iif(mid(WorkingTable.Stop_ID, 6,3)="CMC", left(weekday(ScheduledArrival ),3) & val(left((WorkingTable.Stop_ID,5)),left((WorkingTable.Stop_ID,5))

uhh - just realised Michelin relates to MIS, so would be more complex coding
 

palmer.rp

New member
Local time
Today, 05:44
Joined
Oct 10, 2019
Messages
6
Expression is getting rather long but still doable without function. There are only the 5 conditions to test?

UPDATE workingtable SET workingtable.route_class = Switch(
WorkingTable.Stop_ID = '04434MAXXIS', 'MAX-04434',
WorkingTable.Stop_ID = '04434MICHELIN', 'MIS-04434',
WorkingTable.Stop_ID = '04434TOYO TIRE', 'TOY-04434',
WorkingTable.Stop_ID = '04434CMCCLA' AND WEEKDAY([ScheduledArrival])= 1, 'CMC-TUE4434',
WorkingTable.Stop_ID = '04434CMCCLA' AND WEEKDAY([ScheduledArrival])<> 1, 'CMC-WED4434')

Thanks @June7 . This works. I inherited this dB; Sun = 1, and Sat = 7, so using '3' for Tuesday.
Thanks again
 

Users who are viewing this thread

Top Bottom