Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am struggling with the following SQL query right now:

http://sqlfiddle.com/#!4/736a7/12

select uc.*
from usercredential uc
inner join users u on u.userid = uc.userid
inner join credential c on c.credentialid = uc.credentialid
inner join healthsystemcredential hsc on hsc.credentialid = c.credentialid
inner join accesslevel ac on hsc.hscredentialid in (
  -- Trying to figure out how to filter here?
  select 1 from dual
)
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null) or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) )

Basically I am trying to get UserCredential that are Many To One with Credential. Credential is One To Many with HealthSystemCredential.

The HealthSystemCredential has a foreign key to AccessLevel, however this AccessLevel column in HealthSystemCredential is actually the Lowest Allowable Access Level. AccessLevel records have a Type Rank between 1 at the lowest and 4 at the highest.

Example: A HealthSystemCredential with an AccessLevel record that has a Type Rank of 2 should be fetched when I filter my query by AccessLevels with Types of 2, 3, or 4.

The problem is I can't figure out how to do this in one query. Is there some way I can get a DENSE_RANK column in a subquery of AccessLevels, apply my filter in the subquery and join it to the table somehow to give me all the applicable HealthSystemCredentials I want?

share|improve this question
    
TIP: dont use the new ANSI SQL standard (inner join,left join... etc) if you don't have to (the only case i can think of is full outer join). it's very buggy. you wont see it coming. –  haki Apr 18 '13 at 8:54
4  
@haki Really? This is the first I have heard of such a thing and I have been using ANSI SQL for years. It makes queries much more readable IMO. –  maple_shaft Apr 18 '13 at 10:52
5  
@haki ANSI/ISO joins are buggy in Oracle? They might were, in first implementation. I don't think so, in recent versions. –  ypercube Apr 18 '13 at 10:53
3  
@haki, good to have you on the site, we need some more Oracle DBAs :) care to drop into The Heap to chat? I'd like to hear more about bugs in ANSI joins, and I've got some more examples where you need them :) –  Jack Douglas Apr 18 '13 at 11:32
    
@Jack, Thanks for the warm welcome. I've had a lot of trouble with the new ANSI join syntax when creating materialized views. i am a DWH/DSS DBA so i handled a lot of MV's - i just made a habit of not using them at all. i agree it's more readable and i use them for everyday queries. –  haki Apr 19 '13 at 8:10

2 Answers 2

up vote 3 down vote accepted

There are actually several ranking functions you can use. You can read at the Analytic functions at Oracle documentation. In your case, RANK() and DENSE_RANK() would work, if I have understood you:

select *
from
  ( select uc.*,
           DENSE_RANK() OVER (PARTITION BY ac.HEALTHSYSTEMID
                              ORDER BY ac.ACCESSLEVELTYPE ASC) 
             AS drnk
    from usercredential uc
      inner join users u 
              on u.userid = uc.userid
      inner join credential c 
              on c.credentialid = uc.credentialid
      inner join healthsystemcredential hsc 
              on hsc.credentialid = c.credentialid
      inner join accesslevel ac 
              on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID 
    where c.fulfillmentmethod = 'Attach'
    and c.display = 1
    and uc.auditstatus <> 0
    and ( (uc.auditstatus is null) 
       or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) 
        ) 
  ) tmp
where drnk = 1 ;

The condition on drnk = 1 (lowest Type Rank) will be applied of course after the other conditions in the internal where are applied. If you want to first filter on the lowest Type Rank (this may give you less rows in the final result), you can either group by in a derived table and then join:

select uc.*
from usercredential uc
  inner join users u 
          on u.userid = uc.userid
  inner join credential c 
          on c.credentialid = uc.credentialid
  inner join healthsystemcredential hsc 
          on hsc.credentialid = c.credentialid
  inner join ( SELECT HEALTHSYSTEMID,
                      MIN(ACCESSLEVELTYPE) AS ACCESSLEVELTYPE 
               FROM accesslevel 
               GROUP BY HEALTHSYSTEMID
             ) ac 
          on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID 
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null) 
   or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) 
    ) ;

or use a subquery in the ON clause. This is closer to your try but quite complicated. SQL-Server has CROSS APPLY which makes this kind of joins more easy to write:

select uc.*
from usercredential uc
  inner join users u 
          on u.userid = uc.userid
  inner join credential c 
          on c.credentialid = uc.credentialid
  inner join healthsystemcredential hsc 
          on hsc.credentialid = c.credentialid
  inner join accesslevel ac
          on ac.HEALTHSYSTEMID = hsc.HEALTHSYSTEMID
         and ac.ACCESSLEVELTYPE = 
             ( SELECT MIN(aci.ACCESSLEVELTYPE) AS ACCESSLEVELTYPE
               FROM accesslevel aci
               WHERE aci.HEALTHSYSTEMID = hsc.HEALTHSYSTEMID              
             )
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null) 
   or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) 
    ) ;

Tried at SQL-Fiddle. You can test with data in the tables, to see which one fits your requirements and whether the joins should be on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID or with some other column.

share|improve this answer

I feel that ypercube had a good answer that explained DENSE_RANK and how this can be used in relation to my query, so I have decided to accept his answer, but I want to post an additional answer detailing what I ended up doing.

Ultimately I think that I was able to accomplish this without the use of an analytic function. Here is the query I have come up with below:

with minimumaccessleveltype as (
  select ac_.accessleveltype as accessleveltype from vs.accesslevel ac_
  where ac_.accesslevelid = 3
)
select uc.*
from vs.usercredential uc
inner join vs.users u on u.userid = uc.userid
inner join vs.credential c on c.credentialid = uc.credentialid
inner join vs.healthsystemcredential hsc on hsc.credentialid = c.credentialid
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null) or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) )
and uc.lastmodified > TO_DATE('01-APR-2013','DD-MON-YYYY')
and exists (select null
  from vs.accesslevel ac
  where ac.healthsystemid = hsc.healthsystemid
  and hsc.accesslevelid = ac.accesslevelid
  and ac.accessleveltype <= (select accessleveltype from minimumaccessleveltype)
  )
and hsc.healthsystemid = 3

Notice that I have example values for my default filters, this was to demonstrate that the query actually returns and filters out data that I expect.

AccessLevel ID 3: AccessLevelType = 6

AccessLevel ID 2: AccessLevelType = 4

AccessLevel ID 1: AccessLevelType = 1

I have one UserCredential record that meets the additional filtering and points to a Credential/HealthSystemCredential with an AccessLevel ID of 2. When filtering on AccessLevel ID of 3 and 2 I expect this record to be returned. When filtering on AccessLevel ID of 1 then the Type is too low and I expect it to be filtered out.

The query above seems to pass my unit test expectations so I am satisfied with this solution. The Explain Plan has a low cost and it is fast. The WITH clause will only ever return 1 record so even a CARTESIAN against this wouldn't affect the query.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.