OraFAQ Forum: Forms » Error with inner select statement

Home » Developer & Programmer » Forms » Error with inner select statement (forms 6i)

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

Error with inner select statement [message #406181]

Tue, 02 June 2009 13:12

deahayes3
Messages: 203
Registered: May 2006

Senior Member

Hello all,

The script below works fine in sql, but when I put it in a procedure in forms I get errors around the Max select statement. Any thoughts on why I am receiving an error.

SELECT Count(DISTINCT (o.rowid))
INTO   v_jan --comment out in sql
FROM   incident_info o,
       human_resource h
WHERE  o.person_id = h.person_id
       AND o.location_id = 'US'
       AND (o.first_aid_only_doctor = 'Y'
             OR o.first_aid = 'Y')
       AND o.incident_date BETWEEN To_date('01-JAN'
                                           ||:dummy.p_year,'DD-MON-YYYY') AND Last_day(To_date('01-JAN'
                                                                                               ||:dummy.p_year,'DD-MON-YYYY'))
       AND ((Round(Months_between(To_date('01-JAN'
                                          ||:dummy.p_year,'DD-MON-YYYY'),(SELECT Max(h1.effective_start_date)
                                                                          FROM   human_resource h1
                                                                          WHERE  h1.person_id = o.person_id
                                                                                 AND h1.effective_start_date <= o.incident_date))) > 0))
       AND ((Round(Months_between(To_date('01-JAN'
                                          ||:dummy.p_year,'DD-MON-YYYY'),(SELECT Max(h2.effective_start_date)
                                                                          FROM   human_resource h2
                                                                          WHERE  h2.person_id = o.person_id
                                                                                 AND h2.effective_start_date <= o.incident_date))) <= 3));

[Updated on: Tue, 02 June 2009 13:42] by Moderator

Report message to a moderator

Re: error with select statement [message #406182 is a reply to message #406181]

Tue, 02 June 2009 13:26

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Not without you actually telling us the error.

Two things it could be are the datatype of V_JAN, or some problems in the date formats.

[Updated on: Tue, 02 June 2009 13:26]

Report message to a moderator

Re: error with select statement [message #406183 is a reply to message #406182]

Tue, 02 June 2009 13:33

deahayes3
Messages: 203
Registered: May 2006

Senior Member

It doesn't really give a specific error, it just won't compile. Here is a snippet of what it says:
Error 103 at line 33, column 78
Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifer>
<a bind variable> avg etc.....

Error 103 at line 34, column 153
Encountered the symbol ">" when expecting one of the following:
(Intersect, minus, union )

Hope this helps

Report message to a moderator

Re: error with select statement [message #406184 is a reply to message #406181]

Tue, 02 June 2009 13:35

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

I'm not sure whether Forms 6i is released on Oracle 8 or 8i.
May be the inner query is supported by Forms 6i.
And your database version is supporting.
What is your database version?

In that case, you may need to rewrite your query.

By
Vamsi

Report message to a moderator

Re: error with select statement [message #406185 is a reply to message #406184]

Tue, 02 June 2009 13:56

deahayes3
Messages: 203
Registered: May 2006

Senior Member

I am not sure where to locate my database version but I know its higher than 8 or 8i, I think its 10.

I don't even know where to begin to rewrite this, this one already took some time Smile

Report message to a moderator

Re: error with select statement [message #406191 is a reply to message #406185]

Tue, 02 June 2009 14:30

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

You can't use the inner query in the functions.
But you can write a query such that you will get all the data included the max dates in the inner query itself,
then you can use them in the functions in the outer query.

By
Vamsi

Report message to a moderator

Re: Error with inner select statement [message #406290 is a reply to message #406181]

Wed, 03 June 2009 03:17

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

Senior Member

Or you could just put your query in a function in the database and call that from your form.

Report message to a moderator

Re: Error with inner select statement [message #407703 is a reply to message #406181]

Thu, 11 June 2009 03:03

nagu_bhat
Messages: 10
Registered: May 2009

Junior Member

hi

try this out

select count(distinct (o.rowid))
into v_jan --comment out in sql
from incident_info o, human_resource h,
(select s.person_id sperson_id, max(t.effective_start_date) max_eff_date
from incident_info s, human_resource t
where s.person_id = t.person_id
and t.effective_start_date <= s.incident_date)
where o.person_id = h.person_id
and o.location_id = 'US'
and (o.first_aid_only_doctor = 'Y' or o.first_aid = 'Y')
and o.incident_date between trunc(to_date(:dummy.p_year.'YEAR')) and last_day(trunc(to_date(:dummy.p_year.'YEAR')))
and (round(months_between(trunc(to_date(:dummy.p_year.'YEAR')), max(eff_date)) between 1 and 3
and sperson_id (+) = o.person_id

Regards
Nagaraj

Report message to a moderator

Previous Topic:	grant execution to a program unit in Forms
Next Topic:	FRM-40735:WHEN-BUTTON-PRESSED

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 15:56:43 CST 2024