Solved Using Switch with Case - Possible?

[palmer.rp]

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]

Hi. CASE is TSQL. You could try using an IIf() expression instead. Assuming this is in Access.

 

[plog]

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:

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]

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]

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.

UPDATE workingtable INNER JOIN replacementtable ON workingtable.Stop_ID = replacementtable.An_ID
SET workingtable.route_class = replacementtable.ThisValue

Eberhard

 

[CJ_London]

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]

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