Comparing Data Value

[kublait]

Hi All,
I wrote a script that would evaluate a particular data field from a view to that of another table. The script is as follow:

SELECT DISTINCT
SIEBEL.FMS_VALIDATION_VIEW.ACCOUNT_NUMBER,
'ADDRESS',
-- FMS VALIDATION VIEW VALUE
CASE
WHEN NVL(SIEBEL.FMS_VALIDATION_VIEW.ADDRESS, 'Null') NOT IN NVL(SIEBEL.CX_CC_PARSE_DIA.ADDRESS, 'Null')
THEN NVL(SIEBEL.FMS_VALIDATION_VIEW.ADDRESS, 'Null')
END AS SIEBEL_VALUE,
-- CX CC PARSE DIA TABLE VALUE
CASE
WHEN NVL(SIEBEL.FMS_VALIDATION_VIEW.ADDRESS, 'Null') NOT IN NVL(SIEBEL.CX_CC_PARSE_DIA.ADDRESS, 'Null')
THEN NVL(SIEBEL.CX_CC_PARSE_DIA.ADDRESS, 'Null')
END AS DIA_VALUE
FROM SIEBEL.FMS_VALIDATION_VIEW,
SIEBEL.CX_CC_PARSE_DIA
WHERE SIEBEL.FMS_VALIDATION_VIEW.ACCOUNT_NUMBER = SIEBEL.CX_CC_PARSE_DIA.ACCOUNT_NUMBER
AND SIEBEL.FMS_VALIDATION_VIEW.WORKORDER_NUM = SIEBEL.CX_CC_PARSE_DIA.WORKORDER_NUM
AND NVL(SIEBEL.FMS_VALIDATION_VIEW.ADDRESS, 'Null') NOT IN NVL(SIEBEL.CX_CC_PARSE_DIA.ADDRESS, 'Null')


The problem that I'm encountering is that the results are being returned as:

ACCOUNT_NUMBER ADDRESS SIEBEL_VALUE DIA_VALUE
8255110110001478 ADDRESS 123 MAIN ST PO BOX 1
8255110110001478 ADDRESS PO BOX 1 123 MAIN ST

Creating a unique key would not help because I have to perform the same "unmatch" evaluation on all of the fields between the view and the table. Theoretically the street address and the po box is a match and should not be return in the values. I had though the NOT IN function would solve this problem, but it doesn't. I would appreciate any help. Thanks in advance.

Kublait

 

[SantaMufasa]

Kublait,

I anticipate (from your data above) that you have the following rows (amongst others) in your two tables:

SIEBEL.FMS_VALIDATION_VIEW Table
ACCOUNT_NUMBER      ADDRESS 
8255110110001478    123 MAIN ST
8255110110001478    PO BOX 1

CX_CC_PARSE_DIA Table
ACCOUNT_NUMBER      ADDRESS
8255110110001478    123 MAIN ST
8255110110001478    PO BOX 1

According to your basic join logic, there are 4 potential matching combinations (from the two tables):
1) 8255110110001478 123 MAIN ST 123 MAIN ST
2) 8255110110001478 123 MAIN ST PO BOX 1
3) 8255110110001478 PO BOX 1 PO BOX 1
4) 8255110110001478 PO BOX 1 123 MAIN ST

Once you add in the qualification:

"WHERE...NVL(SIEBEL.FMS_VALIDATION_VIEW.ADDRESS, 'Null') NOT IN NVL(SIEBEL.CX_CC_PARSE_DIA.ADDRESS, 'Null')...", that gets rid of rows #1 and #3, above, leaving what you got, rows #2 and #4.

If you show me what you really want to display, then we can reformulate your SELECT to meet your needs.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 23:59 (10Dec04) UTC (aka "GMT" and "Zulu"),
@ 16:59 (10Dec04) Mountain Time

 

[kublait]

Mufasa/Dave,
You are absolutely correct in your assumption. Essentially I'm trying to compare the data between the two tables. If the data are:

