Home » Fusion Middleware & Colab Suite » WebCenter Suite & Portal » Error in multiple nested select SQL statement
Error in multiple nested select SQL statement [message #155559] Tue, 17 January 2006 04:15 Go to next message
carmenlam
Messages: 3
Registered: January 2006
Junior Member
Hi,

I'm have a multiple nested select statement which runs fine in TOAD SQL Editor. However, it generates error when I try to run it as cursor in either Portal Dynamic Page / TOAD Stored Procedure. A copy of the sample statement and screen shot is uploaded.

Please assists.

Thank you.

Regards,
CL

Re: Error in multiple nested select SQL statement [message #155636 is a reply to message #155559] Tue, 17 January 2006 14:17 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
It looks like a variable is too small to hold the string. Try removing any unnesessary leading or trailing whitespace in the SQL. If that doesn't help, create a view using your select and then try selecting from the view.
Re: Error in multiple nested select SQL statement [message #155957 is a reply to message #155636] Thu, 19 January 2006 20:01 Go to previous messageGo to next message
carmenlam
Messages: 3
Registered: January 2006
Junior Member
hi Andrew,

It seems like your comment was correct. Because I couldn't have the full coding pasted when I try to copy paste my code into SQL Plus in order to re-produce the same error message for Oracle Metalink.

I'm now intend to create a view as what you advise. However, I wondering on how am I suppose to create the said view table because my data are plugging out from many different tables for each particular person. Meaning, I have to loop it so that I can have something like a row of clean records ( plugged out from many source tables ) for each and every person id that I have in my database.

Please advise.

Regards,
CL
Re: Error in multiple nested select SQL statement [message #156103 is a reply to message #155957] Fri, 20 January 2006 15:05 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Well, for a start, I suspect your 'XXX' litteral should be replaced with a variavle name. Anything you can do to shorten the SQL could get you below the limit will help. Try shorteniong the SQL just to find where the cutoff is - even if the SQL doesn't return the right answer.
1.) You can create synonyms for the tables and then use those in your SQL (e.g. create synonym paassign_f for apps.per_all_assignments_f).
2.) remove unnecessary alias qualifiers from column names if possible (change pf.last_name to last_name if possible)
3.) replace this repetitive pattern with a view:
(SELECT ppf.person_id
                     FROM apps.per_all_people_f ppf
                    WHERE ppf.employee_number = 'XXX'
                      AND ppf.current_employee_flag = 'Y'
                      AND SYSDATE BETWEEN ppf.effective_start_date
                                      AND ppf.effective_end_date))

create or replace v1 as ((SELECT ppf.person_id, ppf.employee_number
                     FROM apps.per_all_people_f ppf
                    WHERE ppf.current_employee_flag = 'Y'
                      AND SYSDATE BETWEEN ppf.effective_start_date
                                      AND ppf.effective_end_date)));

select person_id from v1 where employee_number = 'XXX'
(use V1 in your big SQL)
Previous Topic: "Existing Oracle9iAS Single Sign-On" hostname !!!!ERROR!!!!
Next Topic: Oracle Portal compatibility & Upgrade question
Goto Forum:
  


Current Time: Tue Oct 27 13:54:29 CDT 2020