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.

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?

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.
–
hakiApr 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_shaftApr 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.
–
ypercubeApr 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.
–
hakiApr 19 '13 at 8:10

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:

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:

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.