Home » SQL & PL/SQL » SQL & PL/SQL » Need a Stored Procedure
Need a Stored Procedure [message #640839] Wed, 05 August 2015 13:55 Go to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
Hi All,

I have created tables as below
CREATE TABLE R_SHEET
(
L_CODE varchar2(6),
BATCH VARCHAR2(6),
R1 VARCHAR2(4),
R1_Flag varcahr2(1),
S1_A VARCHAR2(2),
S1_B VARCHAR2(2),
S1_C VARCHAR2(2),
S1_D VARCHAR2(2),
S1_FLAG VARCHAR2(1),
R2 VARCHAR2(4)
R2_Flag varcahr2(1),
S2_A VARCHAR2(2),
S2_B VARCHAR2(2),
S2_C VARCHAR2(2),
S2_D VARCHAR2(2),
S2_FLAG VARCHAR2(1),
R3 VARCHAR2(4)
R3_Flag varcahr2(1),
S3_A VARCHAR2(2),
S3_B VARCHAR2(2),
S3_C VARCHAR2(2),
S3_D VARCHAR2(2),
S3_FLAG VARCHAR2(1),
R4 VARCHAR2(4),
R4_Flag varcahr2(1),
S4_A VARCHAR2(2),
S4_B VARCHAR2(2),
S4_C VARCHAR2(2),
S4_D VARCHAR2(2),
S4_FLAG VARCHAR2(1),
C_CODE VARCHAR2(2),
C_CODE_FLAG1 VARCHAR2(1),
C_CODE_FLAG2 VARCHAR2(1),
C_CODE_FLAG3 VARCHAR2(1),
C_CODE_FLAG4 VARCHAR2(1),
C_CODE1 VARCHAR2(1),
C_CODE2 VARCHAR2(1),
C_CODE3 VARCHAR2(1),
C_CODE4 VARCHAR2(1)
);

I have entered the data as follows.

insert into R_SHEET( batch,l_code,r1,s1_a,s1_b,s1_c,s1_d,s2_a,s2_b,s2_c,s2_d) 
  values ('123456', '101202','1234','0','0','0','0','0','0','0','0');

insert into R_SHEET( batch,l_code,r1,s1_a,s1_b,s1_c,s1_d,s2_a,s2_b,s2_c,s2_d,s3_a,s3_b,s3_c,s3_d)
  values('234567','987612','5678','0','0','0','0','2','2','2','2','5','4','2','1');

insert into R_SHEET( batch,l_code,r1,s1_a,s1_b,s1_c,s1_d,s2_a,s2_b,s2_c,s2_d,s3_a,s3_b,s3_c,s3_d)
  values('765432','212223','9968','3','1','5','6','3','5','4','6','0','1','2','3');

insert into R_SHEET( batch,l_code,r1,s1_a,s1_b,s1_c,s1_d,s2_a,s2_b,s2_c,s2_d,s3_a,s3_b,s3_c,s3_d,s4_a,s4_b,s4_c,s4_d)
  values('101298','354673','9999','3','1','5','6','3','5','4','6','5','4','2','1','0','1','2','3');

insert into R_SHEET( batch,l_code,r1,s1_a,s1_b,s1_c,s1_d,s2_a,s2_b,s2_c,s2_d,s3_a,s3_b,s3_c,s3_d,s4_a,s4_b,s4_c,s4_d)
  values('433098','144528','9968','1','2','3','4','2','3','5','5','6','1','2','5','6.5','6.5','6.5','6.5'); -- for this result it should show the error at R4

insert into R_SHEET( batch,l_code,r1,s1_a,s1_b,s1_c,s1_d,s2_a,s2_b,s2_c,s2_d,s3_a,s3_b,s3_c,s3_d,s4_a,s4_b,s4_c,s4_d)
  values('123476','987690','9999','','','','','','','',''); 





select * from R_SHEET

batch  l_code  r1    s1_a  s1_b  s1_c  s1_d  s2_a  s2_b  s2_c  s2_d  s3_a  s3_b  s3_c  s3_d  s4_a  s4_b  s4_c  s4_d
123456 101202  1234   0     0     0     0     0     0     0     0                                               
234567 987612  5678   0     0     0     0     2     2     2     2     5     4     2     1
765432 212223  9968   3     1     5     6     3     5     4     6     0     1     2     3
101298 354673  9999   3     1     5     6     3     5     4     6     5     4     2     1
433098 144528  9968   1     2     3     4     2     3     5     5     6     1     2     5     6.5   6.5  6.5    6.5
123476 987690  9999


Now here is our expected O/P :

1. If S1_a,s1_b,s1_c,s1_d and S2_a,S2_b,S2_c,S2_d are '0' then need to update R1_flag or R2_flag and S1 values and S2 values or S1_Flag and S2_Flag marked 'X'

2. if S1_a,s1_b,s1_c,s1_d ='0' and all S2 values are '2' then update C_COndition_Code1 = 'X' and all S1_flag values should be flagged as 'X'

3. if S1 and S2 have discrepant values and S3 values contains any one '0' then S3_flag should be flagged as 'X'

4. If S1 and S2 values have discrepancy and S3 has values and S4 has any one '0' then R4_Flag should be marked as 'X'

5. IF S1 abd S2 values have discrepancy and S3 have some values and S4 values are Invalid ( values Invalid means , S1/S2/S3/S4 values shouldn't be more than 6. but in the above case we have value 6.5 which is invalid)
then R4_Flag should be marked as 'X'

6. if s1/s2/s3/s4 all values are ' ' then R1_Flag and R2_Flag should be marked as 'X'

7. If S1 has perfect values ( each field value should be between 1-6) and c_condition_code1 has value and S2 values are blank then R2_FLAG should be marked.

8. if S1_A = 1 and S1_b&S1_C & S1_d are blank and S4 is having values then R1_Flag should be marked as 'X' and s1_B,S1_C,S1_D should also be marked as 'X'.

[/code]

I would like to wrote a Stored Procedure which will satisfy the above given requirements.
Please have a look and advice.






[Updated on: Wed, 05 August 2015 14:06]

Report message to a moderator

Re: Need a Stored Procedure [message #640841 is a reply to message #640839] Wed, 05 August 2015 15:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Please have a look and advice.
post your code & results

https://community.oracle.com/thread/3777505

[Updated on: Wed, 05 August 2015 15:50]

Report message to a moderator

Re: Need a Stored Procedure [message #640842 is a reply to message #640841] Wed, 05 August 2015 15:59 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
I Thought Orafaq and ORacle Forums are different. so just posted..

I didn't write code to post here.. can you advice any logic to write a procedure which will satissfy the given conditions.

Thanks

Report message to a moderator

Re: Need a Stored Procedure [message #640843 is a reply to message #640842] Wed, 05 August 2015 16:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>3. if S1 and S2 have discrepant values
>4. If S1 and S2 values have discrepancy
>5. IF S1 abd S2 values have discrepancy
what exactly is discrepency since SQL has no such term. How to determine in code?

some, many, most of "If statements" could be implemented in WHERE clause

post code that satisfies any 1 of the 8

Report message to a moderator

Re: Need a Stored Procedure [message #640844 is a reply to message #640843] Wed, 05 August 2015 16:40 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
Discrepancy means here...

for eg : If you see the values for 

R1      S1_A S1_B  S1_C   S1_D
1234      0    0    0      0

R2   S2_A S2_B  S2_C   S2_D
5678  2    2     2      2


if we compare S1_A , S1_B, S1_C,S1_D and S2_A,S2_B,S2_C,S2_D both values have gap is like 2. Means there is no discrepancy.

If teh gap between values is more than 2 ( like S1_A= 1 and S2_A = 4 ( means gap is more than 2)) then we can treat there is discrepancy between the values.



1. If S1_a,s1_b,s1_c,s1_d and S2_a,S2_b,S2_c,S2_d are '0' then need to update R1_flag or R2_flag and S1 values and S2 values or S1_Flag and S2_Flag marked 'X'

if s1_a = 0 and s1_b = 0 and s1_c =0 and s1_d=0 and s2_a=0 and s2_b=0 and s2_c = 0 and s2_d=0 then
    update r_sheet set  r1_flag = 'X' or re_flag ='X' or s1_flag='X' or s2_flag='X'  
        where s1_X=0 or S2_X = '0'; (


Report message to a moderator

Re: Need a Stored Procedure [message #640845 is a reply to message #640844] Wed, 05 August 2015 18:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post a single UPDATE statement that satisfies just one of the 8 listed requirements.

Report message to a moderator

Re: Need a Stored Procedure [message #640859 is a reply to message #640839] Thu, 06 August 2015 02:41 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Clearly columns S2_A, S2_B, S2_C, S2_D etc are intended to be numbers (as you are performing artithmetic operations on them), why on earth are you storing them as text?

Report message to a moderator

Re: Need a Stored Procedure [message #640861 is a reply to message #640844] Thu, 06 August 2015 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
vasudevaviswa wrote on Wed, 05 August 2015 22:40
Discrepancy means here...

1. If S1_a,s1_b,s1_c,s1_d and S2_a,S2_b,S2_c,S2_d are '0' then need to update R1_flag or R2_flag and S1 values and S2 values or S1_Flag and S2_Flag marked 'X'

if s1_a = 0 and s1_b = 0 and s1_c =0 and s1_d=0 and s2_a=0 and s2_b=0 and s2_c = 0 and s2_d=0 then
    update r_sheet set  r1_flag = 'X' or re_flag ='X' or s1_flag='X' or s2_flag='X'  
        where s1_X=0 or S2_X = '0'; (



That code won't compile - you can't use OR in the SET part of update statement. Do you really expect oracle to guess which column should actually be updated?
It seems that your current requirements are far too vague and you need to go back to the person who gave you them and clarify exactly what they want.

Report message to a moderator

Re: Need a Stored Procedure [message #640895 is a reply to message #640861] Thu, 06 August 2015 09:28 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
That's not code.. I just posted my intention .. what exactly am expecting.. Thats not the code part.

Report message to a moderator

Re: Need a Stored Procedure [message #640896 is a reply to message #640895] Thu, 06 August 2015 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post a single UPDATE statement that satisfies just one of the 8 listed requirements.

Report message to a moderator

Re: Need a Stored Procedure [message #640897 is a reply to message #640896] Thu, 06 August 2015 09:58 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
 
This is our expection for requirement 1.


IF s1_a='0' and s1_b='0' and s1_c='0' and s1_d='0' and s2_a='0' and s2_b='0' and s2_c='0' and s2_d='0' 
  then  
    UPDATE R1_FLAG ='X'
      and  R2_FLAG='X'
      and S1_FLAG = 'X'
      and S2_FLAG = 'X';
END IF;



Report message to a moderator

Re: Need a Stored Procedure [message #640898 is a reply to message #640897] Thu, 06 August 2015 10:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vasudevaviswa wrote on Thu, 06 August 2015 07:58
This is our expection for requirement 1.


IF s1_a='0' and s1_b='0' and s1_c='0' and s1_d='0' and s2_a='0' and s2_b='0' and s2_c='0' and s2_d='0' 
  then  
    UPDATE R1_FLAG ='X'
      and  R2_FLAG='X'
      and S1_FLAG = 'X'
      and S2_FLAG = 'X';
END IF;





UPDATE statement is invalid syntax!
After correcting the syntax, it would change EVERY row in the table.
Please try again.

Report message to a moderator

Re: Need a Stored Procedure [message #640899 is a reply to message #640895] Thu, 06 August 2015 10:07 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
vasudevaviswa wrote on Thu, 06 August 2015 15:28
That's not code.. I just posted my intention .. what exactly am expecting.. Thats not the code part.

So?
Saying you want a or b to be updated is meaningless whether it's intention or actual code.
You have to specify exactly what should be updated in what circumstances.

Report message to a moderator

Re: Need a Stored Procedure [message #640900 is a reply to message #640897] Thu, 06 August 2015 10:10 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
vasudevaviswa wrote on Thu, 06 August 2015 15:58
This is our expection for requirement 1.


IF s1_a='0' and s1_b='0' and s1_c='0' and s1_d='0' and s2_a='0' and s2_b='0' and s2_c='0' and s2_d='0' 
  then  
    UPDATE R1_FLAG ='X'
      and  R2_FLAG='X'
      and S1_FLAG = 'X'
      and S2_FLAG = 'X';
END IF;





I've got to ask, do you know how to write a valid update statement?

Report message to a moderator

Re: Need a Stored Procedure [message #640901 is a reply to message #640900] Thu, 06 August 2015 10:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I've got to ask, do you know how to write a valid update statement?
OBVIOUSLY NOT!

Report message to a moderator

Re: Need a Stored Procedure [message #640902 is a reply to message #640898] Thu, 06 August 2015 10:15 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
 
IF s1_a='0' and s1_b='0' and s1_c='0' and s1_d='0' and s2_a='0' and s2_b='0' and s2_c='0' and s2_d='0' 
  then  
    UPDATE r_sheet set R1_FLAG ='X'
      and  R2_FLAG='X'
      and S1_FLAG = 'X'
      and S2_FLAG = 'X';
END IF;


here we domn't need to verify the data manually. We are getting data from end user. Then we need to upload teh data into the table.

Once the loading is done.. based on s1,s2,s3 ,s4 values teh S fields and C_COde fields and and the remaining flag field should update automatically.

Thanks.

Report message to a moderator

Re: Need a Stored Procedure [message #640903 is a reply to message #640902] Thu, 06 August 2015 10:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Once the loading is done.. based on s1,s2,s3 ,s4 values teh S fields and C_COde fields and and the remaining flag field should update automatically.
Only in your dreams.
NOTHING ever happens "automatically".
EVERYTHING must be coded properly to obtain desired results.
I suggest that you tell your supervisor that this task needs to be assigned to someone who actually know how to write basic SQL

Report message to a moderator

Re: Need a Stored Procedure [message #640904 is a reply to message #640901] Thu, 06 August 2015 10:23 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
Here am positing some sample code which I have written to Validate S values and C_CODE for reference. But this code is not satisfying the above all requirement. Please have a look and advice..

/* validate S3 */
declare
  cursor c1 is
    select rowid from R_SHEET
     where pnb = gpnb
     and clean_record is null 
     and (S3a not in ('0','1','2','3','4','5','6','15','25','35','45','55')
       or S3b not in ('0','1','2','3','4','5','6','15','25','35','45','55')
       or S3c not in ('0','1','2','3','4','5','6','15','25','35','45','55')
       or S3d not in ('0','1','2','3','4','5','6','15','25','35','45','55')
       or (S3a = '0' and (S3b != '0' or S3c != '0' or S3d != '0'))
       or (S3b = '0' and (S3a != '0' or S3c != '0' or S3d != '0'))
       or (S3c = '0' and (S3a != '0' or S3b != '0' or S3d != '0'))
       or (S3d = '0' and (S3a != '0' or S3b != '0' or S3c != '0'))
       or (S3a is null and (S3b is not null or S3c is not null or S3d is not null or rater3 is not null or C_CODE3 is not null))
       or (S3b is null and (S3a is not null or S3c is not null or S3d is not null or rater3 is not null or C_CODE3 is not null))
       or (S3c is null and (S3a is not null or S3b is not null or S3d is not null or rater3 is not null or C_CODE3 is not null))
       or (S3d is null and (S3a is not null or S3b is not null or S3c is not null or rater3 is not null or C_CODE3 is not null)) 
       or (to_number(decode(S1,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1  
           and (S3a is null or S3b is null or S3c is null or S3d is null))
       or (to_number(decode(S2,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1
           and (S3a is null or S3b is null or S3c is null or S3d is null))
       or (to_number(decode(S1b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1                         
           and (S3a is null or S3b is null or S3c is null or S3d is null))
       or (to_number(decode(S2b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1
           and (S3a is null or S3b is null or S3c is null or S3d is null))
       or (to_number(decode(S1c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1  
           and (S3a is null or S3b is null or S3c is null or S3d is null))
       or (to_number(decode(S2c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1
           and (S3a is null or S3b is null or S3c is null or S3d is null))
       or (to_number(decode(S1,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1  
           and (S3a is null or S3b is null or S3c is null or S3d is null))
       or (to_number(decode(S2d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1
           and (S3a is null or S3b is null or S3c is null or S3d is null))
      /* or ((S1 = '0' or S1b = '0' or S1c = '0' or S1d = '0' or 
            S2 = '0' or S2b = '0' or S2c = '0' or S2d = '0') and
           (S3a is null or S3b is null or S3c is null or S3d is null))*/
       or (abs(to_number(decode(S1,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is not null or S3b is not null or S3c is not null or S3d is not null))
       or (abs(to_number(decode(S2,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is not null or S3b is not null or S3c is not null or S3d is not null))               
        or (abs(to_number(decode(S1b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is not null or S3b is not null or S3c is not null or S3d is not null))
        or (abs(to_number(decode(S2b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is not null or S3b is not null or S3c is not null or S3d is not null))           
        or (abs(to_number(decode(S1c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is not null or S3b is not null or S3c is not null or S3d is not null))
        or (abs(to_number(decode(S2c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is not null or S3b is not null or S3c is not null or S3d is not null))           
        or (abs(to_number(decode(S1d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is not null or S3b is not null or S3c is not null or S3d is not null))
        or (abs(to_number(decode(S2d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is not null or S3b is not null or S3c is not null or S3d is not null))); 
     
     hld_rowid   varchar2(25);
     iteration_cnt number(3) := 0 ;
begin
  open c1;
  loop
    fetch c1 into hld_rowid;
    exit when c1%notfound;
       update R_SHEET
       set S3_flag = 'X'
       where rowid = hld_rowid;
    iteration_cnt := iteration_cnt + 1;
    if iteration_cnt > 99 then
      commit;
      iteration_cnt := 0 ;
    end if;
  end loop;
  close c1;
end;

commit;

/* Validating S4 with S3 */


declare
  cursor c1 is
    select rowid from R_SHEET
     where pnb = gpnb
     and clean_record is null
     and S3_flag = 'X' 
     and ((S4a is not null and S4b is not null and S4c is not null and S4d is not null) 
     or  (S4a is  null and S4b is  null and S4c is  null and S4d is  null and C_CODE4 is not null));
     hld_rowid   varchar2(25);
     iteration_cnt number(3) := 0 ;
begin
  open c1;
  loop
    fetch c1 into hld_rowid;
    exit when c1%notfound;  
     update R_SHEET
       set S3_flag = null   
       where rowid = hld_rowid;
    iteration_cnt := iteration_cnt + 1;
    if iteration_cnt > 99 then
      commit;
      iteration_cnt := 0 ;
    end if;
  end loop;
  close c1;
end;

commit;

/* Validate S3 NULL */

declare
  cursor c1 is
    select rowid from R_SHEET
     where pnb = gpnb
     and clean_record is null 
     and (to_number(decode(S1,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1  
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
       or (to_number(decode(S2,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
       or (to_number(decode(S1b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1  
          and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
       or (to_number(decode(S2b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
       or (to_number(decode(S1c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1  
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
       or (to_number(decode(S2c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
       or (to_number(decode(S1,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1  
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
       or (to_number(decode(S2d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) > 1
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
      /* or ((S1 = '0' or S1b = '0' or S1c = '0' or S1d = '0' or 
            S2 = '0' or S2b = '0' or S2c = '0' or S2d = '0') and
           (S3a is null or S3b is null or S3c is null or S3d is null))*/
       or (abs(to_number(decode(S1,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
       or (abs(to_number(decode(S2,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag               
        or (abs(to_number(decode(S1b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
        or (abs(to_number(decode(S2b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1b,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag          
        or (abs(to_number(decode(S1c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
        or (abs(to_number(decode(S2c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1c,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag          
        or (abs(to_number(decode(S1d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S2d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is null and S3b is null and S3c is null and S3d is null)) and C_CODE3 is null  -- C_CODE3 error flag
        or (abs(to_number(decode(S2d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8)) -
           to_number(decode(S1d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))) <= 1
           and (S3a is null and S3b is null and S3c is null and S3d is null) and C_CODE3 is null);                
     hld_rowid   varchar2(25);
     iteration_cnt number(3) := 0 ;
begin
  open c1;
  loop
    fetch c1 into hld_rowid;
    exit when c1%notfound;
       update R_SHEET
       set S3_flag = null 
       where rowid = hld_rowid;
    iteration_cnt := iteration_cnt + 1;
    if iteration_cnt > 99 then
      commit;
      iteration_cnt := 0 ;
    end if;
  end loop;
  close c1;
end;

commit;


/* validate c_code1 */
declare
  cursor c1 is
    select rowid from R_SHEET
     where pnb = gpnb
     and clean_record is null
     and C_CODE1 is not null 
     and (C_CODE1 not in ('1','2','3','4','5')
          or (S1 > 0 and S1b > 0 and S1c > 0 and S1d > 0));
     hld_rowid   varchar2(25);
     iteration_cnt number(3) := 0;
begin
    open c1;
    loop
      fetch c1 into hld_rowid;
      exit when c1%notfound;
        update R_SHEET
        set C_CODE_flag1 = 'X'
        where rowid = hld_rowid;
        iteration_cnt := iteration_cnt + 1;
        if iteration_cnt > 99 then
          commit;
          iteration_cnt := 0;       
      end if;
    end loop;
    close c1;
end;            
    
commit;


/* Validating C_Code1 Null*/

declare
  cursor c1 is
    select rowid from R_SHEET
     where pnb = gpnb
     and clean_record is null 
     and C_CODE1 is null 
     and ((S1 = 0 and S1b = 0 and S1c = 0 and S1d = 0) or (S1 is null and S1b is null and S1c is null and S1d is null)) ;          
     hld_rowid   varchar2(25);
     iteration_cnt number(3) := 0;
begin
    open c1;
    loop
      fetch c1 into hld_rowid;
    exit when c1%notfound;
      update R_SHEET
      set C_CODE_flag1 = 'X'
      where rowid = hld_rowid;
      iteration_cnt := iteration_cnt + 1;
      if iteration_cnt > 99 then
        commit;
        iteration_cnt := 0;
      end if;
    end loop;
    close c1;
end;

commit;

Report message to a moderator

Re: Need a Stored Procedure [message #640905 is a reply to message #640904] Thu, 06 August 2015 10:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Here am positing some sample code which I have written
I don't believe that YOU wrote posted code.

post a single UPDATE statement that satisfies just one of the 8 listed requirements.

Report message to a moderator

Re: Need a Stored Procedure [message #640906 is a reply to message #640902] Thu, 06 August 2015 10:26 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
I was serious when I asked if you know how to write a valid update.

You need to understand - no-one is going to write this procedure for you - if you want that you need to hire a contractor and pay them.

We will quite happily show someone where they are going wrong and/or point them towards the correct approach to the problem.

We are struggling to do that with your problem because:
a) Your specification isn't clear enough
b) You haven't posted any valid code, and several examples of invalid code - leading us to wonder if you have any knowledge of how to write valid SQL and PL/SQL. If you lack that knowledge then you need to go do a few courses and / or start reading the manuals before we can really help you at all.

Report message to a moderator

Re: Need a Stored Procedure [message #640907 is a reply to message #640906] Thu, 06 August 2015 10:31 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
And suddenly valid looking code appears.

Trouble is:
a) your specification is still vague.
b) You haven't told which of the requirements that code fails to satisfy.

Past that - using cursors and loop is pointless. Just write a single update statement for each criteria.

Report message to a moderator

Re: Need a Stored Procedure [message #640908 is a reply to message #640903] Thu, 06 August 2015 10:44 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
Appreciate your reply...

Thanks.

Report message to a moderator

Re: Need a Stored Procedure [message #640909 is a reply to message #640904] Thu, 06 August 2015 10:45 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
n/a

Report message to a moderator

Re: Need a Stored Procedure [message #640910 is a reply to message #640909] Thu, 06 August 2015 11:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post a single UPDATE statement that satisfies just one of the 8 listed requirements.

Report message to a moderator

Re: Need a Stored Procedure [message #640911 is a reply to message #640910] Thu, 06 August 2015 11:15 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Personally I'd write this:
to_number(decode(S2d,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,'8',8,'9',9,'*',8))

as
CASE WHEN s2d = '*' THEN 8 ELSE to_number(s2d) END

It's easier to see at a glance what you're trying to do.

Report message to a moderator

Re: Need a Stored Procedure [message #640916 is a reply to message #640911] Thu, 06 August 2015 12:15 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
Swan,

I got the CASE statement. But I didn't get how can we implement in the above given piece of code. Because need to implement this logic (CASE logic for S1,S2,S3 and S4's.. ).. can you show me me the logic for some piece how can we implement.. so i can work on the remaining code..

Thanks

Report message to a moderator

Re: Need a Stored Procedure [message #640919 is a reply to message #640916] Thu, 06 August 2015 13:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vasudevaviswa wrote on Thu, 06 August 2015 10:15
Swan,

I got the CASE statement. But I didn't get how can we implement in the above given piece of code. Because need to implement this logic (CASE logic for S1,S2,S3 and S4's.. ).. can you show me me the logic for some piece how can we implement.. so i can work on the remaining code..

Thanks


I never mentioned CASE, so you are asking the wrong person.
IMO, CASE is not needed to post a single UPDATE statement that satisfies just one of the 8 listed requirements.

Report message to a moderator

Re: Need a Stored Procedure [message #640920 is a reply to message #640919] Thu, 06 August 2015 13:15 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
Oh,.. ok am sorry for that. Can you post me some sample code which will sove my issues.. ( please advice me the logic in the above given code..)

Report message to a moderator

Re: Need a Stored Procedure [message #640921 is a reply to message #640897] Thu, 06 August 2015 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vasudevaviswa wrote on Thu, 06 August 2015 07:58
This is our expection for requirement 1.


IF s1_a='0' and s1_b='0' and s1_c='0' and s1_d='0' and s2_a='0' and s2_b='0' and s2_c='0' and s2_d='0' 
  then  
    UPDATE R1_FLAG ='X'
      and  R2_FLAG='X'
      and S1_FLAG = 'X'
      and S2_FLAG = 'X';
END IF;



answer the 2 questions below regarding UPDATE statement above
1) which table is changed?
2) which rows get changed?

Report message to a moderator

Re: Need a Stored Procedure [message #640922 is a reply to message #640921] Thu, 06 August 2015 13:37 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
1. R_SHEET table is going to update

2. the code need to compare the values like s1_a to s2_a and s1_b to s2_b etc..

if all S1 values and all S2 values are '0' then R1_FLAG or R2_FLAG or both S1_FLAG or S2_FLAG flagged as 'X'

this is the first requirement.

Let me know if its not clear.


IF s1_a='0' and s1_b='0' and s1_c='0' and s1_d='0' and s2_a='0' and s2_b='0' and s2_c='0' and s2_d='0' 
  then  
    UPDATE r_sheet set R1_FLAG ='X'
      
      where  batch = 'gpnb'
             
      ;
END IF;



Report message to a moderator

Re: Need a Stored Procedure [message #640923 is a reply to message #640922] Thu, 06 August 2015 15:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>UPDATE r_sheet set R1_FLAG ='X'
>where batch = 'gpnb'
do you really want to set R1_FLAG ='X' for EVERY row where batch = 'gpnb' to be changed?
Because that is what the UPDATE above will do.

Report message to a moderator

Re: Need a Stored Procedure [message #640924 is a reply to message #640923] Thu, 06 August 2015 15:50 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
I mean if s1_a,s1_b,s1_c,s1_d and s2_a,s2_b,s2_c,s2_d are '0' for those batches the value should be marked as 'X' as I mentioned below.

Report message to a moderator

Re: Need a Stored Procedure [message #640925 is a reply to message #640924] Thu, 06 August 2015 15:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vasudevaviswa wrote on Thu, 06 August 2015 13:50
I mean if s1_a,s1_b,s1_c,s1_d and s2_a,s2_b,s2_c,s2_d are '0' for those batches the value should be marked as 'X' as I mentioned below.


then they need to be part of the UPDATE statement itself.

Report message to a moderator

Re: Need a Stored Procedure [message #640930 is a reply to message #640925] Thu, 06 August 2015 18:13 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
Can you advice me the cse statement for the belwo condition..


for R1

  S1_A = 1 and there are no values for S1_B,S1_C and S1_D

FOr R2 
   S2_A=1  S2_B=1 S2_C = 1  S2_D =1

R3 and R4 have no values.


In this case it hould be update as R1_FLAG = 'X'..

can you please advice how can we write CASE statement.

Thanks.

Report message to a moderator

Re: Need a Stored Procedure [message #640940 is a reply to message #640930] Fri, 07 August 2015 03:03 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
I said you should use a specific case statement instead of a specific decode statement. I didn't say you should use case in general.

You've got code of the form:
CURSOR c IS
SELECT rowid
FROM r_sheet
WHERE <conditions>

BEGIN

open c;
loop
  fetch c into <variable>
  exit when c%notfound;
  update r_sheet
  set <column(s)> = <value(s)>
  WHERE rowid = <variable>
  
  ......
  
end loop;
close c;
END;


This can be rewritten as:
  update r_sheet
  set <column(s)> = <value(s)>
  where < full where clause from cursor>;

Report message to a moderator

need to build Stored Procedure [message #641277 is a reply to message #640839] Wed, 12 August 2015 16:39 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
Hi All,

I have created Oracle table as follows.
CREATE TABLE R_SHEET
(
L_CODE varchar2(6),
BATCH VARCHAR2(6),
R1 VARCHAR2(4),
R1_Flag varcahr2(1),
S1_A VARCHAR2(2),
S1_B VARCHAR2(2),
S1_C VARCHAR2(2),
S1_D VARCHAR2(2),
S1_FLAG VARCHAR2(1),
R2 VARCHAR2(4)
R2_Flag varcahr2(1),
S2_A VARCHAR2(2),
S2_B VARCHAR2(2),
S2_C VARCHAR2(2),
S2_D VARCHAR2(2),
S2_FLAG VARCHAR2(1),
R3 VARCHAR2(4)
R3_Flag varcahr2(1),
S3_A VARCHAR2(2),
S3_B VARCHAR2(2),
S3_C VARCHAR2(2),
S3_D VARCHAR2(2),
S3_FLAG VARCHAR2(1),
R4 VARCHAR2(4),
R4_Flag  varchar2(1),
S4_A VARCHAR2(2),
S4_B VARCHAR2(2),
S4_C VARCHAR2(2),
S4_D VARCHAR2(2),
S4_FLAG VARCHAR2(1),
C_CODE VARCHAR2(2),
C_CODE_FLAG1 VARCHAR2(1),
C_CODE_FLAG2 VARCHAR2(1),
C_CODE_FLAG3 VARCHAR2(1),
C_CODE_FLAG4 VARCHAR2(1),
C_CODE1 VARCHAR2(1),
C_CODE2 VARCHAR2(1),
C_CODE3 VARCHAR2(1),
C_CODE4 VARCHAR2(1)
);


Here are the sample insert statements.
1. insert into r_sheet(s1_1,s1_b,s1_c,s1_d,s2_1,s2_b,s2_c,s2_d,c_code4) values ('1','1','1','1','5','5','5','5','3');

2. insert into r_sheet(s1_1,s1_b,s1_c,s1_d,s2_1,s2_b,s2_c,s2_d,c_code3,s4_a,s4_b,s4_c,s4_d) values('1','1','1','1','5','5','5','5','2','3','3','3','3');

3. insert into r_sheet(s1_1,s1_b,s1_c,s1_d,s2_1,s2_b,s2_c,s2_d,c_code3,c_code4)  values('1','1','1','1','5','5','5','5','2','3');

4. insert into r_sheet(s1_1,s1_b,s1_c,s1_d,s2_1,s2_b,s2_c,s2_d) values('1','1','1','1','5','5','5','5');

5. insert into r_sheet(c_code1,c_code2) values('1','1') ; -- Need tp flag C_CODE1='X';


Here is teh data from the above stements and some other existed data
 s1_a  s1_b  s1_c   s1_d   s2_a   s2_b   s2_c  s2_d  s3_a  s3_b  s3_c  s3_d  s4_a  s4_b  s4_c  s4_d s1_flag s2_flag s3_flag s4_flag c_code1  c_code2  c_code3  c_code4  c_code_flag1  c_code_flag2  c_code_flag3 _code_flag4

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1       1     1      1      5      5      5      5       

1       1     1      1      1      1      1      1                             5    5     5      5                                                      5

1       1     1      1      1      1      1      1                                                                                                      1         2

                                                      4     4     4      4     5    5      5     5                                     1          1    
1       1     1      1	    1      1      1      1    4     5     6      3
1       1     1      1	    2      2      2      2    4     5     6      3

etc...


Here is the required output.

1. if S1 values are '1111' and S2 values are '1111' and S3 values are '4563' then need to update flag S3_FLAG = 'X'

2. if S1 values are '1111' and S2 values are '2222' and S3 values are '4563' then need to flag S3_FLAG = 'X'

3. if S1 values are '1111' and S2 values are '3333' and S3 values are '2222' and S4 values are '5.5 4.5 3.5 2.5' then need to update Clean_Record = 'Y'

4. if S1 are '1111' and S2 are '1111' and S3 are '4444' and C_CODE4 ='1' then need to update S3_FLAG = 'X'

5 if S1 are '1111' and S2 are '1111' and C_CDE3 =' 1' and S4 are '5555' then need to update S3_FLAG='X'

6. if C_CODE1='1' and C_CODE2='1' and c_code3 = '2' then score3_flag='X'

7. if S1 '1111' and S2 '5555' and C_CODE4='3' then need to update score3_flag = 'X'

[/code]

Can any one please advice.

Thanks,
Vasudev

Report message to a moderator

Re: need to build Stored Procedure [message #641278 is a reply to message #641277] Wed, 12 August 2015 16:53 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Tell us what level you're at. Can you write a basic update statement?
Can you write a basic pl/sql block?
Can you put, in that basic pl/sql block, a basic case statement?
Have you made any attempts so far to achieve this yourself?
What did you try?

Report message to a moderator

Re: Need a Stored Procedure [message #641279 is a reply to message #640940] Wed, 12 August 2015 16:53 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
can any one please advice me a query to which will satisfy the below conditions. unnecessarily created another thread.

[code]

1. if S1 values are '1111' and S2 values are '1111' and S3 values are '4563' then need to update flag S3_FLAG = 'X'

2. if S1 values are '1111' and S2 values are '2222' and S3 values are '4563' then need to flag S3_FLAG = 'X'

3. if S1 values are '1111' and S2 values are '3333' and S3 values are '2222' and S4 values are '5.5 4.5 3.5 2.5' then need to update Clean_Record = 'Y'

4. if S1 are '1111' and S2 are '1111' and S3 are '4444' and C_CODE4 ='1' then need to update S3_FLAG = 'X'

5 if S1 are '1111' and S2 are '1111' and C_CDE3 =' 1' and S4 are '5555' then need to update S3_FLAG='X'

6. if C_CODE1='1' and C_CODE2='1' and c_code3 = '2' then score3_flag='X'

7. if S1 '1111' and S2 '5555' and C_CODE4='3' then need to update score3_flag = 'X'

[code]

Report message to a moderator

Re: Need a Stored Procedure [message #641280 is a reply to message #641279] Wed, 12 August 2015 16:56 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Threads merged.
please answer my questions

Report message to a moderator

Re: Need a Stored Procedure [message #641281 is a reply to message #641280] Wed, 12 August 2015 17:06 Go to previous messageGo to previous message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
Please have a look into below statement which have written for previous conditions. here are my previous conditions.

[code]
1. If S1_a,s1_b,s1_c,s1_d and S2_a,S2_b,S2_c,S2_d are '0' then need to update R1_flag or R2_flag and S1 values and S2 values or S1_Flag and S2_Flag marked 'X'

2. if S1_a,s1_b,s1_c,s1_d ='0' and all S2 values are '2' then update C_COndition_Code1 = 'X' and all S1_flag values should be flagged as 'X'

3. if S1 and S2 have discrepant values and S3 values contains any one '0' then S3_flag should be flagged as 'X'

4. If S1 and S2 values have discrepancy and S3 has values and S4 has any one '0' then R4_Flag should be marked as 'X'

5. IF S1 abd S2 values have discrepancy and S3 have some values and S4 values are Invalid ( values Invalid means , S1/S2/S3/S4 values shouldn't be more than 6. but in the above case we have value 6.5 which is invalid)
then R4_Flag should be marked as 'X'

6. if s1/s2/s3/s4 all values are ' ' then R1_Flag and R2_Flag should be marked as 'X'

7. If S1 has perfect values ( each field value should be between 1-6) and c_condition_code1 has value and S2 values are blank then R2_FLAG should be marked.

8. if S1_A = 1 and S1_b&S1_C & S1_d are blank and S4 is having values then R1_Flag should be marked as 'X' and s1_B,S1_C,S1_D should also be marked as 'X'.

and the code is 
select l_code,batch,r1,
       case when s1_a = '1'
             and rpad(s1_b,2)||rpad(s1_c,2)||rpad(s1_d,2) = rpad(' ',8)
             and coalesce(s4_a,s4_b,s4_c,s4_d) is not null
            then 'X'  -- rule 8
            when rpad(s1_a,2)||rpad(s1_b,2)||rpad(s1_c,2)||rpad(s1_d,2)||
                 rpad(s2_a,2)||rpad(s2_b,2)||rpad(s2_c,2)||rpad(s2_d,2)||
                 rpad(s3_a,2)||rpad(s3_b,2)||rpad(s3_c,2)||rpad(s3_d,2)||
                 rpad(s4_a,2)||rpad(s4_b,2)||rpad(s4_c,2)||rpad(s4_d,2) = rpad(' ',32)
            then 'X'  -- rule 6
       end R1_FLAG,
       s1_a,
       case when s1_a = '1'
             and rpad(s1_b,2)||rpad(s1_c,2)||rpad(s1_d,2) = rpad(' ',8)
             and coalesce(s4_a,s4_b,s4_c,s4_d) is not null
            then 'X'  -- rule 8
       end s1_b,
       case when s1_a = '1'
             and rpad(s1_b,2)||rpad(s1_c,2)||rpad(s1_d,2) = rpad(' ',8)
             and coalesce(s4_a,s4_b,s4_c,s4_d) is not null
            then 'X'  -- rule 8
       end s1_c,
       case when s1_a = '1'
             and rpad(s1_b,2)||rpad(s1_c,2)||rpad(s1_d,2) = rpad(' ',8)
             and coalesce(s4_a,s4_b,s4_c,s4_d) is not null
            then 'X'  -- rule 8
       end s1_d,
       case when replace(s1_a||s1_b||s1_c||s1_d,' ') = '0000'
             and replace(s2_a||s2_b||s2_c||s2_d,' ') = '2222'
            then 'X'  -- rule 2
            when replace(s1_a||s1_b||s1_c||s1_d,' ') = '0000'
             and replace(s2_a||s2_b||s2_c||s2_d,' ') = '0000'
            then 'X'  -- rule 1
       end S1_FLAG,
       R2,
       case when translate(trim(s1_a)||trim(s1_b)||trim(s1_c)||trim(s1_d),'~123456','~') is null
             and length(trim(s1_a)||trim(s1_b)||trim(s1_c)||trim(s1_d)) = 4
             and c_code1 is not null
             and rpad(s2_a,2)||rpad(s2_b,2)||rpad(s2_c,2)||rpad(s2_d,2) = rpad(' ',8)
            then '!'  -- rule 7
            when rpad(s1_a,2)||rpad(s1_b,2)||rpad(s1_c,2)||rpad(s1_d,2)||
                 rpad(s2_a,2)||rpad(s2_b,2)||rpad(s2_c,2)||rpad(s2_d,2)||
                 rpad(s3_a,2)||rpad(s3_b,2)||rpad(s3_c,2)||rpad(s3_d,2)||
                 rpad(s4_a,2)||rpad(s4_b,2)||rpad(s4_c,2)||rpad(s4_d,2) = rpad(' ',32)
            then 'X'  -- rule 6
       end R2_FLAG,
       s2_a,s2_b,s2_c,s2_d,
       case when replace(s1_a||s1_b||s1_c||s1_d,' ') = '0000'
             and replace(s2_a||s2_b||s2_c||s2_d,' ') = '0000'
            then 'X'  -- rule 1
       end S2_FLAG,
       R3,
       R3_FLAG,
       s3_a,s3_b,s3_c,s3_d,
       case when (s1_a != s2_a or s1_b != s2_b or s1_c != s2_c or s1_d != s2_d)
             and instr(s3_a||s3_b||s3_c||s3_d,'0') > 0
            then 'X'  --rule 3
       end S3_FLAG,
       R4,
       case when (s1_a != s2_a or s1_b != s2_b or s1_c != s2_c or s1_d != s2_d)
             and coalesce(s3_a,s3_b,s3_c,s3_d) is not null
             and instr(s4_a||s4_b||s4_c||s4_d,'0') > 0
            then 'X'  --rule 4
            when (s1_a != s2_a or s1_b != s2_b or s1_c != s2_c or s1_d != s2_d)
             and coalesce(s3_a,s3_b,s3_c,s3_d) is not null
             and translate(trim(s4_a)||trim(s4_b)||trim(s4_c)||trim(s4_d),'~123456','~') is not null
             and length(trim(s4_a)||trim(s4_b)||trim(s4_c)||trim(s4_d)) = 4
            then 'X'  --rule 5
       end R4_FLAG,
       s4_a,s4_b,s4_c,s4_d,
       S4_FLAG,
       C_CODE,
       C_CODE_FLAG1,
       C_CODE_FLAG2,
       C_CODE_FLAG3,
       C_CODE_FLAG4,
       case when when replace(s1_a||s1_b||s1_c||s1_d,' ') = '0000'
             and replace(s2_a||s2_b||s2_c||s2_d,' ') = '2222'
            then 'X'  -- rule 2
       end C_CODE1,
       C_CODE2,
       C_CODE3,
       C_CODE4
  from r_sheet


for each condition have written update statement from the above statement.

now here are my requirements as I posted in my thread.

if I try to change/implement anything it is effecting the previous conditions.
SO need suggestions from you all to implement the logic.

Report message to a moderator

Previous Topic: Need a Query to transpose rows to columns dynamically
Next Topic: Exact String Match in Instr
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 06:08:17 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.