How to use Switch function in Query [Archive]

View Full Version : How to use Switch function in Query

sushmitha

08-19-2008, 05:56 AM

MSAccessRookie

08-19-2008, 06:34 AM

I have a table with Time column which has values like A, B, C, D until J.
I need to hard code them if Time is A then 8-9 and if time is B then 9-10 etc

I tried both iif and switch functions which are not working

Please advise me on how to write ??
Am I missing any syntax ??

IIf would be clumsy due to the number of switches. To do this in SQL ONLY, a nested SQL like the following gives you something to start from.

SELECT mid(T.StartTime, instr(1, T.StartTime, " ")+1) AS StartTime,
mid(T.EndTime, instr(1, T.EndTime, " ")+1) AS EndTime From
(SELECT cstr(cdate(DateAdd("h", asc(code)-57, Date()))) AS StartTime,
cstr(cdate(DateAdd("h", asc(code)-56, Date()))) AS EndTime
FROM Table15) AS T;

Note: Table15 contains a column called code that stored the values "A" through "J".

sushmitha

08-19-2008, 11:22 AM

IIf would be clumsy due to the number of switches. To do this in SQL ONLY, a nested SQL like the following gives you something to start from.

SELECT mid(T.StartTime, instr(1, T.StartTime, " ")+1) AS StartTime,
mid(T.EndTime, instr(1, T.EndTime, " ")+1) AS EndTime From
(SELECT cstr(cdate(DateAdd("h", asc(code)-57, Date()))) AS StartTime,
cstr(cdate(DateAdd("h", asc(code)-56, Date()))) AS EndTime
FROM Table15) AS T;

Note: Table15 contains a column called code that stored the values "A" through "J".

I am not working on Timestamp field. I have Time field with values A, B, C, D etc. I just need to hard code them to see if Time value is A then display as 8-9 .
I used iif and switch statements which didnot work
Can you tell me how to do this and syntax

MSAccessRookie

08-19-2008, 11:45 AM

I am not working on Timestamp field. I have Time field with values A, B, C, D etc. I just need to hard code them to see if Time value is A then display as 8-9 .
I used iif and switch statements which didnot work
Can you tell me how to do this and syntax

The following code does the same thing with numbers

SELECT mid(T.StartTime,instr(1,T.StartTime," ")+1) AS StartTime,
mid(T.EndTime,instr(1,T.EndTime," ")+1) AS EndTime
FROM (SELECT IIf((asc(code)-57) Mod 12 = 0, 12, (asc(code)-57) Mod 12) AS StartTime,
IIf((asc(code)-56) Mod 12 = 0, 12, (asc(code)-56) Mod 12) AS EndTime
FROM Table15) AS T;

FoFa

08-19-2008, 01:23 PM

Have you considered just creating a table with the two values and joining them.
Would be way easier.

datAdrenaline

08-19-2008, 01:46 PM

SWITCH([FieldName]="A","8-9",[FieldName]="B","9-10",[FieldName]="C","10-11")

{did you know your displayed ranges overlap? ... 8-9, 9-10 ... so if someone gets 9, are they an A or a B?}
....

But a table is a much better/flexible solution, as FoFa suggested ... maybe something like this could be used ...

tblTimeFrames
TimeFrameID {PK - Text(1)} .. Values like A,B,C,D....
TimeFrame {Text(50)} .. Values like 8-9, 9-10, 10-11, ....

Then an SQL to join to tblTimeFrames would look something like this:

SELECT *, tblTimeFrames.TimeFrame
FROM SomeTable
LEFT JOIN tblTimeFrames ON SomeTable.TimeFrameID = tblTimeFrames.TimeFrameID

MSAccessRookie

08-20-2008, 05:12 AM

sushmitha

08-20-2008, 05:32 AM

I assumed that the ranges represented Hourlong slots (like in a schedule), and Block A was 8:00 - 9:00, etc.

Switch function is not working in Business Objects. Can you let me know how to write the same query using iif()

MSAccessRookie

08-20-2008, 05:47 AM

Switch function is not working in Business Objects. Can you let me know how to write the same query using iif()

I think that it can be done using IIf, but it will be very clumsy

