Static Entity in Aggregate - Hard Coding Filter Works, Using Input Parameter Does Not

Static Entity in Aggregate - Hard Coding Filter Works, Using Input Parameter Does Not

  

EDIT:  I dont know why the post has the \ in the times.  I did not type those but something when I clicked submit added them.  Please ignore the \ in the times.....

--------


Hopefully I have a simple question with a simple answer...  I am working on a Server Action.


I have a static entity with a list of times in a day (Entity: HOURSOFDAY).  This Static entity has the following attributes:

-Id

-Label

-Order

-Is_Active

-Time (this one is one I added.  It is of data type "Time")


My records then have the times of day I want in this static entity (i.e. Label = "00:00", TIME = #00\:00\:00#......Label="00\:30", TIME = #00\:30\:00# etc etc)


I create an aggregate, as a source I drop in my static entity HOURSOFDAY.  


I then want to apply a filter showing me all the records between a START time and an END time.  


So my filter looks like:

HOURSOFDAY.TIME >= #09\:00\:00# and

HOURSOFDAY.TIME <= #20\:00\:00#


This works perfectly.  I get the records I want.


But I need to use an input parameter, which has data type TIME, and then use this input parameter in the filter.  

HOURSOFDAY.TIME >= StartTime and

HOURSOFDAY.TIME <= EndTime


Then in the test values portion of the aggregate, I enter #09\:00\:00# for StartTime and I enter #20\:00\:00# as a test value for EndTime..... my aggregate has no results.


Why would the filter work properly when I hard type in the times I want, but when I use the input parameters and then type the exact same times as test values (#09:00:00# and #20\:00\:00#) I get no results?



Hi Rob,

I haven't had the time to try that out in a dev environment but there are some things you can check meanwhile:

  • Does your aggregate return the correct data at runtime?
  • Have you checked what is the Executed SQL of your Aggregate?

Hi Rob,

Can you please create the same scenario in your cloud and send the oml so that I can check it.


Regards

-SK-

I just created a "test" webscreen, using the same input parameters, aggregate, filters etc.


Again, when putting in the times for the test values in the aggregate, no records would display in the aggregate.


But, when I ran the test webscreen, which uses that preparation aggregate and filters and input parameters that returned no results..... the table records output for the webscreen was perfect!


So I'm thinking it is something buggy/wrong with the Test Values in the aggregate?  Test Values work when you hard code the time, test values doesn't work when you assign the time to an input parameter or a variable.  But run the time input parameters and variables in the aggregate as part of your webscreen and output to table records and it works.


I would be happy to provide the oml, but I have not done that yet.  Will get back with the info once I figure out how to do it.

I hope that this will be something that can be opened by those here.  First time doing this.


In this instance, it seems the test values return everything in the static entity, instead of nothing.  But the bottom line is still the same....the test values aren't working.

I wonder if there might be something with the variables of type TIME?


When I run the debugger and look at the input parameters, the times are showing as #1900-01-01 06:00:00# and #1900-01-01 21:00:00# etc.


These appear to be of type DATETIME, is that correct?  I just tried "converting" the input parameters into local variables of type TIME using the function NEWTIME(HOUR(),MINUTE(),SECOND)), then ran the aggregate again using the variables.... still nothing.


I have a feeling there is something here going on with TIME, DATETIME and they're just not talking to each other or something?


The troubleshooting continues...



hi Rob,

I have tried and it is okay.

regards,

Batik

Solution

Rob K wrote:

I wonder if there might be something with the variables of type TIME?


When I run the debugger and look at the input parameters, the times are showing as #1900-01-01 06:00:00# and #1900-01-01 21:00:00# etc.


These appear to be of type DATETIME, is that correct?  I just tried "converting" the input parameters into local variables of type TIME using the function NEWTIME(HOUR(),MINUTE(),SECOND)), then ran the aggregate again using the variables.... still nothing.


I have a feeling there is something here going on with TIME, DATETIME and they're just not talking to each other or something?


The troubleshooting continues...



For some reason, if you use date time literals like #1900-01-01 10:00:00# as test values everything works as expected... seems the previewer is assuming date time regardless of the more specific data type of the input parameter. :-/


Solution

This is exactly what to expect, it's because a "time" variable is just a datetime where date is ignored, just as a "date" variable is just a datetime where the time is ignored.


J.Ja