Switch Statement Not Accepting <,>,or = Operators

[zgadson]

I'm clearly doing something wrong and would love some guidance. I know enough SQL to dig myself into a deep hole, but not enough to get out of it, as I'm unfortunately very limited by our highly secured data which does not allow for any SQL statements outside of SELECT.

In short, I'm creating a productivity report for various warehouse shifts, and shifts that split days are proving complicated. This report basically totals up how many pallets shipped per hour with a 24 hour period starting at 5AM and ending at 4:59AM the following day. Basically I need the data from the screenshot here to be in a single line, so my thought was to simply change the date of the data to the next day for data recorded between the hours of midnight (hour 0) and 5AM.

In this example Aaron didn't do anything in the midnight hour at all, but had 464 cases picked in the hour of 1AM, so (red arrow), that 1AM data needs to be bumped to the previous date. Thought it would be simple, but with my database limitations from a locked down ODBC/AS400/IBM Client I'm not having fun. And to make matters worse, my DATE field refuses to accept any type of DateAdd functions - I'm thinking it might not be a legit field somehow but it's all I got.

SELECT

MAX(HOUR),

Switch(
MAX(HOUR) < 5, MAX(DATE) -1 DAY,
false, MAX(DATE)
) AS NEW_DATE,

MAX(LTRIM(RTRIM(HMBLDG))) AS BLDG,

MAX(LTRIM(RTRIM(HMUSER))) AS USER_ID,

MAX(LTRIM(RTRIM(USDESC))) AS USER_NAME,

MAX(CASE WHEN HMTTYP = 'AW' THEN 'Putaway'
WHEN HMTTYP = 'FM' THEN 'Floor Move'
WHEN HMTTYP = 'IS' THEN 'Staging'
WHEN HMTTYP = 'LO' THEN 'Loading'
WHEN HMTTYP = 'PK' THEN 'Picking' ELSE LTRIM(RTRIM(HMTTYP)) END) AS MOVE_TYPE,

SUM(CASE WHEN HMPQTY = 0 THEN 1 ELSE LTRIM(RTRIM(HMPQTY)) END) AS PLT

FROM LOREPORTH

GROUP BY  DATE, HOUR, HMBLDG, HMUSER, USDESC, HMPQTY

ORDER BY DATE, HOUR, HMBLDG ASC

Any guidance, ideas, suggestions, etc. would be immensely appreciated.

 

[jackd]

I am not familiar with /AS400/IBM Client. Can you describe your set up in terms of where Access fits?

 

[xenou]

Every flavor of sql has its own syntax (and in some cases peculiarities). So really I also think that is important information that needs to be clarified (what database system are you working with?) To be honest, I've never seen switch used outside of MSAccess anyway -- that's somewhat suspicious at first glance.

 

[zgadson]

AS400 really isn't all that relevant for this particular question, as this is intended for Access. I just meant to say that because my data comes from AS400 that I'm limited with how I can manipulate it in an SQL query. All I need is a working SWITCH statement.

 

[zgadson]

Also tried it as an If statement and it still doesn't like greater than or less than symbols.

Iif(MAX(HOUR) < 5, MAX(DATE) -1 DAY, MAX(DATE)) AS NEW_DATE,

This results in an error stating "Token < was not valid".

 

[xenou]

So ... when you say it is intended for Access, do you mean it is actually an access querying running in an Access database with data stored in an Access table? Or is that only what you intend to do at some point in the future?

As far as dates in access causing problems, you will have to confirm that the dates are stored in a real DateTime field and not stored as Date-like strings in a Text field.

Also, this just looks like an invalid expression: MAX(DATE) -1 DAY

Also Max(Date) is just the same as date when you are talking about a single row operation so I'm not even sure this is on the right track at all.

 

[xenou]

It might be more helpful to show the data as it exists in the table rather than as it shows up in a report ... hopefully your table doesn't have 24 fields in it one for each hour but that is of course entirely possible! Then your sql really gets fun!

 

[jackd]

Further to xenou's comments, your SQL is not valid Access SQL. ACCESS SQL does not have a CASE statement.
Also, Date, Day, Hour are reserved words in Access.
Suggest you provide some details with respect to your database/application.

 

[xenou]

Also since it may not be immediately obvious (and AS400 was mentioned) you sometimes find that when you connect to another type of database (back end) with MS Access (as a front end) - then the queries sometimes have to be written in the back dialect of sql (or in a neutral ODBC-compliant dialect). It gets a little confusing fast - but generally, Access SQL when you are working solely and completely 100% in Access and Access alone is in fact probably not going to work directly as is in other databases, and vice-versa - so it very important to know the setup (that is, if there is indeed some other backend database connection involved).

More specifically, the sql you wrote looks a little like half Access sql and half SQL Server sql - so probably not going to work on either one. CASE, for instance, is used by SQL Server (and some others), where as switch and Iif is somewhat unique to MSAccess.

 

[zgadson]

Fixed it! And it was way easier than I thought! Rather than changing the date, I used a subquery in the WHERE clause to select the data from the next record for data collected after midnight and before 5AM. And @xenou, that has been my exact problem since I started this project.

WHERE (((Table_All_Data.HMQTY) In (SELECT HMQTY - 1 FROM Table_All_Data WHERE HOUR < 5) AND (Table_All_Data.HMQTY) In (SELECT HMQTY FROM Table_All_Data WHERE HOUR >= 5))

Hasn't been an easy few weeks, but I'm so thankful to have all your great minds anxious to help others simply because you can