Hi, I am trying to write a Cursor statement as follows
CURSOR c_GetWaiverInd IS
SELECT Emp_Id,
Join_Dt,
Trm_Dt,
Tax_Wvr_Ind
FROM
(SELECT Emp_Id,
Join_Dt,
Trm_Dt,
Tax_Wvr_Ind
LAG(Tax_Wvr_Ind,1,0) OVER (ORDER BY Join_Dt) AS Prev_Tax_Wvr_Ind
FROM Employee_Tax_History
WHERE Emp_Id = < i_EmpId
ORDER BY Join_Dt)
WHERE Tax_Wvr_Ind <> Prev_Tax_Wvr_Ind;
The SELECT portion works fine in SQL but the cursor gives the following error
LINE/COL ERROR
-------- --------------------------
21/38 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
, from
This error is at the order by clause of "LAG(Tax_Wvr_Ind,1,0) OVER (ORDER BY Join_Dt) AS Prev_Tax_Wvr_Ind"
Can you answer me why?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
You are probably on Oracle8i or below. The problem is that the Oracle8i PL/SQL engine was not configured to handle many of the new analytical syntax such as the window-partition, CASE, etc. The easiest thing to do is put your query into a view, and have your cursor select from that view. That keeps the parsing of the query in the SQL engine, and not in the PL/SQL engine.
The problem goes away in 9i, where the SQL and PL/SQL engine are one in the same.
Andrew
Only in 9i+ the sql-engine's are the same.
When using 8i you have to go for dynamicsql and put
your statement between quotes:
declare
type t_c is ref cursor;
c_GetWaiverInd t_c;
begin
open c_GetWaiverInd for
' SELECT Emp_Id
, Join_Dt
, Trm_Dt
, Tax_Wvr_Ind
FROM ( SELECT Emp_Id
, Join_Dt
, Trm_Dt
, Tax_Wvr_Ind
, LAG(Tax_Wvr_Ind,1,0) OVER (ORDER BY Join_Dt) AS Prev_Tax_Wvr_Ind
FROM Employee_Tax_History
WHERE Emp_Id = < i_EmpId
ORDER BY Join_Dt
)
WHERE Tax_Wvr_Ind <> Prev_Tax_Wvr_Ind '
;
<<the rest of the code...>>
Thanks Guys. I modified my query and created a view.
And thanks again for your valuable inputs.
I am attaching the view query here...
CREATE OR REPLACE VIEW View_Employee_Tax_Waiver AS
SELECT Emp_Id,
Join_Dt,
Trm_Dt,
Tax_Wvr_Ind
FROM
(SELECT Emp_Id,
Join_Dt,
Trm_Dt,
Tax_Wvr_Ind
LAG(Tax_Wvr_Ind,1,0) OVER (ORDER BY Join_Dt) AS Prev_Tax_Wvr_Ind
FROM (SELECT EH.Emp_Id,
Join_Dt,
Trm_Dt,
Tax_Wvr_Ind
LAG(EH.Emp_Id,1,0) OVER (ORDER BY EH.Emp_Id) AS Prev_Emp_Id
FROM Employee_History EH,
Emp_Tax_Waiver_Staging ETWS
WHERE ETWS.Emp_Id = EH.Emp_Id
ORDER BY Emp_Id,Prev_Emp_Id)
WHERE (Prev_Emp_Id = Emp_Id OR Prev_Emp_Id = 0)
ORDER BY Emp_Id, Join_Dt)
Business Accounts
Answer for Membership
by: verysimplePosted on 2003-05-01 at 13:30:01ID: 8438460
The question might be more difficult than what it seems but I only have 90 points at present. So, the answer will be extremely appreciated.