RowNum in VIEW

[RustyAfro]

Oracle 8i

Hi everyone,

I created a View that adds RowNum to an existing table as shown below.

When I join to this new view from another table, I was expecting to get the RANK column for each primary key in the other table that has a one-to-many relationship to this view.

I didn't realize but when joining to this view, the view seems to run first for all records, and then joins to the table. So key 134 might have a rank of 9325.

Is there anyway to get the join to the view to behave as I need? I really need a view I can join to for another tool I am using.

The data needs to look like below.

table.Key  View.Key   View.Code View.RANK
---------  ---------  --------- ---------
134        134        ABC       1
134        134        DWF       2
134        134        DRT       3
253        253        KFD       1
253        253        TTA       2

create or replace view bkb.UWVW_CPT300_ROWID as
SELECT 
        rownum as RANK
      , CPT300_SVC_DIMSN.*
FROM 
     "BKB"."CPT300_SVC_DIMSN" "CPT300_SVC_DIMSN" 
WHERE
     "CPT300_SVC_DIMSN"."CPT_CD" <> '.'
    AND "CPT300_SVC_DIMSN"."CPT_CD" <> '?'
    AND "CPT300_SVC_DIMSN"."CPT_CD" IS NOT NULL
ORDER BY 
rownum

 

[Dagon]

I'm a bit unclear what you're trying to do, but it looks as if you're trying to rank records within a subkey. You can't do this with rownum because it will just count all records.

You could use a ranking function:

create or replace view bkb.UWVW_CPT300_ROWID as
SELECT
RANK() over (partition by key1,key2,key3 order by 1) as ranking
, CPT300_SVC_DIMSN.*
FROM
"BKB"."CPT300_SVC_DIMSN" "CPT300_SVC_DIMSN"
WHERE
"CPT300_SVC_DIMSN"."CPT_CD" <> '.'
AND "CPT300_SVC_DIMSN"."CPT_CD" <> '?'
AND "CPT300_SVC_DIMSN"."CPT_CD" IS NOT NULL

 

[RustyAfro]

Dagon, that was very helpful (didn't know about Rank())

I changed my view as below and it works. However, when joining a table to it as shown in the last query below it runs very slow when just having a simple join between the view and the table and a WHERE clause on the joined table field.

If I also put the same WHERE clause on the joined VIEW field:
"AND BKB437_REV_CD_COV.MED_CTRL_DIMSN_KEY=42924094
AND uwvw_cpt300_rowid.MED_CTRL_DIMSN_KEY=42924094"
it runs instantly.

I know my way around SQL but I'm new to views and their behavior. I want the view to run once for each value in the join only. Unless I explicitly put a condition on a view field however, it seems to run the whole thing before joining.

Any tips?

create or replace view bkb.UWVW_CPT300_ROWID as

SELECT 
s1.med_ctrl_dimsn_key,
s1.cpt_svc_dimsn_key,
s1.rank,
s1.cpt_cd,
s1.cpt_des
FROM       
        (SELECT 
        Rank() Over (Partition by r.med_ctrl_dimsn_key Order By c.CPT_CD) Rank,
        r.med_ctrl_dimsn_key,
        c.cpt_svc_dimsn_key,
        c.CPT_CD,
        c.cpt_des        
         FROM BKB.BKB437_REV_CD_COV r,
              BKB.CPT300_SVC_DIMSN c 
         WHERE 
               c.cpt_svc_dimsn_key = r.cpt_svc_dimsn_key
           AND c.CPT_CD <> '.'
           AND c.CPT_CD <> '?'
           AND c.CPT_CD IS NOT NULL
           AND c.cpt_svc_dimsn_key = r.cpt_svc_dimsn_key
           ) S1

SELECT BKB437_REV_CD_COV.MED_CTRL_DIMSN_KEY
      , REV300_REV_DIMSN.REV_CD
      , REV300_REV_DIMSN.REV_DES
      , REV300_REV_DIMSN.CATEGORY_DES
      , REV300_REV_DIMSN.SECTION_DES
      , BKB437_REV_CD_COV.REV_CD_BILLED_AMT
      , uwvw_cpt300_rowid.CPT_SVC_DIMSN_KEY
      , uwvw_cpt300_rowid.RANK
      , uwvw_cpt300_rowid.CPT_CD
      , uwvw_cpt300_rowid.CPT_DES
FROM BKB.BKB437_REV_CD_COV BKB437_REV_CD_COV
      , BKB.REV300_REV_DIMSN REV300_REV_DIMSN
      , bkb.uwvw_cpt300_rowid uwvw_cpt300_rowid
WHERE (BKB437_REV_CD_COV.REV_CD_DIMSN_KEY   =REV300_REV_DIMSN.REV_CD_DIMSN_KEY) 
    AND (BKB437_REV_CD_COV.CPT_SVC_DIMSN_KEY=uwvw_cpt300_rowid.CPT_SVC_DIMSN_KEY) 
    AND (BKB437_REV_CD_COV.MED_CTRL_DIMSN_KEY=uwvw_cpt300_rowid.MED_CTRL_DIMSN_KEY)
    AND BKB437_REV_CD_COV.MED_CTRL_DIMSN_KEY=42924094     
ORDER BY REV300_REV_DIMSN.REV_CD
      , uwvw_cpt300_rowid.CPT_CD
      , BKB437_REV_CD_COV.REV_CD_BILLED_AMT DESC

 

[Dagon]

What you're doing doesn't make much sense to me. You're ranking BKB.BKB437_REV_CD_COV and then joining it back to itself. Why don't you just move the ranking function into the main query and scrap the view altogether ?

 

[RustyAfro]

I agree, it doesn't make much sense from a SQL standpoint.

The reason for the view though has to do with a complex Crystal Report I am making. Crystal auto generates SQL based on joins, so I am somewhat limited with what I can do, hence the attempt at a view. Their is a way to use pure SQL in Crystal but this solution must use the auto SQL method for internal reasons.

If a view solution isn't possible though, I can move on to some other ideas. Knowing why the View join behaves as mentioned above though would still be helpful for future View issues I may run into.

Thanks again ;-)