distinct clause in POST QUERY trigger in forms

Hello all,
I tried to use distinct clause in POST_QUERY trigger at block level on a query. I am trying ot retrive only one value for a column which as same values for all records.
To retrive unique one vlaue I used DISTINCT cluase, it gives me error saying "Exact fetch returns more than one row".
select DISTINCT dept_payin_no INTO DEPT_PAY from post_treasury_copy
where to_date(payin_date,'DD-MON-YY') = to_date(payin_date,'DD-MON-YY');

How to fix this. This query works fine at SQL prompt and returns only one value for the the rows.

how do I retrive only one value by using sigle query stmt. I know I can use a cursor but still how can I get only one value from single query statement.

Please let me know ASAP.

Obviously there is more than one distinct dept_payin_no - that is what you are being told with "Exact fetch returns more than one row."

So your "I am trying ot retrive only one value for a column which as same values for all records" won't work - you have more than one value that covers all records. Go into SQL*Plus and do select distinct dept_payin_no and you should see more than one value for dept_payin_no.

If you are expecting only one value, your data is broken. Find out how and why that happened, take steps to prevent it in the future, then work on the trigger. That's one solution. There are others, but with your expectation that only value is to be returned, you have other problems to worry about first.

That query works fine at SQL, and when I say it works fine I mean its brings me only one distinct vlaue for that date I provide.For a given date that column has same vlaue for all records I want to retrive that value for that date.
The query can't be worng as it works fine at SQL. I was thinking if DISTINCT clause is restricted in POST QUERY trigger in Forms. Why won't it work in forms while it works in SQL?

Please let me know ASAP

Use

select dept_payin_no INTO DEPT_PAY from post_treasury_copy
where to_date(payin_date,'DD-MON-YY') = to_date(payin_date,'DD-MON-YY')
where rownum = 1

would work if you are sure you have one and only one value based on that date.

I'm not sure, but which one of the payin_date's is your parameter?

see:

select dept_payin_no INTO DEPT_PAY from post_treasury_copy
where to_date(payin_date,'DD-MON-YY') = to_date(payin_date,'DD-MON-YY')

if you have a column with the same name, then it's obious why Oracle returns more than one row.