OraFAQ Forum: Forms » ORA-01422

Home » Developer & Programmer » Forms » ORA-01422 (FORM BUILDER 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01422 [message #396102]

Fri, 03 April 2009 20:46

fadhzone
Messages: 61
Registered: April 2008

Member

Hi experts,

My problem is 'more than one row was returned'.
Now, how can I write a script to fetch all the data?
I tried using cursor but still not work.
This is my piece of code.

...
...

-- TO GET SUPPLIER ARTICLE NO
end_point := INSTR(linebuf,',');			
IF end_point > 1 THEN
  L_ART_NO := SUBSTR(linebuf,1, end_point - 1);
					
  --GET SKU_CD & SKU_DESC
  BEGIN
    SELECT  COUNT(*)
    INTO    ln_count
    FROM    VPC
    WHERE   SUP_ARTICLE_NO = L_ART_NO
    AND     SUP_MAINCD IN (3088,81282,98000);
		
    IF ln_count	= 0 THEN
      :PARAMETER.AL_BTN := Msg.Show_Msg('Check the SKU', Msg.MTEXT,'OK');
      GO_ITEM(:PARAMETER.CURSOR_ITEM);        
      GOTO ENDLOOP;
    ELSE
      /* THIS PART GIVE ME THE ERROR */
      SELECT VPC.SKU_CD,PRODUCT_MASTER.SKU_DESC
      INTO   :BL_UPLOAD_DET.SKU_CD,:BL_UPLOAD_DET.SKU_DESC
      FROM   PRODUCT_MASTER,VPC
      WHERE  PRODUCT_MASTER.SKU_CD = VPC.SKU_CD
      AND    VPC.SUP_MAINCD IN (3088,81282,98000)
      AND    VPC.SUP_ARTICLE_NO = L_ART_NO;
    END IF;
  END;
END IF;
linebuf := SUBSTR(linebuf, end_point + 1);

...
...

Report message to a moderator

Re: ORA-01422 [message #396159 is a reply to message #396102]

Sat, 04 April 2009 07:09

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

Rewrite your query to only return one row.
If you want to get a single row back and you're not then your where clause is not restrictive enough.

I'd also recommend not using goto. If you want to get out of a loop use exit.

Report message to a moderator

Re: ORA-01422 [message #396320 is a reply to message #396102]

Mon, 06 April 2009 02:08

fadhzone
Messages: 61
Registered: April 2008

Member

Thanks Monster.

But.. i want to insert like this

date        inv no  art no desc      sku   qty  cost   total cost
------------------------------------------------------------------------------------------
20/11/2007  DN001   8702   HAMMER    1001  500	3.55   1,775.00 
20/11/2007  DN001   8704   TS HAMMER 1002  500	10.54  5,270.00
20/11/2007  DN001   8704   TS HAMMER 1025  500	10.54  5,270.00
20/11/2007  DN001   8950   TS SAW    4126  500	15.00  7,500.00

even though art no is same,it has different sku.
I need both sku

[EDITED by LF: applied [code] tags]

[Updated on: Mon, 06 April 2009 02:10] by Moderator

Report message to a moderator

Re: ORA-01422 [message #396322 is a reply to message #396320]

Mon, 06 April 2009 02:16

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As you've said, this piece of code produces an error:

SELECT VPC.SKU_CD,PRODUCT_MASTER.SKU_DESC
      INTO   :BL_UPLOAD_DET.SKU_CD,:BL_UPLOAD_DET.SKU_DESC
      FROM   PRODUCT_MASTER,VPC
      WHERE  PRODUCT_MASTER.SKU_CD = VPC.SKU_CD
      AND    VPC.SUP_MAINCD IN (3088,81282,98000)
      AND    VPC.SUP_ARTICLE_NO = L_ART_NO;

No matter that you "use a cursor" (so what? Cursor doesn't guarantee that you'll fetch a single record in some SELECT statement which, eventually, uses values fetched by that cursor).

It appears that the above SELECT statement's WHERE clause should be adjusted by, probably, adding another condition(s) which will ensure that it returns one and only one record.

If you want to select them all, no problem - put this SELECT statement into a cursor FOR loop, use NEXT_RECORD and fill as many records in a block as SELECT returns.

Report message to a moderator

Re: ORA-01422 [message #396325 is a reply to message #396322]

Mon, 06 April 2009 02:21

fadhzone
Messages: 61
Registered: April 2008

Member

Thanks Littlefoot..

This means, the select statement must be wrote in cursor declaration,isn't it?

Report message to a moderator

Re: ORA-01422 [message #396328 is a reply to message #396325]

Mon, 06 April 2009 02:40

fadhzone
Messages: 61
Registered: April 2008

Member

...
...
CURSOR C_SKU_DETAILS (L_ART_NO VARCHAR2) IS
	SELECT  VPC.SKU_CD,PRODUCT_MASTER.SKU_DESC,PRODUCT_MASTER.STATUS
	FROM    PRODUCT_MASTER,VPC
	WHERE   PRODUCT_MASTER.SKU_CD = VPC.SKU_CD
	AND			PRODUCT_MASTER.STATUS = 'A'
	AND     VPC.SUP_MAINCD IN (3088,81282,98000)
	AND     VPC.SUP_ARTICLE_NO = L_ART_NO;

BEGIN
...
...
--SUP ARTICLE NO
end_point := INSTR(linebuf,',');			
IF end_point > 1 THEN
 L_ART_NO := SUBSTR(linebuf,1, end_point - 1);

 --GET SKU_CD & SKU_DESC			
 BEGIN
  SELECT COUNT(*)
  INTO   ln_count
  FROM	VPC
  WHERE  SUP_ARTICLE_NO = L_ART_NO
  AND	SUP_MAINCD IN (3088,81282,98000);
		
  IF ln_count = 0 THEN
   :PARAMETER.AL_BTN := Msg.Show_Msg('Check the SKU', Msg.MTEXT,'OK');
   GO_ITEM(:PARAMETER.CURSOR_ITEM);        
   GOTO ENDLOOP;
  ELSE
   FOR C_SKU IN C_SKU_DETAILS(L_ART_NO)
   LOOP
    EXIT WHEN C_SKU_DETAILS%NOTFOUND;
    L_SKU_CD 	:= C_SKU.SKU_CD;
    L_SKU_DESC	:= C_SKU.SKU_DESC;
    NEXT_RECORD;
   END LOOP;
  END IF;
 END;					
END IF;
linebuf := SUBSTR(linebuf, end_point + 1);

...
...

It only displayed last sku. it just like the second sku overwrite the first sku.

Report message to a moderator

Re: ORA-01422 [message #396346 is a reply to message #396102]

Mon, 06 April 2009 04:23

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

There's nothing obviously wrong with it.
Time for you to debug it.
use message/pause pairs to display the values being fetched by the cursor in each iteration of the loop and also system.cursor_record to show what record it's in.

I would remove this line of code:

EXIT WHEN C_SKU_DETAILS%NOTFOUND;

for loops do that for you automatically so it isn't needed.

Report message to a moderator

Re: ORA-01422 [message #396350 is a reply to message #396102]

Mon, 06 April 2009 04:42

fadhzone
Messages: 61
Registered: April 2008

Member

cookiemonster...
refer to my attachment...
i tried your suggestion but it still doesn't work.

Attachment: TS101F.fmb
(Size: 520.00KB, Downloaded 1000 times)

Report message to a moderator

Re: ORA-01422 [message #396356 is a reply to message #396102]

Mon, 06 April 2009 05:05

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

I am not debugging it for you!

Debug it. if you're still still stuck report what the debug's show and we'll go from there.

Report message to a moderator

Re: ORA-01422 [message #396367 is a reply to message #396356]

Mon, 06 April 2009 05:58

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Here's the new code:

FOR C_SKU IN C_SKU_DETAILS(L_ART_NO)
LOOP
  L_SKU_CD 	:= C_SKU.SKU_CD;
  L_SKU_DESC	:= C_SKU.SKU_DESC;
  NEXT_RECORD;
END LOOP;

It will NOT modify any of block items, because it fetches data into locally declared variables. Shouldn't it be something like

:block.sku_cd   := c_sku.sku_cd;
:block.sku_desc := c_sku.sku_desc;

next_record;

Report message to a moderator

Re: ORA-01422 [message #396374 is a reply to message #396367]

Mon, 06 April 2009 06:21

itech
Messages: 173
Registered: May 2008
Location: Fsd, Pakistan

Senior Member

before your code starts to execute, make sure that

1. your cursor is in the target block,
2. make sure its pointing to the last_record
3. before inserting new values u must use CREATE_RECORD; builtin to create a new record in your desired block to save data on it, then u wont need next_record;

try using create_record; i think u're missing it. the records data is being overwritten with the next fetched record and eventualy only the last record is displayed on the block.

Report message to a moderator

Previous Topic:	how to get user_ip address ?
Next Topic:	Runtime Validation in Forms

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 12:20:45 CDT 2024