SQL & PL/SQL

Hi,

I am working on Oracle 11.2.0.3

Below are my table structures.

{code}

Table 1: R_Sheets

here is the column_List

Table 2: R_IDS

======

R_ID  ( Contains data for R1,R2,R3 )

Fname

LName

Contact

{code}

{code}

Here R1,R2,R3,R4 has 4 scores and C_Codes and C_Code_Flags.

Eg : R1 has R1_S1_A , R1_S1_B, R1_S1_C, R1_S1_D ,C_CODE_FLAG1,C_CODE1

       R2 has R2_S2_A, R2_S2_B, R2_S2_C, R2_S2_D, C_COde_Flag2, C_COde2

       same for R3 and R4 has S3 and S4 values and teh C_codes too

Now here we need to write a Procedure

Case 1:  If R1 all scores = 0

                 R2 all scores = 0  and there is no C_CODE or C_CODE_FLAg then R1 and R2 scores should be flagged as 'X'

Case 2:  If R1 all scores = 0

                 R2 has scores

                 R3 has scores then

                 R1 scores should be flagged as 'X' else C_CODE_FLAG1 should be flagged as 'X'

Case 3 :  IF there is discripency in R1 and R2 scores and R3 having any score value as '0'

                ( eg : R1 score values for S1-A,B,C,D as 3,1,5,6

                        R2 scores as 3,5,4,6

                        R3 scores as 0,1,2,3)

              Then "The presence of a zero, along with score values (1-6) causes that 3 score to be flagged (R3 in this case)."

Case 4 :

           If R1, R2 have discripencies and R3 has scores and R4 has one '0' then R4 scores should be flagged as 'X'

Case 5 :

       If R1,R2 having descripencies and R3 having scores and R4 having Invalid value like 6.7,6..6,6.7,6.5 ( which should not be more than 6)  then  R4 scores should be flagged as 'X'

Case 6 :

      if all blank for R1,R2,R3 then R1 and R2 scores should be flagged as 'X'

Case 7 :

               if R1 scores are blank and R1's C_CODE1 value as '2' and R2 scores are blank then R2 scores should be flagged  as 'X'

Case 8 :

             If R1 has scores and R2 has C_CODe2=5 then R3 scores should be flagegd as 'X'

Case 9 :  if R1 and R2's score values all are '1' and R3 scores are blank and has C_CODE3 = 1 then

                           R3 scores should be flagged as 'X'

Case 10 :

                If R1 has scores like 1,1,1,1 and R2 has scores like 5,5,5,5  then all score values of R3 should be flagged as 'X'

{code}

Let me know if you need more information. Please advice.

Thanks,

Vas