February 19, 2011 at 9:32 am
Hi
I have a situation where I need to look at payments if they pass a criteria they go into a payments table if they dont they go in to a failed payments table.
They start off in a tmp payments table
Our software developers have sent back a try catch block and trigger.
I came up with.
So I 'm now looking at if...else or a true/false
I currently have
create table tmp_payments.......
create table failed_tmp_payments
select personid from tmp_payments where personid not in (select personid from member)
if ???? insert into payments (.....)
else
insert into tmp_failed_payments
February 20, 2011 at 12:03 am
Hi,
IMO, the way you are thinking of using SQL for your logic is a little off.
Consider 2 insert statements:
INSERT INTO payments (col1, coln...)
select col1, coln... from tmp_payments where personid in (select personid from member)
INSERT INTO tmp_failed_payments(col1, coln...)
select col1, coln... from tmp_payments where personid not in (select personid from member)
... that's if I understand what you are asking.
Then comes the question of "marking" rows in your temporary payments table as "dealt with" and the question of what you do with them.
You may find that looking up the keyword "OUTPUT" may help you some.
There will probably be people who object to the use of "WHERE IN (SELECT...)" as opposed to using joins, but I am illustrating a principle and not which manner may result in optimum execution.
February 20, 2011 at 5:34 pm
diamondgm (2/20/2011)
There will probably be people who object to the use of "WHERE IN (SELECT...)" as opposed to using joins, but I am illustrating a principle and not which manner may result in optimum execution.
😀 very appropriate foot note - I was already jumping at it 😛
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 20, 2011 at 11:25 pm
Haha, yeah - I thought it would be scrutanized 😛
February 21, 2011 at 1:20 am
Thanks both
I'm a newbie, will that be a right outter join as the only field that the two tables will have in common are the ones I'm searching for.
just to recap I have a payments going into a payments table and I'm trying to stop the ones that dont have a member id in member from going in to the payments table and raise an exception on them.
February 21, 2011 at 2:07 am
We'd be able to help you a lot more if you posted the DDL and your code thus far.
English can be a little ambiguous 😀
February 22, 2011 at 12:42 am
Hi, this is what I was going to do, but I've been told not to use triggers.
CREATE
PROCEDURE USP_PAYMENTS
@PERSONID
VARCHAR(20),
@SCHOOLID
INT,
@AMOUNT
MONEY,
@REFERENCE
VARCHAR(50)
AS
CREATE
TABLE TMP_PAYMENTS
(
PERSONID VARCHAR (20) NOT NULL,
PAYMENTID
INT NOT NULL PRIMARY KEY,
SCHOOLID
INT NOT NULL,
REFERENCE
VARCHAR (50)NULL,
AMOUNT
MONEY NOT NULL)
INSERT
INTO PAYMENTS (PERSONID,PAYMENTID,SCHOOLID,REFERENCE,AMOUNT)SELECT PERSONID FROM TMP_PAYMENTS WHERE PERSONID IN (SELECT PERSONID FROM Member) GO
CREATE
TRIGGER EMAILNROUGEPAYMENTS
ON
TMP_PAYMENTS
AFTER
INSERT AS
DECLARE
@PERSONID VARCHAR (20) DECLARE
@BODY VARCHAR (100)
DECLARE
@PAYMETID INT
DECLARE
@AMOUNT MONEY SELECT
@PERSONID=PERSONID,
@PAYMENTID
=PAYMENTID,
@SCHOOLID
=SCHOOLID
FROM
INSERTED AS SELECT
PAYMENTID FROM TMP_PAYMENTS WHERE PERSONID NOT IN (SELECT PERSONID FROM Member)
SET
@BODY = 'PAYMENTID '@PAYMENTID,@SCHOOLID,@PERSONID' HAS FAILED AND HAS BEEN DISCAREDED PLEASE RESEND WITH CORRECT USERID'
EXEC master..xp_sendmail
@recipients = 'xxx@XXX.COM',
@subject
= 'FAILED PAYMENTS',
@message
= @body
GO
DROP
TABLE TMP_PAYMENTS
February 22, 2011 at 5:18 am
Steve,
I took a look at the SQL you posted and I am a bit confused as to what you are actually trying to accomplish. Do you have a small set of requirements or problem statement which will give us a quick overview of the process surrounding this?
Thanks
P
February 22, 2011 at 11:54 am
Steve,
Not to be nasty, but I have no cooking clue what the SQL you posted is attempting to accomplish.
Let me try and break your process down for you...
1. Mark all rows in your payments table; you do this incase rows are added while you are working on the table.
2. Write two insert statements
2.1 Statement that inserts rows that meet criteria in to a success table (rows marked in step 1)
2.2 Statement that inserts rows that do not meet criteria in to a failed table (rows marked in step 1).
3. Delete all marked rows in payments table if you like, or create a field that you can mark the rows as processed.
This is one of many ways of doing something you vaguely describe.
February 22, 2011 at 1:02 pm
Ok looks like I need to go back to basics. I never thought of transactions coming in during the process. where can I begin to look at marking rows. What's the process called
February 22, 2011 at 3:54 pm
Steve,
It is not only transactions that may happen while your are processing, your insert table may have multiple rows depending on the scope the transaction. This is one of the reasons I thought maybe a little overview or explanation of the process that is creating these would help to point us in the right direction. There are several correct answers or answers that will work, but which one might fit your particular case best may not be able to be determined without this additional information.
For instance, is the transaction that inserts into the tmp_payments table always just one row? Is there more then one process that inserts to this table at one time? Is the process that inserts into this table happen as a single execution for all inserts and transactions once a day or is it on demand 24/7?
How to answer your question in a reasonably correct manner requires a bit of explanation or context.
P
February 22, 2011 at 8:26 pm
I'm not aware of a name for the concept.
An example will be: table has field named RowStatus of type bit default to null.
While busy with the row, update RowStatus = 0 and when processing is complete, set to 1.
Alternatively you may want to save the pk of each row you're addressing to a table variable.
You could also change the way you are doing things by validating before insert. Write a proc with the criteria for validation inside of it.
February 23, 2011 at 1:39 am
Ok
I have a central database which holds all the member details including a person ID (This is not a live database this is for reporting, live databases are held at each individual location)
every 20 mins we poll another companies server and look for payments. These payments are stored centrally in a table called payments. Payments are made against the person ID.
Currently if the payments are made against a personid we dont have then they just dont go anywhere.
I want to be able to pick up the payments compare them to the member table to make sure there is a match and if there is they go in to the payments table. If not I was going to do a db mail stored procedure to notify the company sending them and then delete them.
when we poll there maybe multiple payments to pick up.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply