Skip to main content
Solved

SQL view works in Query Analyzer, but I get an error that a field does not exist

  • September 14, 2022
  • 4 replies
  • 143 views

Joe Schmucker
Captain II
Forum|alt.badge.img+2

I have a SQL View that returns data when I run a select statement in SQL Query Analyzer.  I get an error that the CustomerLocationID field does not exist when I try to access the DAC.

If I remove that field, it works fine.  But I need that field.

This is the DAC

    #region SOShipment
	[Serializable]
	[PXCacheName("SOShipment SQL View")]
	public class ICSSOShipmentSQLView : IBqlTable
	{
		#region CustomerID
		[PXDBInt]
		public virtual int? CustomerID { get; set; }
		public abstract class customerID : PX.Data.BQL.BqlInt.Field<customerID> { }
		#endregion

		#region CustomerLocationID
		[PXDBInt]
		public virtual int? CustomerLocationID { get; set; }
		public abstract class customerLocationID : 
          PX.Data.BQL.BqlInt.Field<customerLocationID> { }
		#endregion

		#region ShipmentNbr
		[PXDBString(15, IsUnicode = true, IsKey = true)]
		public virtual string ShipmentNbr { get; set; }
		public abstract class shipmentNbr : PX.Data.BQL.BqlString.Field<shipmentNbr> { }
		#endregion
	}

This is the SQL View 

CREATE VIEW [dbo].[ICSMCSOShipmentSQLView]
AS
SELECT    
	CustomerID, 
	ShipmentNbr,
	CustomerLocationID
FROM            
	dbo.SOShipment
GO

When I execute SELECT * FROM [ICSMCSOShipmentSQLView] I get data in that field.

 

In debug, when this line fires I get an error:

foreach (ICSSOShipmentSQLView reg in SelectFrom<ICSSOShipmentSQLView>.Where<ICSSOShipmentSQLView.customerID.IsEqual<@P.AsInt>>.View.Select(this, item.FromCustomerID))
					{
  string test = "testing";
}

 

I’ve tried deleting the View in Query Analyzer, as well as EXEC sp_RefreshView ICSMCSOShipmentSQLView.  

The SOShipment table shows the CustomerLocationID as nullable, int.  That is how my DAC is setup too.

I checked and there are no records in that table with a null value in the field, so that is not it.

To reiterate, if I remove that field from the DAC, there is no error.

What am I doing wrong?

 

Best answer by Joe Schmucker

Well.  I created a new View with “V2” appended to the view name.

Works fine now. 

Before trying a new view name, I even deleted the original view, restarted SQL Server, verified the view was gone, recreated it and I still got that error.

I guess you chalk this up to bad luck.

CREATE VIEW [dbo].[ICSMCSOShipmentSQLViewV2]
AS
SELECT    
	CustomerID, 
	ShipmentNbr,
	CustomerLocationID
FROM            
	dbo.SOShipment
GO

 

View original
Did this topic help you find an answer to your question?

4 replies

Joe Schmucker
Captain II
Forum|alt.badge.img+2
  • Author
  • Captain II
  • 455 replies
  • Answer
  • September 14, 2022

Well.  I created a new View with “V2” appended to the view name.

Works fine now. 

Before trying a new view name, I even deleted the original view, restarted SQL Server, verified the view was gone, recreated it and I still got that error.

I guess you chalk this up to bad luck.

CREATE VIEW [dbo].[ICSMCSOShipmentSQLViewV2]
AS
SELECT    
	CustomerID, 
	ShipmentNbr,
	CustomerLocationID
FROM            
	dbo.SOShipment
GO

 


Chris Hackett
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • 2754 replies
  • September 14, 2022

Thank you for sharing your solution @joe21 !


Dmitrii Naumov
Acumatica Moderator
Forum|alt.badge.img+7
  • Acumatica Moderator
  • 632 replies
  • September 14, 2022

@joe21 just wanted to note that you probably need to include ‘CompanyID’ field to your view. Otherwise you’ll have some results selected from other tenants and/or snapshots.

 

And in general we do not recommend using SQL views because they neglect some important platform mechanisms, e.g. role based access, sharing data between parent-child tenants, etc. Instead the recommendation is to use PXProjectionAttribute on DACs.


Joe Schmucker
Captain II
Forum|alt.badge.img+2
  • Author
  • Captain II
  • 455 replies
  • September 14, 2022

Thanks @Dmitrii Naumov.  Pro tips!  I have never used a Projection.  I will investigate how they work and try to implement that instead.

Thank you!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings