DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: distinct clause in POST QUERY trigger in forms

  1. #1
    Join Date
    Sep 2002
    Posts
    20
    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.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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.

  3. #3
    Join Date
    Sep 2002
    Posts
    20
    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

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    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.

  5. #5
    Join Date
    Sep 2002
    Location
    Romania, Cluj-Napoca
    Posts
    11
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width