Solved

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

  • 14 September 2022
  • 4 replies
  • 114 views

Userlevel 6
Badge +3

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?

 

icon

Best answer by Joe Schmucker 14 September 2022, 03:03

View original

4 replies

Userlevel 6
Badge +3

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

 

Userlevel 7
Badge

Thank you for sharing your solution @joe21 !

Userlevel 7
Badge +5

@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.

Userlevel 6
Badge +3

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


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved