|
arv_rehal1381
Starting Member
4 Posts |
 Posted - 2013-09-29 : 17:40:36
|
| I need to copy information from few tables to one table. I am inserting values to "SalesOrder" table but there is foreign key column "OpportunityId" which is primary column in Opportunity table, but when I am inserting value using my view it is giving me referential error.Here is my view..SELECT dbo.ToGuid(dbo.OrdMain.Order_ID) AS SalesOrderId, dbo.ToGuid(dbo.OrdMain.Opportunity_ID) AS OpportunityId, 1 AS PriorityCode, dbo.ToGuid(N'592A6ECEEEF6E211BC40005056B10074') AS OwningBusinessUnit, dbo.ToGuid(N'10A6EA56D7A5E211BA9E005056B10074') AS PriceLevelId, dbo.OrdMain.Order_Num AS OrderNumber, dbo.OrdMain.Summary AS Description, CONVERT(MONEY, dbo.OrdMain.OrderDiscount_natural) AS DiscountAmount, CONVERT(MONEY, dbo.OrdMain.ListTotal_natural) AS TotalAmount, CONVERT(MONEY, dbo.OrdMain.ItemPriceTotal_natural) AS TotalLineItemAmount, CONVERT(MONEY, dbo.OrdMain.ItemDiscTotal_natural) AS TotalLineItemDiscountAmount, CONVERT(MONEY, dbo.OrdMain.ShipHandle_natural) AS FreightAmount, CONVERT(MONEY, dbo.OrdMain.DiscountTotal_natural) AS TotalDiscountAmount, dbo.OrdMain.ShipRequestDate AS RequestDeliveryBy, CONVERT(MONEY, dbo.OrdMain.TaxTotal_natural) AS TotalTax, dbo.OrdMain.PayTerms AS PaymentTermCode, CONVERT(INT, dbo.OrdMain.DeliveryCode) AS ShippingMethodCode, dbo.SystemUserBase.SystemUserId AS CreatedBy, dbo.OrdMain.AddDate AS CreatedOn, SystemUserBase_1.SystemUserId AS ModifiedBy, dbo.OrdMain.ChangeDate AS ModifiedOn, 0 AS StateCode, dbo.OrdMain.Address1 AS ShipTo_Line1, dbo.OrdMain.Address2 AS ShipTo_Line2, dbo.OrdMain.Address3 AS ShipTo_Line3, dbo.OrdMain.City AS ShipTo_City, dbo.OrdMain.State AS ShipTo_StateorProvince, dbo.OrdMain.Zip AS ShipTo_PostalCode, dbo.OrdMain.Country AS ShipTo_Country, dbo.OrdMain.PriPhone AS ShipTo_Telephone, dbo.OrdMain.BillTo AS BillTo_ContactName, dbo.OrdMain.BillAddr1 AS BillTo_Line1, dbo.OrdMain.BillAddr2 AS BillTo_Line2, dbo.OrdMain.BillAddr3 AS BillTo_Line3, dbo.OrdMain.BillCity AS BillTo_City, dbo.OrdMain.BillState AS BillTo_StateOrProvince, dbo.OrdMain.BillZip AS BillTo_PostalCode, dbo.OrdMain.BillCountry AS BillTo_Country, dbo.Org1.OrgName AS BillTo_Name, dbo.TransactionCurrencyBase.TransactionCurrencyId, dbo.TransactionCurrencyBase.ExchangeRate, CONVERT(MONEY, dbo.OrdMain.ListTotal_natural) AS TotalLineItemAmount_Base, CONVERT(MONEY, dbo.OrdMain.DiscountTotal_natural) AS TotalDiscountAmount_Base, CONVERT(MONEY, dbo.OrdMain.SubTotal_natural) AS TotalamountLessFreight_Base, CONVERT(MONEY, dbo.OrdMain.GrandTotal_natural) AS TotalAmount_Base, CONVERT(MONEY, dbo.OrdMain.ItemDiscTotal_natural) AS DiscountAmount_Base, CONVERT(MONEY, dbo.OrdMain.ItemDiscTotal_natural) AS TotalLineItemDiscountAmount_Base, CONVERT(MONEY, dbo.OrdMain.TaxTotal_natural) AS TotalTax_Base, dbo.ToGuid(dbo.OrdMain.Org_ID) AS CustomerId, 1 AS CustomerIdType, 8 AS OwnerIdType, dbo.Org1.OrgName AS CustomerIdName, dbo.OrdMain.OrderStat AS StatusFROM dbo.OrdMain LEFT OUTER JOIN dbo.Person ON dbo.OrdMain.Per_ID = dbo.Person.Per_ID LEFT OUTER JOIN dbo.TransactionCurrencyBase ON dbo.OrdMain.CurrencyCode = dbo.TransactionCurrencyBase.ISOCurrencyCode LEFT OUTER JOIN dbo.SystemUserBase AS SystemUserBase_1 ON dbo.OrdMain.ChangeUser = SystemUserBase_1.UserName LEFT OUTER JOIN dbo.Org1 ON dbo.OrdMain.Org_ID = dbo.Org1.Org_ID LEFT OUTER JOIN dbo.SystemUserBase ON dbo.OrdMain.AddUser = dbo.SystemUserBase.UserNameThanks in advance for any help |
|
The INSERT statement conflicted with the FOREIGN K