Complicated query - need Cursor?

[CraigBest]

Hope someone can help me...

I am trying to write a query that gets data from several tables. I have the query written and it works. I am including it here:

SELECT TO_CHAR(A.PONumPrefix, '000') || TO_CHAR(A.PONumMiddle, '000000') || TO_CHAR(A.PONumSuffix, '000') PONum, D.DeptName, P.VendorName, U.UserName FROM ApprovalTracking A, PO P, Department D, POUsers U WHERE A.Approved = ' ' AND A.PONumPrefix IN (SELECT DeptID FROM ORGCHART WHERE UserID = '" & gsLocalUser.UserID & "') AND U.UserID = A.AssignTo AND D.DeptID = A.PONumPrefix AND P.PONumPrefix = A.PONumPrefix AND P.PONumMiddle = A.PONumMiddle AND P.PONumSuffix = A.PONumSuffix ORDER BY P.CreationDate ASC, A.PONumMiddle ASC;

This query is supposed to get all pending approval records that are assigned to the department(s) the user belongs to. It works great. However, I need to restrict the select to pick only records that the local user is allowed to see.

Each user has a rank within a department that determines what they can and can't see. The users' rank can be different from department to department. I need to include something in the query that will check the department and rank level of the current record, and compare that to the local users' rank level for that department, and skip records that are higher ranking than the local user.

I'm starting to think this is going to require the use of a cursor, but I've never written with one before. Is there another way to do this? If not, how do I go about writing and using a cursor? I'm writing in Visual Basic 6 and using an Oracle 8 DB.

Thanks in advance

 

[Naseer]

Need more explaination of Ranks and Dept. and also the rough structure of the tables you are using, specially the one has relationship between Ranks and Dept.
Writing cursor is very simple e.g.
declare
cursor c1 is
select empno, ename, dept
from emp
order by 1;
begin
for c1REC in c1 loop
-- now c1REC.empno, c1REC.ename, c1REC.dept
-- are holding the values from table, u can do what u
-- want, this will loop through all row in the table
end loop;
end;

 

[CraigBest]

Nasser, thanks, but I was able to find a way of doing it with a separate query.

I got a temporary recordset with the Dept / Rank data in it, one record per entry in the table (same employee can have multiple records if they do work for different departments). I made up a routine that creates a string used in the query, repeating it for each record that occurs in the temporary query. Each time it repeats it places an "OR" statement in between the entries. At the end it encloses the whole thing in a set of parens and later the whole string is passed into the query text. Works like a charm!

vReturn = OpenRS(rsLookup, "SELECT DeptID, RankID FROM OrgChart WHERE UserID = '" & gsLocalUser.UserID & "'", cnPO, True)
rsLookup.MoveFirst
Do
If Len(sRankDept) > 0 Then
sRankDept = sRankDept & &quot OR ("
End If
sRankDept = sRankDept & &quot;A.PONumPrefix = &quot; & rsLookup.Fields!DeptID & &quot; AND A.ApprLevel <= &quot; & CInt(rsLookup.Fields!RankID) & &quot; &quot;
rsLookup.MoveNext
Loop Until rsLookup.EOF

If rsLookup.RecordCount = 1 Then
sRankDept = &quot;(&quot; & sRankDept & &quot AND &quot;
Else ' > 1
sRankDept = &quot;((&quot; & sRankDept & &quot) AND &quot;
End If

Hope that makes sense. Thanks for your help.

 

[CraigBest]

As long as we are on the subject of cursors, though...

I guess my big problem with them is I don't really understand where the SQL part ends and the Host Language (In my case VB6) begins. I have two books and neither explains it well enough for me to understand.

Maybe if I were able to see an example with VB Language in it it might make more sense.

 

[Naseer]

Craig,

I am very sorry for cursor things, because I am NOT a VB person. If you need any help in Oracle Developer/2000 let me know. Check some VB forum for help.

Good luck!

 

[carp]

This may have taken care of the problem for this specific situation. However, I would be concerned about users being able to see the table regardless of their rank/priority. I think I would be inclined towards creating a role that corresponds with each level of authority, and a view that restricts the viewable contents of the table(s) appropriately for each level. Then, rather than granting select privileges on the table to users, I would grant select privileges on the appropriate view to each role and grant the role to the appropriate users. This would enhance your security and make administration easier; as a user transitions between authority levels, you just grant them the appropriate role.

Of course, this assumes a database-centric solution; it might not be applicable to your situation.

 

[sem]

Another decision is to associate Oracle user with user as a POUsers (add ora_user to POUsers and store his Oracle username there). You may create the same view but with additional conditions: ora_user=USER and some conditions, that restricts records according to user's access level. This creates one view with different data for different users.

 

[CraigBest]

Thanks, guys, those sound like good ideas. I'm primarily a programmer and I'm just learning to use Oracle now. I think much of what you are discussing is currently beyond my abilities, but I will carefully consider this and do some research into how I might implement this. For now, most of what you are suggesting is being handled programmatically.