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

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 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!

Userlevel 7
Badge

Thank you for sharing your solution @joe21 !

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