October 1, 2009 at 11:25 am
I created a view with 3 tables and it does not seem to update when the tables are updated. Below is my Select statement
SELECT dbo.CUST_LINE_BINARY.CUST_ORDER_LINE_NO, dbo.CUST_LINE_BINARY.BITS, dbo.CUSTOMER_ORDER.ACCEPT_EARLY,
dbo.CUSTOMER_ORDER.DAYS_EARLY, dbo.CUSTOMER_ORDER.BACKORDER_FLAG, dbo.CUST_LINE_BINARY.CUST_ORDER_ID
FROM dbo.CUSTOMER_ORDER LEFT OUTER JOIN
dbo.CUST_LINE_BINARY ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_LINE_BINARY.CUST_ORDER_ID RIGHT OUTER JOIN
dbo.CUSTOMER ON dbo.CUSTOMER_ORDER.CUSTOMER_ID = dbo.CUSTOMER.ID
I am really new at using/creating views and created this one by looking at an existing one so I apologize if this is a really stupid question but I really need this to update when the tables are updated. Thank you in advance for your help.
October 1, 2009 at 11:49 am
Are you trying to update the view directly? Can you show the Update statement?
October 1, 2009 at 11:53 am
As I said...I am really new to creating views...if I had an update statement, where would it be??? If I don't have one, how would I create one??? None of the other views that already exist for this database seem to have one.
October 1, 2009 at 11:57 am
If you are positive that the underlying tables are updated, then there could be a problem with the view. The problem, however, is that we can't see what you see from here. It would help if you could post the DDL for the underlying tables, some sample data for the tables, expected results based on the sample data, a sample update to the data with a subsequent expected results so that we can see what is happening (or not).
Please read the first article I have referenced below in my signature block, it will show what you need to post and how to get the best help possible.
October 1, 2009 at 12:08 pm
I'm sorry but I don't know what DDL is so I'll do the best I can without annoying everyone too much. Below is a select statement from the customer_order table that is part of the view (this is the actual order that I am trying to get info from, it was entered today).
select * from customer_order where id = '61103'
I get a single line with the customer order information
If I run this select statement on my view
select * from ab_cust_order_ack where cust_order_id = '61103', I get no results so it appears that the tables are updating, not the view.
October 1, 2009 at 12:55 pm
DDL is Data Definition Language, and what it means is the CREATE TABLE statements used to create your tables.
Please read the article I recommended. It will help you get the help you are asking for, and with the benefit of getting TESTED code.
October 1, 2009 at 12:59 pm
Thank you for the clarification. Here it is.
CREATE VIEW dbo.AB_CUST_ORDER_ACK
AS
SELECT dbo.CUST_LINE_BINARY.CUST_ORDER_LINE_NO, dbo.CUST_LINE_BINARY.BITS, dbo.CUSTOMER_ORDER.ACCEPT_EARLY,
dbo.CUSTOMER_ORDER.DAYS_EARLY, dbo.CUSTOMER_ORDER.BACKORDER_FLAG, dbo.CUST_LINE_BINARY.CUST_ORDER_ID
FROM dbo.CUSTOMER_ORDER LEFT OUTER JOIN
dbo.CUST_LINE_BINARY ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_LINE_BINARY.CUST_ORDER_ID RIGHT OUTER JOIN
dbo.CUSTOMER ON dbo.CUSTOMER_ORDER.CUSTOMER_ID = dbo.CUSTOMER.ID
October 1, 2009 at 3:00 pm
You really should read the article that Lynn recommended. It really makes the difference between receiving a useful response and what may be, at best, a guess.
You mentioned something about 'copying' the code from another view to create your own? Bear in mind that the code contains multiple joins and join types which may be the reason the row you expect is not retrieved.
Do you need columns from all the tables contained in the view?
October 1, 2009 at 3:10 pm
I'm sorry, I have had no SQL training whatsoever. Even the article is confusing to me. I apologize for wasting your time, that is why I chose the newbie forum. I'll try one more time
CREATE VIEW dbo.AB_CUST_ORDER_ACK
AS
SELECT dbo.CUST_LINE_BINARY.CUST_ORDER_LINE_NO, dbo.CUST_LINE_BINARY.BITS, dbo.CUSTOMER_ORDER.ACCEPT_EARLY,
dbo.CUSTOMER_ORDER.DAYS_EARLY, dbo.CUSTOMER_ORDER.BACKORDER_FLAG, dbo.CUST_LINE_BINARY.CUST_ORDER_ID
FROM dbo.CUSTOMER_ORDER LEFT OUTER JOIN
dbo.CUST_LINE_BINARY ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_LINE_BINARY.CUST_ORDER_ID RIGHT OUTER JOIN
dbo.CUSTOMER ON dbo.CUSTOMER_ORDER.CUSTOMER_ID = dbo.CUSTOMER.ID
October 1, 2009 at 3:11 pm
Sorry, didn't mean to post that yet...no, I don't need all of the columns, only 6. 3 from one table and 3 from another. I don't even need the Customer table anymore, only the Customer Order and the Customer Line Binary.
October 1, 2009 at 3:48 pm
I have not been able to test this code as you have not provided any test data but try this:
create view dbo.AB_CUST_ORDER_ACK
as
select dbo.CUST_LINE_BINARY.CUST_ORDER_LINE_NO,
dbo.CUST_LINE_BINARY.BITS,
dbo.CUSTOMER_ORDER.ACCEPT_EARLY,
dbo.CUSTOMER_ORDER.DAYS_EARLY,
dbo.CUSTOMER_ORDER.BACKORDER_FLAG,
dbo.CUST_LINE_BINARY.CUST_ORDER_ID
from dbo.CUSTOMER_ORDER
left outer join dbo.CUST_LINE_BINARY
on dbo.CUSTOMER_ORDER.ID = dbo.CUST_LINE_BINARY.CUST_ORDER_ID
Note that this would return all rows from the Customer_Order table as well as matching rows from the Cust_Line_Binary table.
If you only want matching rows from both tables then simply remove the 'left outer' portion of the join statement.
October 1, 2009 at 8:05 pm
Three and four part naming conventions have been depreciated in SELECT lists. You really need to start using table aliases and two part naming conventions like this:
create view dbo.AB_CUST_ORDER_ACK
as
select
clb.CUST_ORDER_LINE_NO,
clb.BITS,
custord.ACCEPT_EARLY,
custord.DAYS_EARLY,
custord.BACKORDER_FLAG,
clb.CUST_ORDER_ID
from
dbo.CUSTOMER_ORDER custord
left outer join dbo.CUST_LINE_BINARY clb
on custord.ID = clb.CUST_ORDER_ID;
October 1, 2009 at 8:07 pm
Now, if you really want help, we need the DDL for the underlying tables and sample data as well. You should be able to follow the instruction in the article I referenced even if you have no real experience. If there are parts you having problems, ask questions.
October 2, 2009 at 8:37 am
Thank you both for your advice. I have been looking into this further and for some reason, the cust_line_binary table did not update when the customer_order table did. I am going to contact my ERP provider to find out what triggers this. This may be the whole problem. The Order_ID does not exist in the cust_line_binary table and I believe it should.
October 2, 2009 at 10:09 am
Just to update you both and thank you again for your patience with me, the issue was with the data, not the view. I suppose I could have worked with the joins more to get it to work but as it turns out, the fields I was trying to incorporate into the view were not needed. I inherited a lot of really old reports that have some strange input items from long ago that are no longer necessary so I get a lot of weird problems. Thank you again for your patience. I have learned alot.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply