Key Component - Choose a Group Field?

questions faq
1

Hey Folks,

Looking to add in the SQL Date/Time fields into Keys (as they are stated in the OVER’d Group), but can’t select them when choosing components.

The docs say that a DIM field can’t be used, but nothing stated about a group field.

Any ideas?

2

Just to be clear. In a record such as:

SqlDateTimeStr STRING(8)
SqlDateTimeGrp GROUP,OVER(SqlDateTimeStr)
SqlDate DATE
SqlTIme TIME
END

Do you want to include SqlDateTimeStr in the key?

3

Working with the standard templates can sometimes provide a problem, especially range limiting on a browse. The GROUP construct is a Clarion tool that delivers something like DATEPART() or TIMEPART() but the actual stored data on the server is still a single STRING, so you cannot easily break those components into a Key that can be saved on the server. On the server side you could have a separate DATE and a TIME field, that would then easily allow components to be in the key.

I did once added another DATE field on the server, filled at runtime as an exact copy of the DATE in the group, that gave me a template range limit on a key easily but that is a dirty fix since we now have duplicate data. A better alternative that I have done since is to manually control the filter in code at the “ValidateRecord” embed. e.g. do your test on the component and set the [BRW1:RecordStatus] = RecordFiltered. [You need to substitute the correct template generated name with Record:Status]

4

Nope, I want to include the “SqlDate” DATE field in the key, and the TIME one as well if required.

5

Right yeah, I guess I was hoping clarion would let me create keys that it understands even if SQL wouldn’t (ie using the DATE and TIME fields in the group).

6

For sorting, using SqlDateTimeStr in a Clarion Dct defined key should not be a problem. If you want SqlDate in the key for range purposes, I believe you can essentially achieve the same results using a filter defined as:
SqlDateTime>=QueryBeginDate AND SqlDateTime<(QueryEndDate+1).

1 Like
7

Since you’re working with SQL don’t forget you can make use of SQL functions to make your SQL faster and easier to understand.

Why not try:

‘cast(sqldatetime as date) between’

This can make for very fast queries and gets around the time portion of the datetime variable.