How to use Switch function in Query

sushmitha · Aug 19, 2008

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 ??

MSAccessRookie · Aug 19, 2008

sushmitha said:
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.

Code:

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

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

sushmitha · Aug 19, 2008

Don't need Timestamp

MSAccessRookie said:
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.

Code:

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

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 · Aug 19, 2008

sushmitha said:
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

Code:

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 · Aug 19, 2008

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

datAdrenaline · Aug 19, 2008

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:

Code:

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

MSAccessRookie · Aug 20, 2008

datAdrenaline said:
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:

Code:

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

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

sushmitha · Aug 20, 2008

MSAccessRookie said:
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 · Aug 20, 2008

sushmitha said:
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

Code:

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 · Aug 20, 2008

>> 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 ...

Code:

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 ...

Code:

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 · Aug 20, 2008

datAdrenaline said:
>> 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 ...

Code:

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 ...

Code:

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 ...

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 ??

MSAccessRookie · Aug 20, 2008

sushmitha said:
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?

Code:

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 · Aug 20, 2008

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 · Aug 20, 2008

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

MSAccessRookie · Aug 20, 2008

georgedwilkinson said:
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.

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

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

How to use Switch function in Query

sushmitha

Registered User.

MSAccessRookie

AWF VIP

sushmitha

Registered User.

MSAccessRookie

AWF VIP

FoFa

Registered User.

datAdrenaline

AWF VIP

MSAccessRookie

AWF VIP

sushmitha

Registered User.

MSAccessRookie

AWF VIP

datAdrenaline

AWF VIP

sushmitha

Registered User.

MSAccessRookie

AWF VIP

datAdrenaline

AWF VIP

georgedwilkinson

AWF VIP

MSAccessRookie

AWF VIP

Similar threads

Users who are viewing this thread