I am writing a script to take data from an external source and create within a product on SQL7.0, and could use a little assistance on a SQL query.
Note: My references to transaction are in the context of an invoice, not as in the transaction log etc.
I have a table named A (which is populated from an external source) which contains transactions to create within SS7
As well as creating the transactions, I need to keep a record of which records have been created.
To process them, I need to perform several checks, such as does the customer that each transaction record relates to exist with my SS7 database.
Records that are processed ok, are written to a Processed table. (inside the loop for creating SS7 invoice transaction records), so that I check that the same transaction is not processed twice
Records with errors, such as customer not known are written to an error table.
If I then create the customer, and re-run the script It will create the SS7 customer transaction and create the processed record.
However this is inefficient, when 1000's of transactions are expected.
It would be much more efficient if I could perform a query against the table A, and the error table to determine records that were not reported as error for the processing that has just happened. So if a customer did not exist, when I ran the script yesterday, the query will not see it this time.
Example Data:
Table A
ID CustID Cost
1 ABC 3.45
2 DEF 1.23
3 GHI 5.32
Error Table
ID WhenCreated Reason
2 Thursday 30th August 11am Unknown Customer
Processed Table
ID
1
3
If I re-ran the script, then customer DEF would fail again.
I then create the customer DEF, and re-run the script
so the query needs to check 2 things,
1. that the ID in table A doesn't exist in the error table at all
2. that if it does exist in the error table, then the error records it finds, is/are not for this run.
I think some sort of inner join is needed with the error table joining itself, and that would require another field in the error table that stored the time of the current run.
any assistance, greatly appreciated.
thanks in advance
Note: My references to transaction are in the context of an invoice, not as in the transaction log etc.
I have a table named A (which is populated from an external source) which contains transactions to create within SS7
As well as creating the transactions, I need to keep a record of which records have been created.
To process them, I need to perform several checks, such as does the customer that each transaction record relates to exist with my SS7 database.
Records that are processed ok, are written to a Processed table. (inside the loop for creating SS7 invoice transaction records), so that I check that the same transaction is not processed twice
Records with errors, such as customer not known are written to an error table.
If I then create the customer, and re-run the script It will create the SS7 customer transaction and create the processed record.
However this is inefficient, when 1000's of transactions are expected.
It would be much more efficient if I could perform a query against the table A, and the error table to determine records that were not reported as error for the processing that has just happened. So if a customer did not exist, when I ran the script yesterday, the query will not see it this time.
Example Data:
Table A
ID CustID Cost
1 ABC 3.45
2 DEF 1.23
3 GHI 5.32
Error Table
ID WhenCreated Reason
2 Thursday 30th August 11am Unknown Customer
Processed Table
ID
1
3
If I re-ran the script, then customer DEF would fail again.
I then create the customer DEF, and re-run the script
so the query needs to check 2 things,
1. that the ID in table A doesn't exist in the error table at all
2. that if it does exist in the error table, then the error records it finds, is/are not for this run.
I think some sort of inner join is needed with the error table joining itself, and that would require another field in the error table that stored the time of the current run.
any assistance, greatly appreciated.
thanks in advance
