Home » Developer & Programmer » Forms » fetch resutl of a query in a variable (forms6i)
fetch resutl of a query in a variable [message #686480] Fri, 23 September 2022 04:14 Go to next message
rhnshk
Messages: 26
Registered: May 2008
Junior Member
hi
what i am trying to achieve is on WHEN-MOUSE-DOUBLECLICK on a column field, opens an editor fetching few columns from tables, which will return single/muitiple rows.
on running the following piece of code , i am only getting 1 row displayed inside the EDITOR for a muilti-row dataset.
  DECLARE
  	 CURSOR C_TXT1 IS SELECT mprdh_no|| chr(9) ||ACNT_NAME|| chr(9) ||to_char(mprd_amount,'999999,990')
			FROM MPR_FORMD,ACCOUNTS_MASTER									
			WHERE  ACNT_CODE = MPRD_ITEM_CODE
			AND    MPRDH_NO = 384;

  	VAL VARCHAR2(4000);
  	ed_ok   BOOLEAN;
  BEGIN
	OPEN  C_TXT1;
  	FETCH C_TXT1 INTO VAL;  
  	CLOSE C_TXT1;
  	
Show_Editor( 'REMARKS_EDIT', val, 250,150, val, ed_ok); 

  END; 

the below query resutl is expected as seen from running at the sql prompt;

SQL> SELECT mprdh_no|| chr(9) ||ACNT_NAME|| chr(9) ||to_char(mprd_amount,'999999,990')
  2     FROM MPR_FORMD,ACCOUNTS_MASTER
  3  WHERE  ACNT_CODE = MPRD_ITEM_CODE           
  4  AND    MPRDH_NO = 384;

MPRDH_NO||CHR(9)||ACNT_NAME||CHR(9)||TO_CHAR(MPRD_AMOUNT,'999999,990')
----------------------------------------------------------------------------------------------------
384     R&M DOOR ACCESS CONTROL SYSTEM          143
384     R&M DOOR ACCESS CONTROL SYSTEM          143
384     R&M CEILING FAN          45
384     R&M CEILING FAN          15


thanks in advance. Appreciate for any help.
Regards

[Updated on: Fri, 23 September 2022 04:26]

Report message to a moderator

Re: fetch resutl of a query in a variable [message #686482 is a reply to message #686480] Sat, 24 September 2022 03:55 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The way you put it, you're fetching only the first row into the variable. One option to fix it is to use a cursor for loop which then concatenates results returned by the cursor to the previous values stored in a local variable. Something like this:

declare
  val     varchar2(4000);
  ed_ok   BOOLEAN;
begin
  for cur_r in 
    (select mprdh_no|| chr(9) ||ACNT_NAME|| chr(9) ||to_char(mprd_amount,'999999,990') val
     FROM MPR_FORMD,ACCOUNTS_MASTER									
     WHERE  ACNT_CODE = MPRD_ITEM_CODE
	   AND  MPRDH_NO = 384
    )
  loop
    val := val || chr(10) || cur_r.val;
  end loop;

  Show_Editor( 'REMARKS_EDIT', val, 250,150, val, ed_ok); 
END;
icon14.gif  Re: fetch resutl of a query in a variable [message #686483 is a reply to message #686482] Sat, 24 September 2022 04:56 Go to previous messageGo to next message
rhnshk
Messages: 26
Registered: May 2008
Junior Member
perfect ! Thanks LittleFoot.. Smile

To align the columns, I tried giving the substr, but I still get a shaky amount column.

is it that it could be achieved using %rowtype or %type?

SQL> ed
Wrote file afiedt.buf

1 SELECT substr(to_char(mprdh_no),1,10)|| chr(9) ||
2 substr(ACNT_NAME,1,20)|| chr(9) ||substr(to_char(mprd_amount,'999999,990'),1,15) val
3 FROM MPR_FORMD,ACCOUNTS_MASTER
4 WHERE ACNT_CODE = MPRD_ITEM_CODE
5* AND MPRDH_NO = 384
SQL> /

VAL
-------------------------------------------
384 R&M DOOR ACCESS CONT 143
384 R&M DOOR ACCESS CONT 143
384 R&M CEILING FAN 45
384 R&M CEILING FAN 15

Re: fetch resutl of a query in a variable [message #686484 is a reply to message #686483] Sat, 24 September 2022 14:25 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try RPAD (or LPAD, depending on datatype) instead of a SUBSTR.

Though, note that nothing will help much if font is proportional (such as Arial), e.g.

384 R&M DOOR ACCESS CONT 143
384 R&M CEILING FAN 45

If you used a non-proportional font (can't remember whether you can set font for a certain field) such as Courier, you'd see what you really expect, e.g.
384 R&M DOOR    ACCESS CONT 143
384 R&M CEILING        FAN   45

[Updated on: Sat, 24 September 2022 14:31]

Report message to a moderator

Re: fetch resutl of a query in a variable [message #686485 is a reply to message #686484] Sat, 24 September 2022 16:27 Go to previous message
rhnshk
Messages: 26
Registered: May 2008
Junior Member
thanks..ill take it from here.
Previous Topic: Copy time to another field
Next Topic: display field visual attribute
Goto Forum:
  


Current Time: Fri Mar 29 01:46:46 CDT 2024