OraFAQ Forum: Forms » Select...AS.. .From error in oracle forms

Home » Developer & Programmer » Forms » Select...AS.. .From error in oracle forms

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

Select...AS.. .From error in oracle forms [message #208289]

Sat, 09 December 2006 01:48

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.

Report message to a moderator

Re: Select...AS.. .From error in oracle forms [message #208291 is a reply to message #208289]

Sat, 09 December 2006 02:17

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

Re: Select...AS.. .From error in oracle forms [message #208292 is a reply to message #208291]

Sat, 09 December 2006 02:23

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...

Report message to a moderator

Re: Select...AS.. .From error in oracle forms [message #208302 is a reply to message #208292]

Sat, 09 December 2006 03:58

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

Report message to a moderator

Re: Select...AS.. .From error in oracle forms [message #208465 is a reply to message #208289]

Mon, 11 December 2006 00:18

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 13:50:00 CDT 2024