I set up define:
DEFINE FILE WKFLOW_SUM_FACT
TransactionDate/MDYY=HDATE( WKFLOW_SUM_FACT.WKFLOW_SUM_FACT.DT_KEY ,'YYMD' );
TRAN_YEAR/YY=TransactionDate;
TRAN_MONTH/Mtr=TransactionDate;
TRAN_QTR/YYQ=TransactionDate;
CURDATE/HMDYY=HGETC(10,'HMDYY');
END

Then I set up where/if:
WHERE ( WKFLOW_SUM_FACT.WKFLOW_SUM_FACT.TransactionDate EQ CURDATE )

and get this error:
0 ERROR AT OR NEAR LINE 67 IN PROCEDURE myprocedure
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

If I look at both I see them coming in as m/d/yy.

This message has been edited. Last edited by: Kerry,

Brenda,
either use a Smart Date to a Smart Date comparison or use Alpha to Alpha comparisons. Within any report you have amper variables available to you for this type of comparison:

 
 &DATE         = 10/05/11
 &DMY          = 051011
 &DMYY         = 05102011
 &MDY          = 100511
 &MDYY         = 10052011
 &YMD          = 111005
 &YYMD         = 20111005
 

any of these can be used for a smart date to smart date comparison or you could convert them to Alpha and compare. Hope this helps.

ERINP

This message has been edited. Last edited by: ERINP,

Assuming tha TransactionDate is a date-time field (e.g. HYYMDs), why not:

...
WHERE WKFLOW_SUM_FACT.WKFLOW_SUM_FACT.TransactionDate EQ DT(&YYMD 00:00:00)
...

You can then get rid of all those DEFINE'd fields

Of course, it would be easier to help if you told us what ACTUAL and USAGE values are there for TransactionDate in your master file.

for report I only want to select records where the transactions date in my master file is = to the current date.
The format in my master file is: HYYMDS (2011/02/14 00:00:00.000)

Is that what you were asking?

Exactly! Didn't this work for you?

WHERE WKFLOW_SUM_FACT.WKFLOW_SUM_FACT.TransactionDate EQ DT(&YYMD 00:00:00)

That should retrieve the records with TransactionDate equals to the current date, which in WebFOCUS can be obtained by reading &YYMD.

The DT() function is there to convert current date (which would give you 20111005 for instance) to a date-time value that can be compared to your database field.

this is what i get:
0 ERROR AT OR NEAR LINE 45 IN PROCEDURE dexflow_ca_rater_w
(FOC006) THE FORMAT OF THE TEST VALUE IS INCONSISTENT WITH FIELD FORMAT:
20111005 00:00:00
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Brenda,
I assume you are only concerned with the date portion of the time stamp?? Otherwise are you ever going to have a transaction that exactly matches the current date and time to the millisecond?
I would HCNVRT the timestamp to get the date portion as an Alpha field and convert it to a smart date:

 
-* In the DEFINE
-* timestamp to alpha
ALPHA_DATE_TIME1/A20 = HCNVRT(Transactiondate, '(H17)', 17, 'A20');
-*strip out the date portion
ALPHA_DT/A8 = EDIT(ALPHA_DATE_TIME1, '99999999$$$$$$$$$$$$'); 
-*convert to a date field
TRANS_DT/YYMD = DATECVT(ALPHA_DT, 'A8YYMD', 'MDYY'); 
-*make todays date EQ to &YYMD
TODAYS_DATE/YYMD = '&YYMD'; 
-*Then compare TRANS_DT to TODAYS_DATE for your WHERE statement
 

Okay ... what happens if you simply say:

WHERE WKFLOW_SUM_FACT.WKFLOW_SUM_FACT.TransactionDate EQ DT(&YYMD)

ERINP, though your approach may work it might cause WebFOCUS to retrieve all of the data to then be filtered internally, as opposed to having the database apply the filters directly (and therefore taking advantage of any available table partitions, indices, etc).

Of course, if the volume of data is not significant this may not be an issue at all but I always try to make the database work harder

Bingo

Brenda,
was njsden solution functional? if it is I will start using that for ALL of my timestamp comparisons. A lot shorter and more direct approach.


ERINP

And more efficient too.

You can enable traces to see what SQL statement WebFOCUS generates to query the data.

In most cases, you will find that filters on date fields based on DEFINES are *not* sent to SQL so there will be a performance hit.

Using DT( <constant> ) on the other hand, allows iWay to translate this into a proper WHERE statement in SQL and now the database (and WebFOCUS) are going to query and fetch only the intended records.

ERINP, yes, it is a very simple approach and functional.

I learn something new just about every day on this site. I will have to give this a try to see if I notice any performance differences in some of my reports. I am surprised that the comparison between a timestamp and a date field does not throw any errors.

Thanks,
ERINP

quote:

I am surprised that the comparison between a timestamp and a date field does not throw any errors

Such a comparison would always fail but this is not the case as Brenda is comparing a date-time field [ TransactionDate/HYYMDS ] to a date-time constant [ DT(&YYMD) ].

That's the magic of DT().

njsden,
thanks for the insight. dually noted and book marked for future use

You're welcome! I just found some interesting stuff related to this matter here.

I'm getting the D.T.'s just thinking about this!

I had not heard of "delirium tremens" in such a loooong time

Boy, that was refreshing!