Home » Developer & Programmer » Forms » ORA-01422 (FORM BUILDER 6i)
icon9.gif  ORA-01422 [message #396102] Fri, 03 April 2009 20:46 Go to next message
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);

...
...
Re: ORA-01422 [message #396159 is a reply to message #396102] Sat, 04 April 2009 07:09 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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.
Re: ORA-01422 [message #396320 is a reply to message #396102] Mon, 06 April 2009 02:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
Re: ORA-01422 [message #396325 is a reply to message #396322] Mon, 06 April 2009 02:21 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
Thanks Littlefoot..

This means, the select statement must be wrote in cursor declaration,isn't it?
Re: ORA-01422 [message #396328 is a reply to message #396325] Mon, 06 April 2009 02:40 Go to previous messageGo to next message
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.
Re: ORA-01422 [message #396346 is a reply to message #396102] Mon, 06 April 2009 04:23 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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.

Re: ORA-01422 [message #396350 is a reply to message #396102] Mon, 06 April 2009 04:42 Go to previous messageGo to next message
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 115 times)
Re: ORA-01422 [message #396356 is a reply to message #396102] Mon, 06 April 2009 05:05 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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.
Re: ORA-01422 [message #396367 is a reply to message #396356] Mon, 06 April 2009 05:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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;
Re: ORA-01422 [message #396374 is a reply to message #396367] Mon, 06 April 2009 06:21 Go to previous message
itech
Messages: 168
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.

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


Current Time: Thu Dec 08 16:31:43 CST 2016

Total time taken to generate the page: 0.14631 seconds