| Error in multiple nested select SQL statement [message #155559] |
Tue, 17 January 2006 04:15  |
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 #155957 is a reply to message #155636] |
Thu, 19 January 2006 20:01  |
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 |
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) |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