IIf(FieldName="A", 8,
IIf(FieldName="B", 9,
IIf(FieldName="C", 10,
IIf(FieldName="D", 11,
IIf(FieldName="E", 12,
IIf(FieldName="F", 13,
IIf(FieldName="G", 14,
IIf(FieldName="H", 15,
IIf(FieldName="I", 16,
IIf(FieldName="J", 17, 0)))))))))) AS StartHour,

IIf(FieldName="A", 9,
IIf(FieldName="B", 10,
IIf(FieldName="C", 11,
IIf(FieldName="D", 12,
IIf(FieldName="E", 13,
IIf(FieldName="F", 14,
IIf(FieldName="G", 15,
IIf(FieldName="H", 16,
IIf(FieldName="I", 17,
IIf(FieldName="J", 18, 0)))))))))) AS EndHour

datAdrenaline

08-20-2008, 06:39 AM

>> Switch function is not working in Business Objects. <<

So ... you must be connecting to a JET datasource through an ADO connection? ... Sorry about that if you are not, you have now discovered that the Switch() function is NOT part of core set of functions available from the JET Expression Service, but as you have guessed, IIf() IS a native function in the JET Expression Service and thus can be used, however, I would encourage the use of the table method and a JOIN ... it would be MUCH easier to maintain.

But if you go with the IIf(), the expression offered by MSAccessRookie *should* work, but I offer you this too, which is a derivative of Rookies' ... Its just half the size ...

IIf(FieldName="A", "8-9",
IIf(FieldName="B", "9-10",
IIf(FieldName="C", "10-11",
IIf(FieldName="D", "11-12",
IIf(FieldName="E", "12-13",
IIf(FieldName="F", "13-14",
IIf(FieldName="G", "14-15",
IIf(FieldName="H", "15-16",
IIf(FieldName="I", "16-17",
IIf(FieldName="J", "17-18", "")))))))))) AS TimeFrame

...Or .. if you don't like the Nested IIf(), which often gets me with trying to balance my parenthesis, you can concatenate a bunch of small, complete IIf()'s ... something like this ...

IIf([FieldName]="A","8-9","") &
IIf([FieldName]="B","9-10","") &
IIf([FieldName]="C","10-11","") &
IIf([FieldName]="D","12-13","") & ... etc ...

{Note - Line breaks added for clarity}

Hope that helps ...

sushmitha

08-20-2008, 06:49 AM

MSAccessRookie

08-20-2008, 07:01 AM

Even iif() is giving me error in Business Objects when running query. Parsing is OK but when running it is giving me too few parameters errors

Do you have any idea about this error ??

My previous post assumed that you wanted to know both of the hours, not the timeframe. I used datAdrenaline's formula for my testing and the code works fine for me (See my code below). I am using Access 2003, what version are you using? Also, can you post your code so that we can look at it and help you to make it work?
SELECT IIf(Code="A", "8-9",
IIf(Code="B", "9-10",
IIf(Code="C", "10-11",
IIf(Code="D", "11-12",
IIf(Code="E", "12-13",
IIf(Code="F", "13-14",
IIf(Code="G", "14-15",
IIf(Code="H", "15-16",
IIf(Code="I", "16-17",
IIf(Code="J", "17-18", "")))))))))) AS TimeFrame
FROM Table15;

datAdrenaline

08-20-2008, 07:37 AM

Sushmitha ...

Can you please show use your SQL statement? ... its hard to debug something we can't see <dazed> ... :) ...

Also, HOW are you executing this query? ... is the query a Query Object in an MDB, then you are trying to execute it in Buisness Objects? ... or are you executing an SQL statement in Buisness Objects (thats part of SAP right?) ...

So ... with Switch() were you getting the Too Few Parameters ... as well? or was it a different error ...

georgedwilkinson

08-20-2008, 09:08 AM

I'm just wondering why this isn't table driven. Forget all the Iif stuff and just put your values in a table.

MSAccessRookie

08-20-2008, 09:16 AM

I'm just wondering why this isn't table driven. Forget all the Iif stuff and just put your values in a table.

That was suggested once before, and reiterated again after that, but there was no response.

Have you considered just creating a table with the two values and joining them.
Would be way easier.

But a table is a much better/flexible solution, as FoFa suggested.