November 10, 2008 at 3:50 pm
Drop 10 yards and punt?
Not sure either. We don't know your table and view structures, we don't have any sample data with which to work. All we can do is throw guesses into the dark. You have everything you need to debug your problems.
For better help helping you, take the time to read the article in my signature block below.
November 10, 2008 at 4:02 pm
i sent you an email with a screenshot
November 10, 2008 at 4:07 pm
Thanks, but I'm not working from a screen shot of the data. Please take the time to read the article shown below in my signature block and follow the guidelines there to create the DDL for the tables and views, the insert statements with the sample data, and provide what the results should be based on the sample data.
The more you do to help us help you the better results you will get.
November 10, 2008 at 4:12 pm
Oh, one more thing. Post all the above here on this thread. If you need to, zip everything and post the zip file. If you PM it someone, no one else has the opportunity to jump in and try to help. The beauty of this site is that many times more than one person helps out and they usually come up with slightly different ways to accomplish the same task. Some are better than others, and some morph as others look at the solutions provided and even better solutions sometimes evolve.
November 11, 2008 at 8:22 am
Can you tell me why this doesnt run:
USE [AtriumWOE_CS_UserData_52]
GO
/****** Object: StoredProcedure [dbo].[BILLTO_Customers] Script Date: 11/10/2008 16:02:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BILLTO_Customers]
AS
SET NOCOUNT ON
update dbo.Customers
SET
CustomerID = @Account_Number,
Name = @Customer_Name,
ShortName = @Known_As,
ReferenceNo = @Legacy_Account_Number,
EffectiveDate = @Account_Established_Date,
Status = @status,
CreateDate = @Account_Established_Date
from
dbo.Customers
inner join dbo.AVW_11i_WA_BILLTO_Customers
where (cust.CustomerID = newcust.Account_number)
if @@rowcount = 0
insert into dbo.Customers
select * from dbo.AVW_11i_WA_BILLTO_Customers newcust
left outer join dbo.Customers cust
on (newcust.Account_number = cust.CustomerID)
(CustomerID,
Name,
ShortName,
ReferenceNo,
EffectiveDate,
Status,
CreateDate)
values (@Account_Number, @Customer_Name, @Known_As, @Legacy_Account_Number, @Account_Established_Date, @status, @Account_Established_Date)
November 11, 2008 at 8:23 am
this is the error:
Msg 137, Level 15, State 2, Procedure BILLTO_Customers, Line 8
Must declare the scalar variable "@Account_Number".
Msg 102, Level 15, State 1, Procedure BILLTO_Customers, Line 28
Incorrect syntax near 'CustomerID'.
November 11, 2008 at 8:36 am
Oh where to begin.
None of your @column-name's will work as anything beginning with @ is a variable and you never declare any variables.
I'm not sure why you are going through all this pain. My original post should work using copy and paste. You may need to remove the reference to deleted as you aren't using it here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2008 at 8:52 am
error is
Msg 207, Level 16, State 1, Procedure BILLTO_Customers, Line 14
Invalid column name 'Status'.
Msg 207, Level 16, State 1, Procedure BILLTO_Customers, Line 34
Invalid column name 'Status'.
Msg 207, Level 16, State 1, Procedure BILLTO_Customers, Line 34
Invalid column name 'CreateDate'.
USE [AtriumWOE_CS_UserData_52]
GO
/****** Object: StoredProcedure [dbo].[BILLTO_Customers] Script Date: 11/10/2008 16:02:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BILLTO_Customers]
AS
SET NOCOUNT ON
-- updates existing customers
UPDATE dbo.Customers
SET
CustomerID = A.Account_Number,
Name = A.Customer_Name,
ShortName = A.Known_As,
ReferenceNo = A.Legacy_Account_Number,
EffectiveDate = A.Account_Established_Date,
Status = A.Status,
CreateDate = A.Account_Established_Date
from
dbo.Customers C
JOIN dbo.AVW_11i_WA_BILLTO_Customers A
on C.CustomerID = A.Account_number
-- inserts new ones
insert into dbo.Customers
(CustomerID,
Name,
ShortName,
ReferenceNo,
EffectiveDate,
Status,
CreateDate)
select A.Account_number, A.Customer_name, A.Known_As, A.Legacy_Account_Number,A.Account_Established_Date, A.Status, A.CreateDate
from
dbo.AVW_11i_WA_BILLTO_Customers A
left outer join dbo.Customers C
on A.Account_number = C.CustomerID
where
C.CustomerID is null
November 11, 2008 at 8:56 am
paul.starr (11/11/2008)
error isMsg 207, Level 16, State 1, Procedure BILLTO_Customers, Line 14
Invalid column name 'Status'.
Msg 207, Level 16, State 1, Procedure BILLTO_Customers, Line 34
Invalid column name 'Status'.
Msg 207, Level 16, State 1, Procedure BILLTO_Customers, Line 34
Invalid column name 'CreateDate'.
I used the column names you provided in your original post. What are the correct column names in the Customers table?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2008 at 9:00 am
that is correct do i need to put dbo.Status, dbo.CreateDate ?
November 11, 2008 at 9:08 am
Paul,
This is becoming the Oracle post all over again. You supply very little data about your issue, and when we try to guess with your half supplied information, you attempt to copy/paste code and then paste back errors. Half of these errors are very easy to troubleshoot. "Invalid column name 'Status'." means that there is no column status in the table you are trying to pull it from. If you're so unfamiliar with SQL that you can't perform even that level of troubleshooting and need us to write the query for you, that's fine... but it will take a bit of effort on your part to get us the information to do so.
Read the post in my signature that Lynn tried to send you to earlier and supply that information and we can provide something that actually works.
November 11, 2008 at 9:31 am
USE [AtriumWOE_CS_UserData_52]
GO
/****** Object: Table [dbo].[Customers] Script Date: 11/11/2008 10:27:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
[CustomerID] [dbo].[C_ID] NOT NULL,
[ParentCustomerID] [dbo].[C_ID] NULL,
[ParentContactID] [dbo].[C_ID] NULL,
[Name] [dbo].[C_Name] NOT NULL,
[ShortName] [dbo].[C_Caption] NULL,
[ReferenceNo] [dbo].[C_Name] NULL,
[Logo] [dbo].[C_SmallLink] NULL,
[CustomerTypeID] [dbo].[C_ID] NULL,
[Templates] [dbo].[C_Link] NULL,
[CorpURL] [dbo].[C_Link] NULL,
[CorpEmail] [dbo].[C_Email] NULL,
[SupportEmail] [dbo].[C_Email] NULL,
[SalesEmail] [dbo].[C_Email] NULL,
[OtherEmail] [dbo].[C_Email] NULL,
[DefaultContactID] [dbo].[C_ID] NULL,
[Markup] [dbo].[C_Percentage] NULL DEFAULT (0),
[DefaultCurrencyID] [dbo].[C_ID] NULL,
[PriceListID] [dbo].[C_ID] NOT NULL,
[FreightID] [dbo].[C_ID] NULL,
[PaymentTypeID] [dbo].[C_ID] NULL,
[PaymentTermID] [dbo].[C_ID] NULL,
[PaymentTermNotes] [dbo].[C_Comments] NULL,
[ShippingPolicyID] [dbo].[C_ID] NULL,
[CarrierID] [dbo].[C_ID] NULL,
[ShippingMethodID] [dbo].[C_ID] NULL,
[EffectiveDate] [dbo].[C_Date] NOT NULL DEFAULT (getdate()),
[ExpiryDate] [dbo].[C_Date] NOT NULL DEFAULT ('12/31/2020'),
[DefaultTaxRate] [dbo].[C_Percentage] NULL DEFAULT (0),
[DefaultQuoteDiscount] [dbo].[C_Percentage] NULL DEFAULT (0),
[DiscountComments] [dbo].[C_Comments] NULL,
[Notes] [dbo].[C_Text] NULL,
[Deleted] [dbo].[C_Boolean] NOT NULL DEFAULT (0),
[Status] [dbo].[C_Status] NULL DEFAULT (1),
[StatusComments] [dbo].[C_Comments] NULL,
[StatusChangeDate] [dbo].[C_Date] NULL,
[StatusChangeUser] [dbo].[C_UserName] NULL,
[CreateDate] [dbo].[C_Date] NOT NULL DEFAULT (getdate()),
[CreateUser] [dbo].[C_UserName] NULL DEFAULT (suser_sname()),
[MaintenanceDate] [dbo].[C_Date] NOT NULL DEFAULT (getdate()),
[MaintenanceUser] [dbo].[C_UserName] NULL DEFAULT (suser_sname()),
[LockUser] [dbo].[C_UserName] NULL,
[LockSessionID] [dbo].[C_GUID] NULL,
[LockDate] [dbo].[C_Date] NULL,
[PriceList_MaintenanceDate] [dbo].[C_Date] NULL,
[SyncStatus] [dbo].[C_Status] NOT NULL DEFAULT (0),
[SyncDate] [dbo].[C_Date] NULL,
[SyncUser] [dbo].[C_UserName] NULL,
[DiscountMode] [dbo].[C_Integer] NULL DEFAULT (0),
[UpchargeMode] [dbo].[C_Integer] NULL DEFAULT (0),
[CreateCustomerID] [dbo].[C_ID] NULL,
[CreateContactID] [dbo].[C_ID] NULL,
[MaintenanceCustomerID] [dbo].[C_ID] NULL,
[MaintenanceContactID] [dbo].[C_ID] NULL,
[InUse] [dbo].[C_Boolean] NOT NULL DEFAULT (0),
[CustomerSalesModel] [dbo].[C_Integer] NULL DEFAULT (0),
[CustomerPrefix] [dbo].[C_Caption] NULL,
[State] [dbo].[C_Status] NOT NULL DEFAULT (0),
[DefaultCustomerAddressID] [dbo].[C_ID] NULL,
[DefaultCustomerID] [dbo].[C_ID] NULL,
CONSTRAINT [Customers_PK] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [Contacts_Customers_FK1] FOREIGN KEY([ParentCustomerID], [ParentContactID])
REFERENCES [dbo].[Contacts] ([CustomerID], [ContactID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [Contacts_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [Contacts_Customers_FK2] FOREIGN KEY([MaintenanceCustomerID], [MaintenanceContactID])
REFERENCES [dbo].[Contacts] ([CustomerID], [ContactID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [Contacts_Customers_FK2]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [Contacts_Customers_FK3] FOREIGN KEY([CreateCustomerID], [CreateContactID])
REFERENCES [dbo].[Contacts] ([CustomerID], [ContactID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [Contacts_Customers_FK3]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [Contacts_Customers_FK4] FOREIGN KEY([CustomerID], [DefaultContactID])
REFERENCES [dbo].[Contacts] ([CustomerID], [ContactID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [Contacts_Customers_FK4]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [CustomerAddresses_Customers_FK1] FOREIGN KEY([DefaultCustomerAddressID], [DefaultCustomerID])
REFERENCES [dbo].[CustomerAddresses] ([CustomerAddressID], [CustomerID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [CustomerAddresses_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [Customers_Customers_FK1] FOREIGN KEY([ParentCustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [Customers_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [CustomerTypes_Customers_FK1] FOREIGN KEY([CustomerTypeID])
REFERENCES [dbo].[CustomerTypes] ([CustomerTypeID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [CustomerTypes_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [FreightTerms_Customers_FK1] FOREIGN KEY([FreightID])
REFERENCES [dbo].[FreightTerms] ([FreightID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [FreightTerms_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [PaymentTerms_Customers_FK1] FOREIGN KEY([PaymentTermID])
REFERENCES [dbo].[PaymentTerms] ([PaymentTermID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [PaymentTerms_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [PaymentTypes_Customers_FK1] FOREIGN KEY([PaymentTypeID])
REFERENCES [dbo].[PaymentTypes] ([PaymentTypeID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [PaymentTypes_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [ShippingCarriers_Customers_FK1] FOREIGN KEY([CarrierID])
REFERENCES [dbo].[ShippingCarriers] ([CarrierID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [ShippingCarriers_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [ShippingMethods_Customers_FK1] FOREIGN KEY([CarrierID], [ShippingMethodID])
REFERENCES [dbo].[ShippingMethods] ([CarrierID], [ShippingMethodID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [ShippingMethods_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [ShippingPolicies_Customers_FK1] FOREIGN KEY([ShippingPolicyID])
REFERENCES [dbo].[ShippingPolicies] ([ShippingPolicyID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [ShippingPolicies_Customers_FK1]
November 11, 2008 at 9:55 am
Looks like there is not a column named Status in the view you are trying to get the data from. The CreateDate is also not in the view and should be apparently, Account_Established_Date.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2008 at 9:59 am
i found out that it was looking at my view and my view equivalent for the table is such:
Status = Customer_Status
CreateDate = Account_Established_Date
November 11, 2008 at 10:56 am
when i do an insert from my stored procedure
insert into dbo.Customers (CustomerID, Name, ShortName, ReferenceNo, EffectiveDate, Status, CreateDate)
select A.Account_number, A.Customer_name, A.Known_As, A.Legacy_Account_Number, A.Account_Established_Date, A.Customer_Status, A.Account_Established_Date
I need to create this logic for the status column, "If fields(""Field6"") = ""I"" Then 0
Else
1"
Field6 = Customer_Status in the view, which has values of A or I
how would i create this in my stored procedure.
Viewing 15 posts - 16 through 30 (of 61 total)
You must be logged in to reply to this topic. Login to reply