SIEBEL.FMS_VALIDATION_VIEW Table
ACCOUNT_NUMBER ADDRESS
8255110110001478 123 MAIN ST
8255110110001478 PO BOX 1
8255110110001478 567 MAIN ST

CX_CC_PARSE_DIA Table
ACCOUNT_NUMBER ADDRESS
8255110110001478 123 MAIN ST
8255110110001478 PO BOX 1
8255110110001478 PO BOX 5

Then the only value I want to see is
ACCOUNT_NUMBER ADDRESS SIEBEL_VALUE DIA_VALUE
8255110110001478 ADDRESS 567 MAIN ST PO BOX 5

 

[SantaMufasa]

Kublait,

How about this code, then:

SELECT fms.ACCOUNT_NUMBER,'ADDRESS',FMS_ADDR,DIA_ADDR
FROM  
 (SELECT account_number, workorder_num, address fms_addr
         from SIEBEL.FMS_VALIDATION_VIEW
  MINUS
  SELECT account_number, workorder_num, address
         SIEBEL.CX_CC_PARSE_DIA dia) fms
,(SELECT account_number, workorder_num, address dia_addr
         SIEBEL.CX_CC_PARSE_DIA dia
  MINUS
  SELECT account_number, workorder_num, address fms_addr
         from SIEBEL.FMS_VALIDATION_VIEW) dia
WHERE fms.ACCOUNT_NUMBER = DIA.ACCOUNT_NUMBER(+)
AND   fms.WORKORDER_NUM = DIA.WORKORDER_NUM(+)

The above code presumes that the SIEBEL.FMS_VALIDATION_VIEW has at least one entry for each account_number/workorder and SIEBEL.CX_CC_PARSE_DIA might have (but is not required) to have an entry for each account_number/workorder. If it is conceivable that the reverse situation is true instead or also, then let me know and I can take care or that situation, as well.

If there are multiple, but different, addresses for the same account_number/workorder combination in either table, each unique address will show up in the output.

Let me know if this resolves you need. (I'm curious, since I do not have your data to test the code...the code comes straight out of my head without the confirmation of machine testing. And I don't like to deliver untested code.)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 00:47 (11Dec04) UTC (aka "GMT" and "Zulu"),
@ 17:47 (10Dec04) Mountain Time

 

[kublait]

Mufasa/Dave
Thank you very much for your help. The final query are as follow:

(SELECT view1.account_number, 'ADDRESS' column_name,
'SIEBEL_VALUE' siebel_dia_flg, NVL (view1.ADDRESS, 'Null') column_value
FROM siebel.fms_validation_view view1, siebel.cx_cc_parse_dia table1
WHERE view1.account_number = table1.account_number
AND view1.workorder_num = table1.workorder_num
MINUS
SELECT table1.account_number, 'ADDRESS' column_name,
'SIEBEL_VALUE' siebel_dia_flg, NVL (table1.ADDRESS,
'Null') column_value
FROM siebel.fms_validation_view view1, siebel.cx_cc_parse_dia table1
WHERE view1.account_number = table1.account_number
AND view1.workorder_num = table1.workorder_num)
UNION
(SELECT table1.account_number, 'ADDRESS' column_name,
'DIA_VALUE' siebel_dia_flg, NVL (table1.ADDRESS, 'Null') column_value
FROM siebel.fms_validation_view view1, siebel.cx_cc_parse_dia table1
WHERE view1.account_number = table1.account_number
AND view1.workorder_num = table1.workorder_num
MINUS
SELECT view1.account_number, 'ADDRESS' column_name, 'DIA_VALUE' siebel_dia_flg,
NVL (view1.ADDRESS, 'Null') column_value
FROM siebel.fms_validation_view view1, siebel.cx_cc_parse_dia table1
WHERE view1.account_number = table1.account_number
AND view1.workorder_num = table1.workorder_num)


This works perfectly. Once again thanks for your help. :-D