Home » SQL & PL/SQL » SQL & PL/SQL » iterative field matching process (Oracle 9)
iterative field matching process [message #416783] Tue, 04 August 2009 05:22 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi all,
I have a bit of an issue that I need to solve - probably using some kind of stored procedure, I guess.

I have a query that returns several rows of financial transactionl data.

For each row that is returned, I need to compare the row with another table (GL_Distributions) in order to find the 'best' match.

The table contains 8 fields - some of which are populated with *'s (which denote a wildcard)
Basically, I need find the best match (i.e. the row in the GL_Distributions) that matches with the most fields.
The process should go through every row in the GL table until it has found the best match. so if it cannot find a match on all 8 fields, it will try to match on 7, then 6 etc.

Does this sound like something I should be doing in a stored procedure within Oracle? or am i barking up the wrong tree.
Has anyone on here done anything similar? If so, some guidance would be much appreciated.

The stored procedure (if that is the way to go) will ultimately be called by a GEL Script within our CA Clarity application.

Thanks in advance,
Matt


Re: iterative field matching process [message #416790 is a reply to message #416783] Tue, 04 August 2009 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There was the same kind of question I answered on AskTom a couple of years ago, please search on it.

If you want a more complete answer here you have to post a working Test case: create table and insert statements along with the result you want with these data.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: iterative field matching process [message #417171 is a reply to message #416790] Thu, 06 August 2009 06:22 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Thanks Michel.
For starters, the database version is 9.2.0.4

I searched AskTom, and found something simiar but I could not make head nor tail of it.
Essentially, what I need to do is as follows:
1. Read entries from my WIP table.
2. For EACH entry find the best match in the GL Distribution table (this table will include null values which should act as wildcard)
3. When this match has been found, the GL Accounts table (which links to the distributions table) will contain usually 2 account numbers.
Data from the WIP transaction, GL Distribution and the GL Account are then wirtten to a new table.

I wasn't sure whether to compare the WIP entries with just the Distributions and THEN query the appropriate linked account.
or compare the WIP table to the query containing both the Distributions and Accounts tables (there will be duplicates for the distributions values.

Query to link the two:
SELECT *
FROM
odf_account_test tst,
odf_dist_test dst
WHERE tst.odf_parent_id = dst.id



GL Distributions table with INSERT statement:
CREATE TABLE odf_dist_test ( 
  id                   DECIMAL(22), 
  NAME                 VARCHAR2(160), 
  code                 VARCHAR2(60), 
  post                 DECIMAL(22)    NULL, 
  reverse              DECIMAL(22)    NULL, 
  transtype            VARCHAR2(30)    NULL, 
  client_class         VARCHAR2(30)    NULL, 
  dst_gl_dist_dept     VARCHAR2(30)    NULL, 
  prod_sol             VARCHAR2(30)    NULL, 
  dst_gl_dist_revclass VARCHAR2(30)    NULL, 
  wip_class            VARCHAR2(30)    NULL, 
  chargecode           VARCHAR2(30)    NULL, 
  dst_gl_dist_exec_dep VARCHAR2(10)    NULL, 
  entity               VARCHAR2(30)    NULL); 

INSERT INTO ODF_DIST_TEST (ID,NAME,CODE,POST,REVERSE,TRANSTYPE,WIP_CLASS,DST_GL_DIST_DEPT,DST_GL_DIST_EXEC_DEP,DST_GL_DIST_REVCLASS,PROD_SOL,ENTITY,CHARGECODE,CLIENT_CLASS) VALUES (5000000,'GL000001','1',1,0,'WRITEUP','T&M','A21200',null,null,'FST','DSTTH','LADM2000','OTHER');
INSERT INTO ODF_DIST_TEST (ID,NAME,CODE,POST,REVERSE,TRANSTYPE,WIP_CLASS,DST_GL_DIST_DEPT,DST_GL_DIST_EXEC_DEP,DST_GL_DIST_REVCLASS,PROD_SOL,ENTITY,CHARGECODE,CLIENT_CLASS) VALUES (5002002,'GL000010','test3',0,0,'LABOUR','T&M','D41000',null,null,null,null,null,'OTHER');
INSERT INTO ODF_DIST_TEST (ID,NAME,CODE,POST,REVERSE,TRANSTYPE,WIP_CLASS,DST_GL_DIST_DEPT,DST_GL_DIST_EXEC_DEP,DST_GL_DIST_REVCLASS,PROD_SOL,ENTITY,CHARGECODE,CLIENT_CLASS) VALUES (5002001,'GL00000H','id2',0,0,'LABOUR','T&M','D41106',null,null,'502IAI',null,null,'DSTBELV');
INSERT INTO ODF_DIST_TEST (ID,NAME,CODE,POST,REVERSE,TRANSTYPE,WIP_CLASS,DST_GL_DIST_DEPT,DST_GL_DIST_EXEC_DEP,DST_GL_DIST_REVCLASS,PROD_SOL,ENTITY,CHARGECODE,CLIENT_CLASS) VALUES (5002000,'GL000009','gl9',0,0,'LABOUR','T&M','D41106',null,'DEV',null,null,null,'OTHER');
INSERT INTO ODF_DIST_TEST (ID,NAME,CODE,POST,REVERSE,TRANSTYPE,WIP_CLASS,DST_GL_DIST_DEPT,DST_GL_DIST_EXEC_DEP,DST_GL_DIST_REVCLASS,PROD_SOL,ENTITY,CHARGECODE,CLIENT_CLASS) VALUES (5000001,'GL000003','2',0,0,'BILLING',null,null,null,null,null,null,null,null);
INSERT INTO ODF_DIST_TEST (ID,NAME,CODE,POST,REVERSE,TRANSTYPE,WIP_CLASS,DST_GL_DIST_DEPT,DST_GL_DIST_EXEC_DEP,DST_GL_DIST_REVCLASS,PROD_SOL,ENTITY,CHARGECODE,CLIENT_CLASS) VALUES (5001001,'GL000005','UK Labour',1,0,'LABOUR',null,'D41000',null,'DEV',null,null,'LDEV7560',null);
INSERT INTO ODF_DIST_TEST (ID,NAME,CODE,POST,REVERSE,TRANSTYPE,WIP_CLASS,DST_GL_DIST_DEPT,DST_GL_DIST_EXEC_DEP,DST_GL_DIST_REVCLASS,PROD_SOL,ENTITY,CHARGECODE,CLIENT_CLASS) VALUES (5001002,'GL000007','adjuk',0,1,'ADJUSTMENT',null,null,null,null,null,null,null,null);
INSERT INTO ODF_DIST_TEST (ID,NAME,CODE,POST,REVERSE,TRANSTYPE,WIP_CLASS,DST_GL_DIST_DEPT,DST_GL_DIST_EXEC_DEP,DST_GL_DIST_REVCLASS,PROD_SOL,ENTITY,CHARGECODE,CLIENT_CLASS) VALUES (5001000,'GL000004','tg1',0,0,'LABOUR','T&M',null,null,null,null,null,null,null);



GL Account
CREATE TABLE ODF_ACCOUNT_TEST
(
   ID decimal(22),
   NAME varchar2(160),
   CODE varchar2(60),
   ODF_PARENT_ID decimal(22),
   ODF_CNCRT_PARENT_ID decimal(22),
   DST_GL_ACC_TYPE varchar2(30),
   DST_GL_ACCOUNT varchar2(20),
   DST_GL_ACC_INT decimal(22),
   DST_GL_ACC_USE_TRANS decimal(22)
);


INSERT INTO odf_account_test (ID,NAME,CODE,ODF_PARENT_ID,ODF_CNCRT_PARENT_ID,DST_GL_ACC_TYPE,DST_GL_ACCOUNT,DST_GL_ACC_INT,DST_GL_ACC_USE_TRANS) VALUES (5001003,'test','test1',5002001,5002001,'chargecred','account3',0,0);
INSERT INTO odf_account_test (ID,NAME,CODE,ODF_PARENT_ID,ODF_CNCRT_PARENT_ID,DST_GL_ACC_TYPE,DST_GL_ACCOUNT,DST_GL_ACC_INT,DST_GL_ACC_USE_TRANS) VALUES (5001006,'test','test4',5002002,5002002,'chargedebit','account5',0,0);
INSERT INTO odf_account_test (ID,NAME,CODE,ODF_PARENT_ID,ODF_CNCRT_PARENT_ID,DST_GL_ACC_TYPE,DST_GL_ACCOUNT,DST_GL_ACC_INT,DST_GL_ACC_USE_TRANS) VALUES (5001000,'GL9','GL9',5002000,5002000,'chargecred','account1',1,0);
INSERT INTO odf_account_test (ID,NAME,CODE,ODF_PARENT_ID,ODF_CNCRT_PARENT_ID,DST_GL_ACC_TYPE,DST_GL_ACCOUNT,DST_GL_ACC_INT,DST_GL_ACC_USE_TRANS) VALUES (5001002,'GL9','GL9b',5002000,5002000,'chargedebit','Account2',1,0);
INSERT INTO odf_account_test (ID,NAME,CODE,ODF_PARENT_ID,ODF_CNCRT_PARENT_ID,DST_GL_ACC_TYPE,DST_GL_ACCOUNT,DST_GL_ACC_INT,DST_GL_ACC_USE_TRANS) VALUES (5001004,'test','test2',5002001,5002001,'chargedebit','account4',0,0);
INSERT INTO odf_account_test (ID,NAME,CODE,ODF_PARENT_ID,ODF_CNCRT_PARENT_ID,DST_GL_ACC_TYPE,DST_GL_ACCOUNT,DST_GL_ACC_INT,DST_GL_ACC_USE_TRANS) VALUES (5000000,'Revenue','Rev01',5001001,5001001,'chargecred','460065',0,0);
INSERT INTO odf_account_test (ID,NAME,CODE,ODF_PARENT_ID,ODF_CNCRT_PARENT_ID,DST_GL_ACC_TYPE,DST_GL_ACCOUNT,DST_GL_ACC_INT,DST_GL_ACC_USE_TRANS) VALUES (5000001,'WIP','Rev02',5001001,5001001,'chargedebit','120400',1,0);
INSERT INTO odf_account_test (ID,NAME,CODE,ODF_PARENT_ID,ODF_CNCRT_PARENT_ID,DST_GL_ACC_TYPE,DST_GL_ACCOUNT,DST_GL_ACC_INT,DST_GL_ACC_USE_TRANS) VALUES (5001005,'test','test3',5002002,5002002,'chargecred','account4',0,0);



WIP Table with inserts. (note, for this post I have put all data in one table. in reality it comes from several tables):
CREATE TABLE WIP_TEST (		
Transno varchar2(10),		
TransType varchar2(10),		
CLIENTCLASS varchar2(6),		
DEPARTCODE varchar2(8),		
PROJECTCLASS varchar2(10),		
DSTI_REVENUE_CLASS varchar2(3),		
WIPCLASS varchar2(10),		
CHARGE_CODE varchar2(10),		
ENTITY varchar2(8),		
COMPANY_CODE varchar2(10),		
RESOURCE_CODE varchar2(5),		
CURRENCY_CODE varchar2(3),		
AMOUNT FLOAT,		
PROJECT_CODE varchar2(10)		
);		
		
INSERT INTO WIP_TEST VALUES(	'6464148','LABOUR','OTHER','D41106','502IAI','DEV','T&M','LDEL0010','DSTUK','NCB01BH','59964','GBP','1250.03','DST009749')	;
INSERT INTO WIP_TEST VALUES(	'6464179','LABOUR','OTHER','D41106','502IAI','DEV','T&M','LCON3000','DSTUK','NCB01BH','59964','GBP','-1249.95','DST008149')	;
INSERT INTO WIP_TEST VALUES(	'6463064','LABOUR','OTHER','D41000','502IAI','DEV','T&M','LCON4644','DSTUK','NCB01BH','60578','GBP','1166.69','DST009749')	;
INSERT INTO WIP_TEST VALUES(	'6463067','LABOUR','OTHER','D41000','502IAI','DEV','T&M','LCON3000','DSTUK','NCB01BH','60578','GBP','-1166.62','DST008149')	;
INSERT INTO WIP_TEST VALUES(	'6463865','LABOUR','OTHER','D41000','502IAI','DEV','T&M','LCON3000','DSTUK','NCB01BH','59951','GBP','-666.64','DST008149')	;
INSERT INTO WIP_TEST VALUES(	'6463866','LABOUR','OTHER','D41000','502IAI','DEV','T&M','LCON3380','DSTUK','NCB01BH','59951','GBP','666.68','DST009749')	;
INSERT INTO WIP_TEST VALUES(	'6463866','LABOUR','OTHER','D41000','502IAI','DEV','T&M','LCON3380','DSTUK','NCB01BH','59951','GBP','666.68','DST009749')	;
INSERT INTO WIP_TEST VALUES(	'6368392','LABOUR','OTHER','D41000','502MOF','DEV','T&M','LDEV7560','DSTUK','NUL02UK','60550','GBP','840','DST006679')	;
INSERT INTO WIP_TEST VALUES(	'6368393','LABOUR','OTHER','D41000','509IAN','DEV','T&M','LCON3080','DSTUK','JPM01UK','60443','GBP','900','DST007571')	;
INSERT INTO WIP_TEST VALUES(	'6368394','LABOUR','OTHER','D41000','535OTZ','DEV','INTERNAL','LINT1000','DSTUK','INTERUK','60385','GBP','0','INT1200UK')	;
INSERT INTO WIP_TEST VALUES(	'6368395','LABOUR','OTHER','D41000','508OPO','DEV','T&M','LDEL0010','DSTUK','SRI01CH','60385','GBP','266.66','DST009321')	;



For the first transaction, (transno = 6464148) here is the way I would manually acheive the match:
Check transtype for row against distributions
It is of type 'LABOUR' so matches with 5 GL Distributions.
CLIENTCLASS = 'OTHER' which matches with 3 distributions (there is a null value in one fo the distribution client class, but the process should find the best match)

DEPARTCODE = 'D41106' this matches with 2 distributions
PRJECTCLASS = '502IAI' - this matches with one of the GL distributions.
At this point the process could stop, as it has narrowed down to one.

The GL distribution located is of ID 5002001 - GL00000H.
This has 2 corresponding entries in the ODF_ACCOUNT_TEST table.


I'm not sure how to then read the informatino from all 3 tables to write it into the database either.
Should the process just identify the ID of the Distributions objedct, and log the matching fields and put together a query of all 3 tables?! that would no doubt be inefficient if I have hundreds of rows in the WIP table...

I appreciate any help you can offer, as I really am stuck and panicking about my deadline!!!

thanks in advance,
Matt






Re: iterative field matching process [message #417190 is a reply to message #417171] Thu, 06 August 2009 07:40 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
You can use pivot table to break your WIP_TABLE to individual rows

6464148 | LABOUR
6464148 | OTHER
6464148 | D41106
........
........

join it with GL Distributions table to find which one is matching least
I think performance wise my solution is not better.
Re: iterative field matching process [message #417192 is a reply to message #417190] Thu, 06 August 2009 07:46 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Thanks, but I need some kind of Stored Procedure/Script that will carry out the entire process..
Re: iterative field matching process [message #417196 is a reply to message #417192] Thu, 06 August 2009 08:05 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
It can be possible by a SQL Query also but performance wise I have no experience

[Updated on: Thu, 06 August 2009 08:09]

Report message to a moderator

Re: iterative field matching process [message #417197 is a reply to message #416783] Thu, 06 August 2009 08:11 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

thats what i'm not sure about.
how can I have it check each field in turn for a match until it finds a single entry?

with SQL i'd need to specify joins in the where clause.

are you able to provide an simple example?

thanks for your time.
Matt
Re: iterative field matching process [message #417199 is a reply to message #417197] Thu, 06 August 2009 08:15 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
how can I have it check each field in turn for a match until it finds a single entry?
select * from odf_dist_test where 'D41000' in (transtype,client_class,dst_gl_dist_dept)
Re: iterative field matching process [message #417200 is a reply to message #417197] Thu, 06 August 2009 08:30 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
a big example Wink
SELECT distinct a.x,b.assemble
  FROM (SELECT transtype x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT clientclass x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT departcode x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT projectclass x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT dsti_revenue_class x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT wipclass x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT charge_code x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT entity x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT company_code x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT resource_code x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT currency_code x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT TO_CHAR (amount) x
          FROM wip_test
         WHERE transno = 6464148
        UNION
        SELECT project_code x
          FROM wip_test
         WHERE transno = 6464148) a,
       (SELECT TO_CHAR (ID) || NAME || code|| TO_CHAR (POST)|| TO_CHAR (REVERSE)||
               transtype|| client_class|| dst_gl_dist_dept|| prod_sol||
               dst_gl_dist_revclass|| wip_class|| chargecode||
               dst_gl_dist_exec_dep|| entity assemble
          FROM odf_dist_test) b
 WHERE instr(b.assemble,a.x)>0
 order by 1


Hope this helps

[Updated on: Thu, 06 August 2009 08:34]

Report message to a moderator

Re: iterative field matching process [message #417335 is a reply to message #417200] Fri, 07 August 2009 05:46 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi,
thanks for that,
I can see that it pivots the information from the WIP table,

but it doesn't really end up giving me combined data from one row.

for the example transno I posted, I need one row of data that contains all info from WIP_TEST and ODF_DIST_TEST for the best match.

..or at least to identify a single ID from the ODF_DIST_TEST for each WIP transaction.

thanks again though.
Regards,
Matt
Re: iterative field matching process [message #417343 is a reply to message #417335] Fri, 07 August 2009 07:33 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
but it doesn't really end up giving me combined data from one row.


the select is always in your hands pal
Re: iterative field matching process [message #417352 is a reply to message #417343] Fri, 07 August 2009 09:39 Go to previous message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

sorry,
what I mean to say is that leaves me with a pivot table
but I still cannot see how I can convert that to return a single row from ODF_DIST_TEST. As from each single row of the resultant pivot it is not possible to tell which has the best match.

am I being really dim? (hehe don't answer that!!) Confused

apologies for dragging this out a lot longer that it possibly needed to be.

Previous Topic: Suggest the best alternative apart from the Function
Next Topic: Please validate the update
Goto Forum:
  


Current Time: Sat Dec 10 14:45:04 CST 2016

Total time taken to generate the page: 0.13259 seconds