Is there a better way than a correlated subquery? 1

[CharlesCook]

I have a table that containes multiple records for each person's benefit plans. Each plan has a effective date and effective squence. So a employee could have several records for the same benefit each with a different EFFDT and EFFSEQ combo.

The columns are as follows:

EMPLID
EMPL_RCD_NBR
EFFDT
EFFSEQ
BENEFIT_PROGRAM

A user wants to see the current benefit and current -1 benefit. To get the current benefit they are using a correlated subquery shown below in their where clause.

("PV_BEN_PROG_VW2"."EFFSEQ" = (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "SYSADM"."PV_BEN_PROG_VW" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PV_BEN_PROG_VW2"."EMPLID"
AND "INNERALIAS"."EFFDT" = "PV_BEN_PROG_VW2"."EFFDT")
AND
"PV_BEN_PROG_VW2"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "SYSADM"."PV_BEN_PROG_VW" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PV_BEN_PROG_VW2"."EMPLID"))

To get the curent benefit -1 using a sub query in a sub query would get very messy. Is there a better way with newer SQL techniques?

Thanks

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[SantaMufasa]

Is there a better way with newer SQL techniques?

Actually, the following is not a "newer SQL technique", but it will work for you. It displays the two most recent BENEFIT_PROGRAMs for a given EMPLID.

Section 1 -- Sample data:

select * from pv_ben_prog_vw;

EFFSEQ     EMPLID EFFDT     
------ ---------- ---------
   100        204 31-AUG-92
   101        205 31-AUG-92
   111        204 09-SEP-92
    97        201 28-AUG-92
    98        202 31-AUG-92
    99        203 31-AUG-92
   113        204 10-SEP-92

Section 2 -- Sample code to do what you want. (Note: since my code is prompting for the Employee number to display, the code must run from a SQL*Plus script. In my case, I named the script "tt_355.sql".):

set verify off
accept empl prompt "Enter the employee number for whom to list benefit programs: "
break on emplid
select emplid, effseq, effdt
  from (select rownum rn, a.* from pv_ben_prog_vw a
         where emplid = '&empl'
         order by effdt desc)
 where rn <= 2;

Section 3 -- Sample execution and output from "tt_355.sql" running against Section 1 data:

SQL> @tt_355
Enter the employee number for whom to list benefit programs: 204

    EMPLID     EFFSEQ EFFDT
---------- ---------- ---------
       204        111 09-SEP-92
                  100 31-AUG-92

SQL> @tt_355
Enter the employee number for whom to list benefit programs: 205

    EMPLID     EFFSEQ EFFDT
---------- ---------- ---------
       205        101 31-AUG-92

Let us know if this does what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[CharlesCook]

Thanks Dave,

If I changed the where clause to:

where rn <= 3;

Would it return the 3 most recent BENEFIT_PROGRAM rows?


The tool the user has does not let them change the from clause very easly. Is their any other way to do this without using an inline view?

Thanks
Charles

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[SantaMufasa]

If I changed the where clause to:

where rn <= 3;

Would it return the 3 most recent BENEFIT_PROGRAM rows?

Yes.

The tool the user has does not let them change the from clause very easly. Is their any other way to do this without using an inline view?

Sure...change the entire SELECT statement, above, to a classic Oracle VIEW. That way, you could change the WHERE clause "on demand" or "at will" by issuing the command:

CREATE [b]or REPLACE[/b] VIEW <name>
AS SELECT...FROM <in-line VIEW> WHERE <new conditions>...et cetera

Let us know if you have other questions?


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[CharlesCook]

Thanks Dave,

Sorry to be a pain in the a$$, but other then creating a view (the users don't have security and the IS dept has bigger fish to fry) is there any other way? Could you use analytic functions for this? I have never used analytic functions so I don't know.

Thanks Again for all your help
Charles Cook

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[SantaMufasa]

Charles,

It's fascinating to me that your users can apparently create CR queries to their hearts' content, but they do not have permission to name their queries (which is exactly what a VIEW is...a named query).

As far as analytic functions are concerned: they may provide you with a solution, but how would analytic functions, but you said:

The tool the user has does not let them change the from clause very easly. Is their any other way to do this without using an inline view?

Do they have any greater latitude to produce analytic functions? Because analytic functions could certainly be more "dangerous" than allowing users to CREATE VIEWs.


And anyway, you needn't GRANT CREATE VIEW to all your users...just GRANT CREATE VIEW to a single business administrator who can grant read-only access on the VIEW to appropriate users.

...Or are your DBAs like many others: Legends in their own Minds...Gods unto themselves...who must keep a tight hold on data to prevent users from getting their work done? "Information is Power...Power to the DBAs"

<grin><grin><grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[taupirho]

Using analytics, the following SQL will retrieve the TOP N records per emplid grouping:

select
t1.emplid, t1.effseq, t1.effdt
from
(
select emplid, effseq, effdt,
dense_rank() over(partition by emplid order by
effdt desc,effseq) rnk
from pv_ben_prog) t1
where t1.rnk <= N

 

[CharlesCook]

This "End User Reporting Tool" is used by payroll clerks and up the food chain, as well as benefit dept and hr dept.

The tool creates the SQL for you as you work in it's interface. It even creates correlated subquerys. The problem is to create a full feature report you need to use SQL functions and complex selection critera and database grouping. So the user needs to know more and more about the sql. The tool only wants you to write your own sql in some places (Select, Where, Having) or you can edit all of the sql. If you edit all of the sql you lose a lot of the tools functions so most people don't do this.

Many times I haved been asked to look at a report that works most of the time and just had to laugh. Then I would rewrite the report from scratch because the report never worked they just did not know it.

Because this tool is only in the payroll/hr/benefits departments the IS dept/DBA may never see it. It may be installed by the same people who install ms-word.

I think most DBA's would freek out if they knew just how much SQL is created by end users. And these databases are very very big (all employees in the company X all checks big).

Anyway thanks for all your help.


CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[CharlesCook]

Here is a star for your help.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[CharlesCook]

Thanks also taupirho.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining