March 6, 2017 at 10:11 am
account_nbr
something_birth_date
final_payment_date
current_acct_balance
acct_status
>> Now this view updates every time a customer's account is closed (acct_status= closed) <<
Know your mindset still wrong. The view does not update; it is re-created every time it's invoked within a session. You're still thinking of a static file or deck of punch cards that persists; think of a virtual rather than persisted data.
>> I created a table from my view ( Select* into #newtable from oldtable) so that I can set up a trigger on this table(NEW TABLE) to then input details into the mailing table (to send a closed account letter etc...). <<
No, again, you got conceptual errors about what a view is. You also missed a fundamental concept of RDBMS. Our goal in a database (actually doesn't have to be RDBMS. Just any database) is to remove redundancy. You want to increase it! I'm very old and back in the days when we had magnetic tapes and punchcards, the same data appeared all over the place. We were always sorting it moving it duplicating it and never quite sure what was where… Or what was correct exclamation
My suggestion is that you read this article:
https://www.simple-talk.com/sql/t-sql-programming/state-transition-constraints/
And understand what a status is. It is a state of being, so by its nature, it must have a temporal dimension. This temporal dimension is called an interval data type in ANSI/ISO standard SQL and it's basically a (status_start_timestamp, status_end_timestamp) pair that defines a half open interval. Why don't you extend your account status codes and their relationships to include printing things out.? This avoids needless redundancy and gets the history the account into one table.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 6, 2017 at 10:13 am
For simplicity I wrote 'when the view changes' I understand that it is the data that the view returns that changes.
So everytime there is a new entry in the view i.e a customer closes their account (FYI every time this happens they go into my view)
I then want my table(that is created from my view) to also include these new additions.
I assumed a stored procedure on the view will solve this. So the store procedure would insert new entries into my table when new entries are made into my view. The store proc I wrote only updates my table with the latest entry in my view and not the rest of the new additions.
If I have got this totally wrong then once again I apologise.
I wont be wasting anymore of your time on this topic.
All the help has been massivley appreciated.
Thanks
March 6, 2017 at 10:16 am
ExhibitA - Monday, March 6, 2017 8:02 AMYour column names are still pretty vague and I'm trying to figure out why the birthdate of a customer would make any sense in the view of an account..
Isn't it possible that two customer's can have the same D.O.B?
why do the column names matter?
March 6, 2017 at 10:38 am
ExhibitA - Monday, March 6, 2017 8:02 AMWhy do the column names matter?
Column names matter because there's a thing called ISO 11179, which sets up rules for metadata and naming conventions. But that's not my question; to whom does this birthdate belong, and why does it matter to the account? Is there a business rule that says the customer over 65 has to be terminated or something? In fact, accounts often persist after the original customer has died. You also don't seem to understand that you put a view on a view on the view.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 6, 2017 at 1:46 pm
ExhibitA - Monday, March 6, 2017 8:02 AMBefore I begin let me apologise for my earlier posts. It seems that I may offended some of the more experienced SQL users. I am now fully aware of how a view works and its limitations.With this said please can anyone assists with my problem:
So I have a view that is composed of customer information :
[Account.Number]
[D.O.B]
[LastPaymentDate]
[CurrentBalance]
[Status]Now this view updates every time a customer's account is closed (status= closed)
Now my previous post(see above) has been solved, and the trigger questions stem from a need to insert details (from the view) into a mailing table that runs automatically and mails customers.
Right, in an attempt to solve this dilemma, I created a table from my view ( Select* into #newtable from oldtable) so that I can set up a trigger on this table(NEW TABLE) to then input details into the mailing table (to send a closed account letter etc...).
I have written a Stored procedure that updates my table from my view each time the view changes.
However, each time I run the stored procedure it truncates the values from my table and only inserts the last entry from my view. Any ideas?
Apologies if this is confusing.
USE [WORK]
GOSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE PROCEDURE [DBO].[USP_CUSTOMER_VC_CLOSED]
AS
DECLARE @ACCOUNT_NUMBER CHAR(19)
DECLARE @CURRENTBALANCE DECIMAL (20,3)
DECLARE @DATEIDENTIFIED DATE
DECLARE @LASTPAYMENTDATE DATE
DECLARE @statusSELECT @ACCOUNT_NUMBER=(ACCOUNT_NUMBER) FROM [Work].DBO.vw_CUST_VC_CLOSED
SELECT @CURRENTBALANCE=(CURRENTBALANCE) FROM [Work].DBO.vw_CUST_VC_CLOSED
SELECT @DATEIDENTIFIED=(DATEIDENTIFIED) FROM [Work].DBO.vw_CUST_VC_CLOSED
SELECT @LASTPAYMENTDATE=(LASTPAYMENTDATE) FROM [Work].DBO.vw_CUST_VC_CLOSED
SELECT @status=(@NOCOMMITMENT) FROM [Work].DBO.vw_CUST_VC_CLOSEDBEGIN
SET NOCOUNT ONIF (SELECT DATENAME(DW,GETDATE()))='MONDAY'
INSERT INTO [WORK].[dbo].[T_CUSTOMER_VC_CLOSED]
(
account_number
,currentbalance
,dateidentified
,lastpaymentdate
,status
)values
(
@ACCOUNT_NUMBER
,@CURRENTBALANCE
,@DATEIDENTIFIED
,@LASTPAYMENTDATE
,@STATUS
)END
The reason that is not working the way you expect is that you are assinging multiple values to a single variable and thus it only retains the last one.
What would be better code for what you are trying to do would be something like:CREATE PROCEDURE [DBO].[USP_CUSTOMER_VC_CLOSED]
AS
BEGIN
SET NOCOUNT ON
IF (SELECT DATENAME(DW,GETDATE()))='MONDAY'
INSERT INTO [WORK].[dbo].[T_CUSTOMER_VC_CLOSED]
(
account_number
,currentbalance
,dateidentified
,lastpaymentdate
,status
)
values
(
SELECT ACCOUNT_NUMBER,
CURRENTBALANCE,
DATEIDENTIFIED,
LASTPAYMENTDATE,
STATUS
FROM [WORK].[DBO].[VW_CUST_VC_CLOSED]
WHERE STATUS LIKE 'closed'
)
END
Although looking at your code, I don't see how your original SP would have worked. The line "SELECT @status=(@NOCOMMITMENT) FROM [Work].DBO.vw_CUST_VC_CLOSED" confuses me as @NOCOMMITMENT doesn't appear to ever be defined.
That being said, I think you are overcomplicating this whole process. Lets go back to the beginning. You want to make a list of user accounts that are not closed so you can email them, correct? Why not change your view to something like:SELECT ACCOUNT_NUMBER,
CURRENTBALANCE,
DATEIDENTIFIED,
LASTPAYMENTDATE,
STATUS
FROM [WORK].[DBO].[VW_CUST_VC_CLOSED]
WHERE STATUS NOT LIKE 'closed'
Drop that into a view and you have a list of all non-closed customers and you don't need to duplicate any data. Your method where you are pulling data from a view (which pulls its data from the underlying tables) and storing that in a different table feels like uneccessary overhead to me. I do not see the benefit of duplicating the data.
If the stored procedure is the way you want to go, I'd remove the "MONDAY" check and just schedule it via SQL Agent to run on Monday's.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply