Home » Developer & Programmer » Forms » ORA-01422
ORA-01422 [message #191106] Mon, 04 September 2006 11:16 Go to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
I receive this error message when there are duplicate records. How do you write code for a query to return the first record only?
Re: ORA-01422 [message #191118 is a reply to message #191106] Mon, 04 September 2006 14:27 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It depends on what you call the first record. It could be, for example, MIN, MAX, or dependant on condition in the WHERE clause.
Re: ORA-01422 [message #191289 is a reply to message #191118] Tue, 05 September 2006 08:40 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
I use the query below to regain only one record, which I guess is the one in the min rowid.

This query find the duplicate records
SELECT tax_payer_no, spouse_no
FROM ind_spouse
GROUP BY tax_payer_no,spouse_no
HAVING ( COUNT(spouse_no) > 1);


This query returns only the min rowid record
delete from ind_spouse
   where (rowid, spouse_no)
    not in
     (select min(rowid), spouse_no from ind_spouse group by spouse_no);

[Updated on: Tue, 05 September 2006 08:41]

Report message to a moderator

Re: ORA-01422 [message #191667 is a reply to message #191289] Thu, 07 September 2006 11:24 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
The following code returns ORA-01422 in the WHEN-VALIDATE-ITEM trigger but there is only one record. Can anyone assist me please? Keep in mind that I am not a forms modul/SQL expert.

Thanks

/* CGCC$CHK_CONS_ON_VF_MOD */
/* Validate item against appropriate check constraints */

DECLARE
  v_separation_date  ind_spouse.separation_date%type;
  V_TAX_NO INDIVIDUAL.TAX_PAYER_NO%TYPE;
  v_record_no      number;
  alert_is ALERT := FIND_ALERT('CFG_ERROR');
  alert_button number; 
  min_age number;
  CURSOR C3_SPOUSE IS
   select marriage_end_date,TAX_PAYER_NO ,spouse_no 
      from ind_spouse
      where spouse_no = :ind_spou.spouse_no AND marriage_end_date IS  NULL;
   CURSOR C1_SPOUSE IS
   select marriage_end_date,TAX_PAYER_NO,spouse_no  
      from ind_spouse
      where tax_payer_no  = :ind.tax_payer_no AND marriage_end_date IS NOT NULL;
       

 
BEGIN
  SELECT spouse_min_age 
    into min_age
    FROM SIGTAS_RULES
    WHERE sigtas_rules_no = 1;
   IF :ind_spou.dsp_birth_date is not null THEN
    IF months_between( :IND_SPOU.MARRIAGE_DATE , :IND_SPOU.dsp_birth_date ) /12 <  min_age then
      IRD_MSG(' Marriage date is under minimum date for marriage  ','E',TRUE);    
    END IF;
   END IF;
   IF :IND_SPOU.SEPARATION_DATE IS NOT NULL 
       AND :IND_SPOU.MARRIAGE_DATE IS NOT NULL THEN
    IF :IND_SPOU.MARRIAGE_DATE > :IND_SPOU.SEPARATION_DATE THEN
       IRD_MSG(' Marriage date is ahead of separation date ','E',TRUE);
     END IF;
   END IF;

   VALIDATE_END_MARRIAGE_DATE;

 
  IF :system.record_status = 'INSERT'  or :system.record_status = 'CHANGED' then
    if :ind_spou.marriage_date > sysdate then
      SET_ALERT_PROPERTY(alert_is,ALERT_MESSAGE_TEXT,' Marriage date is ahead of current date  ');
      alert_button := SHOW_ALERT(alert_is);  
      raise form_trigger_failure;
   end if; 
 
  

  IF :SYSTEM.RECORD_STATUS = 'CHANGED'    THEN 
    FOR IND_SPOU_TAB1 IN C1_SPOUSE
       LOOP
    
        IF  :IND_SPOU.MARRIAGE_DATE  <= IND_SPOU_TAB1.MARRIAGE_END_DATE  then
              --  AND :IND.TAX_PAYER_NO <> IND_SPOU_TAB1.TAX_PAYER_NO THEN
            IRD_MSG('Taxpayer already assigned spouse  #:  '|| to_char(ind_spou_tab1.spouse_NO) ||' in this period ','E',TRUE); 
          END IF;
       END LOOP;
  END IF;








    IF :SYSTEM.RECORD_STATUS = 'INSERT'    THEN 
     FOR IND_SPOU_TAB IN  C3_SPOUSE
       LOOP
          SET_ALERT_PROPERTY(alert_is,ALERT_MESSAGE_TEXT,'Spouse #  '|| to_char(ind_spou_tab.spouse_no) || ' Already Assigned To Tax Payer  # ' || to_char(ind_spou_tab.tax_payer_no)  );
          alert_button := SHOW_ALERT(alert_is);  
          raise form_trigger_failure;
       END LOOP;
    
      FOR IND_SPOU_TAB1 IN C1_SPOUSE
       LOOP
          IF  :IND_SPOU.MARRIAGE_DATE  <= IND_SPOU_TAB1.MARRIAGE_END_DATE  then
           --   AND :IND.TAX_PAYER_NO <> IND_SPOU_TAB1.TAX_PAYER_NO THEN
            IRD_MSG('Taxpayer already assigned spouse  #:  '|| to_char(ind_spou_tab1.spouse_NO) ||' in this period ','E',TRUE); 
          END IF;
       END LOOP;
      END IF;
      IF :SYSTEM.RECORD_STATUS = 'INSERT'  
             THEN 
            FOR IND_SPOU_TAB1 IN C1_SPOUSE
       LOOP
          IF :IND_SPOU.MARRIAGE_DATE <= IND_SPOU_TAB1.MARRIAGE_END_DATE 
           AND :IND.TAX_PAYER_NO <> IND_SPOU_TAB1.TAX_PAYER_NO then
            IRD_MSG('Taxpayer already assigned spouse  #:  '|| to_char(ind_spou_tab1.spouse_NO) ||' in this period ','E',TRUE); 
          END IF;
       END LOOP;

      END IF;

  
      IF ( :ind_spou.marriage_date < :ind_spou.dsp_birth_date
             and :ind_spou.dsp_birth_date is not null ) then
           SET_ALERT_PROPERTY(alert_is,ALERT_MESSAGE_TEXT,' Marriage Date Exceeds Birth Date Of Spouse  ');
           alert_button := SHOW_ALERT(alert_is);  
           raise form_trigger_failure;
      END IF;      
  END IF;



/* CGUV$CHK_KEYS_ON_VAL_FLD */
/* Check item value against unique or primary key */
  CGUV$CHK_IND_SPOU_PK(
    :IND_SPOU.MARRIAGE_DATE    /* IN : Item value                    */
   ,:IND_SPOU.SPOUSE_NO        /* IN : Item value                    */
   ,:IND_SPOU.TAX_PAYER_NO     /* IN : Item value                    */
   ,TRUE                   );  /* IN : Is the trigger item level?    */
  IRD_MSG('Row exists already with same Tax Payer No,Spouse No,Marriage
Date', 'E', TRUE);
EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
  WHEN OTHERS THEN
    CGTE$OTHER_EXCEPTIONS;
END;
Re: ORA-01422 [message #191747 is a reply to message #191667] Fri, 08 September 2006 00:35 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Eighter Your are having more than one rows for

SELECT spouse_min_age
into min_age
FROM SIGTAS_RULES
WHERE sigtas_rules_no = 1;

or

there is any query in called procedure VALIDATE_END_MARRIAGE_DATE, which is raising this error.

Sandy
Re: ORA-01422 [message #191888 is a reply to message #191747] Fri, 08 September 2006 08:33 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
SELECT spouse_min_age
into min_age
FROM SIGTAS_RULES
WHERE sigtas_Rules_no = 1;

returns one row only when run explicity in SQL Worksheet.You're right there is a procedure named VALIDATE_END_MARRIAGE_DATE (below). How do I know it returns more than one row? How do I test it?

PROCEDURE VALIDATE_END_MARRIAGE_DATE IS
BEGIN
  
 
 
IF :ind_spou.MARRIAGE_END_DATE is not null then
  IF :ind_spou.MARRIAGE_END_DATE > sysdate then
      IRD_MSG(' Separation Date Exceeds Current Date  ','E',TRUE);
     
  END IF;  

 
    IF :IND_SPOU.SEPARATION_DATE IS NOT NULL THEN
       IF :IND_SPOU.MARRIAGE_END_DATE < :IND_SPOU.SEPARATION_DATE THEN
          IRD_MSG(' End date of marriage is earlier than separation date ','E',TRUE);
       END IF;
    END IF; 

   IF  :ind_spou.marriage_date is not null then   
     IF :ind_spou.MARRIAGE_END_DATE <  :ind_spou.marriage_date then
         IRD_MSG(' End date of marriage is earlier than marriage date ','E',TRUE);
     END IF; 
   END IF;

 END IF;
 END;
Re: ORA-01422 [message #191895 is a reply to message #191888] Fri, 08 September 2006 09:12 Go to previous message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Your have written a exception handler
when others then
CGTE$OTHER_EXCEPTIONS.

So is should not show any error, except the No_data_Found, all the errors will be handled by this handler. If it's showing means there might be failure in CGTE$OTHER_EXCEPTIONS.
Please check in CGTE$OTHER_EXCEPTIONS too;
Previous Topic: FROM clause query and global variables
Next Topic: automatic message notification
Goto Forum:
  


Current Time: Wed Dec 07 20:49:46 CST 2016

Total time taken to generate the page: 0.11417 seconds