Home » Developer & Programmer » Forms » Select...AS.. .From error in oracle forms
icon4.gif  Select...AS.. .From error in oracle forms [message #208289] Sat, 09 December 2006 01:48 Go to next message
marge_cpn
Messages: 16
Registered: July 2006
Location: Makati City, Philippines
Junior Member


hi everyone,

why is it that the select AS from Table does not work with procedure being called in oracle forms. Is it really incompatible.

This is my exact code and it resulted an error
select distinct(PLAN_CODE) As PROD_ID,
DESCRIPTION
from gtr_plan;

Is there an alternative?

Please need help right away.

Thank you all very much.
Re: Select...AS.. .From error in oracle forms [message #208291 is a reply to message #208289] Sat, 09 December 2006 02:17 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

hi,
SELECT AS is compatible in Oracle forms as well. However, it'll not work unless you don't include an INTO clause in your code.

Your code should be like this.
DECLARE
v_plan_code NUMBER;
v_desc VARCHAR2(100);
BEGIN
select distinct(PLAN_CODE) As PROD_ID,DESCRIPTION
INTO v_plan_code, v_desc
from gtr_plan
WHERE ......


PLAN_CODE and DESCRIPTION should return only one value. Otherwise you'll get an error. If the query returns more than one rows, use cursor instead of simple variable.

regards,
Saadat Ahmad

[Updated on: Sat, 09 December 2006 02:18]

Report message to a moderator

icon4.gif  Re: Select...AS.. .From error in oracle forms [message #208292 is a reply to message #208291] Sat, 09 December 2006 02:23 Go to previous messageGo to next message
marge_cpn
Messages: 16
Registered: July 2006
Location: Makati City, Philippines
Junior Member


hi,

thanks for the reply.
these codes are actually use cursor but still encountered an error.

CURSOR C2 IS
    select distinct(PLAN_CODE) AS PROD_ID,
	DESCRIPTION
    from gtr_plan;


please help...

Re: Select...AS.. .From error in oracle forms [message #208302 is a reply to message #208292] Sat, 09 December 2006 03:58 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

in that case, you are not providing the full code.

Ok, here is an example. Folow the steps then.

Create an test form using the "emp" table in scott's schema.

Write this code on When-New-Form-Instance trigger
DECLARE
	CURSOR c1 IS SELECT empno AS EMP_NO, ename
	FROM emp;
	c2 c1%ROWTYPE;
BEGIN
 OPEN c1;
 LOOP FETCH c1 INTO c2;
 	EXIT WHEN c1%NOTFOUND;
 	MESSAGE(c2.EMP_NO||' '||c2.ename); --note c2.EMP_NO here
 END LOOP;
 CLOSE c1;
END;

run the form and you should get your answer.

regards,
Saadat Ahmad
Re: Select...AS.. .From error in oracle forms [message #208465 is a reply to message #208289] Mon, 11 December 2006 00:18 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What version of Forms and database are you running?

Have you tried it without the 'AS'?

David

[Updated on: Mon, 11 December 2006 00:19]

Report message to a moderator

Previous Topic: Forms 9i reports
Next Topic: string error
Goto Forum:
  


Current Time: Sun Dec 04 04:31:14 CST 2016

Total time taken to generate the page: 0.09851 seconds