set user selection system function

[swathi]

HI All

I need to give Set User Selection in my report to print records between 11:00PM of the previous day to 11:00AM in the next day.So I got two variables with data type TMLS for time and two variables with datatype LOAD for date but if I write the logic in the following way it is not givig the desired output:

set user selection BC DATE is greater than or equal to PREVIOUS DATE and
set user selection BC TMLS is greater than or equal to PREVIOUS TIME and
set user selection BC DATE is less than or equal to PRESENT DATE and
set user selection BC TIME is less than or equal to PRESENT TIME none

Please help me!!!
Swati.

<font color="black"> </font>

 

[JMR]

Swati,

The TIME parameters are causing incorrect results. Try the following generic logic for a given date/time range:

BC DATE > FIRSTDAY <and>
BC DATE < LASTDAY <and>
BC DATE = FIRSTDAY <or>
BC TIME >= FIRSTDAYTIME <and>
BC DATE = LASTDAY <or>
BC TIME <= LASTDAYTIME

Which reads like:

(BC DATE > FIRSTDAY <and> BC DATE < LASTDAY)
<or> (BC DATE = FIRSTDAY <and> BC TIME >= FIRSTDAYTIME)
<or> (BC DATE = LASTDAY <and> BC TIME <= LASTDAYTIME)

 

[Remo Williams]

swati, instead of and/and/and/none, try none/and/and/and. remember that those "and" and "or" operators at the end of a "set user selection" statement apply to the current and the previous statement(s). NOT the current and the next.

 

[peterbruce]

Swati

There are a couple of things to remember with the Set User Selection function. First, <none> and <and> both result in <and> in the SQL statement. Second, the <none>/<and>/<or> that is the last parameter of the Set User Selection function should be read to be at the beginning. For example:

Set User Selection(BC DATE, <equal to>, PRESENT DATE, <or>)

is the same as:

Or BC DATE equal to PRESENT DATE

Try these Set User Selection functions to resolve your problem:

Set User Selection(BC DATE, <equal to>, PREVIOUS DATE, <none>)
Set User Selection(BC TMLS, <greater than or equal to>, PREVIOUS TIME, <and>)
Set User Selection(BC DATE, <equal to>, PRESENT DATE, <or>)
Set User Selection(BC TIME, <less than or equal to>, PRESENT TIME, <and>)

This will result in a SQL WHERE clause like this

WHERE ...
AND (BC DATE = PREVIOUS DATE
AND BC TMLS >= PREVIOUS TIME)
OR (BC DATE = PRESENT DATE
AND BC TIME<= PRESENT TIME)

For more information see post 68587 and it's attachment.

 

[kiran4hari]

HI,


I expect this should work, change the last None to AND

<font class="small">Code:</font><hr /><pre>
set user selection BC DATE is greater than or equal to PREVIOUS DATE and
set user selection BC TMLS is greater than or equal to PREVIOUS TIME and
set user selection BC DATE is less than or equal to PRESENT DATE and
set user selection BC TIME is less than or equal to PRESENT TIME and </pre><hr />

Regards,
kiran

 

[JMR]

kiran,

What if PREVIOUSTIME = 13:00:00 (1 PM)
and PRESENTTIME = 11:00:00 (11 AM)? you will get no records

Problem is you need all records from:
PREVIOUSTIME to Midnight on PREVIOUSDAY +
Midnight to PRESENTTIME on PRESENTDAY

With your logic, you will only get records where the time values overlap.

 

[Gov]

Hi Swati,

For me it just confuses your requirement. As you said you are storing time in 2 fields and date in one field. If you woule like to look for the records b/n date and time, then I guess we should look only 2 fields(One for Date and One for Time). Can you please put your requirement in more words that could help us to understand to write the select statement.

 

[bravin]

HI

u will try this selection

set user selection BC DATE is greater than or equal to
PREVIOUS DATE and
set user selection BC TMLS is greater than or equal to
PREVIOUS TIME or
set user selection BC DATE is less than or equal to
PRESENT DATE and
set user selection BC TIME is less than or equal to
PRESENT TIME none

Please help me!!!
Swati.

This post contains the poll as shown below. To
vote, please visit the JDEList Forums.
set user selection system function
You may choose only one [input] set user selection

Votes accepted from 9/1/2006 00:00 AM to No end
specified
View the results of this poll