Home » Developer & Programmer » Forms » Error with inner select statement (forms 6i)
Error with inner select statement [message #406181] Tue, 02 June 2009 13:12 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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
Re: error with select statement [message #406184 is a reply to message #406181] Tue, 02 June 2009 13:35 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
Re: error with select statement [message #406185 is a reply to message #406184] Tue, 02 June 2009 13:56 Go to previous messageGo to next message
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
Re: error with select statement [message #406191 is a reply to message #406185] Tue, 02 June 2009 14:30 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
Re: Error with inner select statement [message #406290 is a reply to message #406181] Wed, 03 June 2009 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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.
Re: Error with inner select statement [message #407703 is a reply to message #406181] Thu, 11 June 2009 03:03 Go to previous message
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
Previous Topic: grant execution to a program unit in Forms
Next Topic: FRM-40735:WHEN-BUTTON-PRESSED
Goto Forum:
  


Current Time: Fri Dec 09 15:48:21 CST 2016

Total time taken to generate the page: 0.18734 seconds