Home » SQL & PL/SQL » SQL & PL/SQL » Item Validation (oracle 11.g)
Item Validation [message #621485] Wed, 13 August 2014 13:13 Go to next message
diva_thilak
Messages: 5
Registered: November 2010
Junior Member
I am facing a challenge in getting an approach for solving this issue.


ID	Item Name	Item RC Code1	Item RC Code2	Item RC Code 3	Item RC Code 4	Item RC Code 5	Item RC Amt1	Item RC Amt2	Item RC Amt3	Item RC Amt3	Item RC Amt4	Item RC Amt5	Item CC Code1	Item CC Code2	Item CC Code 3	Item CC Code 4	Item CC Amt1	Item CC Amt2	Item CC Amt 3	Item CC Amt4
1234	Item-1	CC1	CC2	CC3			12	12	12				CC2				12			
1235	Item-2	CC1	CC2	CC5			12	17	18				CC2	CC5			17	16		


My requirement is to take each row and For all the Non-null CC codes (1-4), see if there is a matching RC code and Amount in the same row. If the amounts and codes match i need to populate balanced, if not it is outofbalance. In the above scenario the first record would be balanced and second one would be out of balance.

Can this be done in SQL or it has be through the PL/SQL block.
create table ITEMS (ITM_ID number(10), ITM_NME varchar(25),
ITM_RC_C1 VARCHAR(10),
ITM_RC_C2 varchar(10),
ITM_RC_C3 VARCHAR(10),
ITM_RC_C4 varchar(10),
ITM_RC_C5 varchar(10),
ITM_CC_C1 VARCHAR(10),
ITM_CC_C2 varchar(10),
ITM_CC_C3 varchar(10),
ITM_CC_C4 varchar(10),
ITM_RC_AMT1 decimal(18,2),
ITM_RC_AMT2 decimal(18,2),
ITM_RC_AMT3 decimal(18,2),
ITM_RC_AMT4 decimal(18,2),
ITM_RC_AMT5 decimal(18,2),
ITM_CC_AMT1 decimal(18,2),
ITM_CC_AMT2 decimal(18,2),
ITM_CC_AMT3 decimal(18,2),
ITM_CC_AMT4 decimal(18,2))
 
insert into ITEMS value(1234,'Item-1','CC1','CC2','CC3','','','CC2','','','',12,12,12,0,0,12,0,0,0);
insert into items value(1235,'Item-2','CC1','CC2','CC5','','','CC2','CC5','','',12,17,18,0,0,12,17,0,0);
Re: Item Validation [message #621522 is a reply to message #621485] Thu, 14 August 2014 00:54 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
diva_thilak wrote on Wed, 13 August 2014 23:43

insert into ITEMS value(1234,'Item-1','CC1','CC2','CC3','','','CC2','','','',12,12,12,0,0,12,0,0,0);
insert into items value(1235,'Item-2','CC1','CC2','CC5','','','CC2','CC5','','',12,17,18,0,0,12,17,0,0);


1. Your test case is invalid, in insert statement keyword should be "values" and not "value".

Quote:

My requirement is to take each row and For all the Non-null CC codes (1-4), see if there is a matching RC code and Amount in the same row. If the amounts and codes match i need to populate balanced, if not it is outofbalance. In the above scenario the first record would be balanced and second one would be out of balance.

Can this be done in SQL or it has be through the PL/SQL block.


2. Why PL/SQL? If I understand your requirement, you need SQL.

3. Why do you have 5 ITM_CC codes in your table but you say only 4 are required? check this.

4. In your test case, there are no values provided for a match to populate 'Balance'. So, I have added another insert statement for demonstration :

insert into ITEMS values(1236,'Item-3','CC1','CC2','CC3','','','CC1','CC2','CC3','',12,12,12,12,12,12,12,12,12);


SQL> SELECT CASE
  2           WHEN itm_rc_c1 = itm_cc_c1
  3                AND itm_rc_c1 IS NOT NULL
  4                AND itm_rc_amt1 = itm_cc_amt1 THEN 'Balanced'
  5           ELSE 'out of balance'
  6         END itm_1,
  7         CASE
  8           WHEN itm_rc_c2 = itm_cc_c2
  9                AND itm_rc_c2 IS NOT NULL
 10                AND itm_rc_amt2 = itm_cc_amt2 THEN 'Balanced'
 11           ELSE 'out of balance'
 12         END itm_2,
 13         CASE
 14           WHEN itm_rc_c3 = itm_cc_c3
 15                AND itm_rc_c3 IS NOT NULL
 16                AND itm_rc_amt3 = itm_cc_amt3 THEN 'Balanced'
 17           ELSE 'out of balance'
 18         END itm_3,
 19         CASE
 20           WHEN itm_rc_c4 = itm_cc_c4
 21                AND itm_rc_c4 IS NOT NULL
 22                AND itm_rc_amt4 = itm_cc_amt4 THEN 'Balanced'
 23           ELSE 'out of balance'
 24         END itm_4,
 25         t.*
 26  FROM   items t
 27
SQL> /

ITM_1          ITM_2          ITM_3          ITM_4              ITM_ID
-------------- -------------- -------------- -------------- ----------
ITM_NME                   ITM_RC_C1  ITM_RC_C2  ITM_RC_C3  ITM_RC_C4  ITM_RC_C5
------------------------- ---------- ---------- ---------- ---------- ----------

ITM_CC_C1  ITM_CC_C2  ITM_CC_C3  ITM_CC_C4  ITM_RC_AMT1 ITM_RC_AMT2 ITM_RC_AMT3
---------- ---------- ---------- ---------- ----------- ----------- -----------
ITM_RC_AMT4 ITM_RC_AMT5 ITM_CC_AMT1 ITM_CC_AMT2 ITM_CC_AMT3 ITM_CC_AMT4
----------- ----------- ----------- ----------- ----------- -----------
out of balance out of balance out of balance out of balance       1234
Item-1                    CC1        CC2        CC3
CC2                                                  12          12          12
          0           0          12           0           0           0


ITM_1          ITM_2          ITM_3          ITM_4              ITM_ID
-------------- -------------- -------------- -------------- ----------
ITM_NME                   ITM_RC_C1  ITM_RC_C2  ITM_RC_C3  ITM_RC_C4  ITM_RC_C5
------------------------- ---------- ---------- ---------- ---------- ----------

ITM_CC_C1  ITM_CC_C2  ITM_CC_C3  ITM_CC_C4  ITM_RC_AMT1 ITM_RC_AMT2 ITM_RC_AMT3
---------- ---------- ---------- ---------- ----------- ----------- -----------
ITM_RC_AMT4 ITM_RC_AMT5 ITM_CC_AMT1 ITM_CC_AMT2 ITM_CC_AMT3 ITM_CC_AMT4
----------- ----------- ----------- ----------- ----------- -----------
out of balance out of balance out of balance out of balance       1235
Item-2                    CC1        CC2        CC5
CC2        CC5                                       12          17          18
          0           0          12          17           0           0


ITM_1          ITM_2          ITM_3          ITM_4              ITM_ID
-------------- -------------- -------------- -------------- ----------
ITM_NME                   ITM_RC_C1  ITM_RC_C2  ITM_RC_C3  ITM_RC_C4  ITM_RC_C5
------------------------- ---------- ---------- ---------- ---------- ----------

ITM_CC_C1  ITM_CC_C2  ITM_CC_C3  ITM_CC_C4  ITM_RC_AMT1 ITM_RC_AMT2 ITM_RC_AMT3
---------- ---------- ---------- ---------- ----------- ----------- -----------
ITM_RC_AMT4 ITM_RC_AMT5 ITM_CC_AMT1 ITM_CC_AMT2 ITM_CC_AMT3 ITM_CC_AMT4
----------- ----------- ----------- ----------- ----------- -----------
Balanced       Balanced       Balanced       out of balance       1236
Item-3                    CC1        CC2        CC3
CC1        CC2        CC3                            12          12          12
         12          12          12          12          12          12


SQL>




Regards,
Lalit
Re: Item Validation [message #621590 is a reply to message #621522] Thu, 14 August 2014 13:00 Go to previous message
diva_thilak
Messages: 5
Registered: November 2010
Junior Member
Thank you Kumar for taking time and replying to this post.

1. I need to take all the non-null ITM_CC_* (codes and amounts) and compare against the ITM_RC_* (codes and amounts). If all the ITM_CC_* match with the ITM_RC_* then it would be balanced if not out of balance.
2. The order of codes populated in ITM_CC_CC* would be not be same as ITM_RC_*. Example i have provided, CC1 in the ITM_RC_CC1 did not appear in the ITM_CC_CC1.

The driving factor for the balancing would be ITM_CC_CC*.

Previous Topic: Clustered index on primary key columns
Next Topic: Spool into excel file with different tabs
Goto Forum:
  


Current Time: Sun Dec 21 07:14:56 CST 2014

Total time taken to generate the page: 0.15890 seconds