hi,

i am facing a problem, in filtering by date on html painter.

i have a report, where i have a field called TRANSACTION_DATE, whose format is HYYMDS, and i have shown it as HMTDkYYS (June 22, 2010 10:58:43).

Now, in my html painter, i want to put filters as from date and to date, and on filtering, only those records should be shown whose transaction date is GE from date and LE to date. So, for this i added where/If clause in my report "( SYS_CREATION_DATE GE DT(&FROM_TRANS_DATE) ) AND ( SYS_CREATION_DATE LE DT(&TO_TRANS_DATE) )".

Also, in my panter filter parameters, i have put data source date format as "January 15, 2010".

But when i run it, and try to filter it on date, it does nothing.

i am quite new to WebFOCUS.
Can anybody of you help me in this?

Thanks,
Neelam.

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

What is the source, is it relational ?

If so what SQL is generated ?

i have done it with SQL PASSTHROUGH.

And, the sql I gave is:
"SELECT TO_CHAR(PI.ID) AS TRANS_ID1, PI.*, (PI.GROSS_AMOUNT - PI.NET_AMOUNT) AS VAT_AMT, DP.ID AS DP_ID, DP.COMPANY_NAME, DP.BUDGET_ID, DP.CURRENCY_ID, DSP.TELEPHONE, DSP.VAT_NUM, DSL.COUNTRY_ID, DSL.STATE_ID, DSL.CITY_ID,
DSL.ADDRESS_LINE1, DSL.ZIP_CODE, CT.VALUE AS CITY_NAME , S.VALUE AS STATE_NAME, CTY.VALUE AS COUNTRY_NAME , DPT.PAYMENT_TYPE AS PAY_TYPE_VAL , NVL(PM.ID,0) AS MSG_ID, PM.MESSAGE,
PM.STATUS_ID AS MSG_STATUS_VAL , PM.SYS_CREATION_DATE, PAYMENT_STATUS.NAME AS PI_STATUS , NVL(MSG_STATUS.NAME,PAYMENT_STATUS.NAME) AS PM_STATUS, B.CURRENT_USAGE
FROM PAYMENT_INFO PI, DA_PAYMENT_TYPE DPT, PAYMENT_MESSAGES PM, STATUSES PAYMENT_STATUS, STATUSES MSG_STATUS, BUDGET B,
DA_PARTNER DP, DA_SIM_PARTNER DSP, DA_SIM_LOCATION DSL, CITY CT, STATE S, COUNTRY CTY
WHERE PI.PAYMENT_TYPE = DPT.ID AND PI.ID = PM.PAYMENT_INFO_ID(+) AND PI.PARTNER_ID = DP.ID AND DP.EXTERNAL_ID = DSP.ID AND DSP.LOCATION_ID = DSL.ID
AND DSL.CITY_ID = CT.ID AND DSL.STATE_ID = S.ID(+) AND DSL.COUNTRY_ID = CTY.ID AND DP.BUDGET_ID = B.ID
AND PI.STATUS = PAYMENT_STATUS.ID AND PM.STATUS_ID = MSG_STATUS.ID(+)
END".

I don't see anything about transaction_date in your code, but to answer your question I would do
AND TO_DATE(TRANSACTION_DATE,'YYYYMMDD') => TO_DATE(&INPUT_DATE,'YYYYMMDD')

One other thing I would suggest is to start each field on a new line (everywhere you have a comma or an AND). It will make your code much easier to read and much easier to de-bug later.

hi
Thank you all for your replies.

the problem got solved.

it was because, the format of the date filter. when i put YYMD, it stared working.

Also, i noticed one thing.

if that report has filters and when we reference/import that report in html painter, it asks for filter formats and controls. I used to select Calendar there. but instead, if we keep calendar control separately in filter element, it works.

i dont know the logic, but it worked finally.

thanks.