Skip to main content
Solved

Specified Cast Is Not Valid with SQL view


Forum|alt.badge.img+8
  • Captain II
  • 362 replies

Hey guys,

 

this is my first time playing around with views for a generic inquiry we will be requiring for a new legislation coming in.

I have used the view in a GI and it has given me the error: ‘Error: An error occurred during processing of the field Base Item Weight: Specified cast is not valid.’.

This is my view:

CREATE VIEW CBAMCalculator AS

SELECT 
	Inv.CompanyID,
	TranLine.DocType,
	TranLine.BatNbr,
	Inv.InventoryID,
	Orders.OrderNbr,
	Line.LineNbr,
	Inv. BaseItemWeight FROM [InventoryItem] AS Inv
INNER JOIN [AMMTran] AS TranLine ON Inv.[InventoryID] = TranLine.[InventoryID]
INNER JOIN [AMBatch] AS Batch ON TranLine.[DocType] = Batch.[DocType] AND TranLine.[BatNbr] = Batch.[BatNbr]
INNER JOIN [SOLine] AS Line ON TranLine.[OrderType] = Line.[AMOrderType] AND TranLine.[ProdOrdID] = Line.[AMProdOrdID]
INNER JOIN [SOOrder] AS Orders ON Line.[OrderType] = Orders.[OrderType] AND Line.[OrderNbr] = Orders.[OrderNbr]
WHERE Batch.[DocType] = 'M'
GO

This is my DAC:

#region DocType
    [PXDBString(1, IsFixed = true, InputMask = "")]
    [PXUIField(DisplayName = "Doc Type")]
    public virtual string DocType { get; set; }
    public abstract class docType : PX.Data.BQL.BqlString.Field<docType> { }
    #endregion

    #region BatNbr
    [PXDBString(15, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Bat Nbr")]
    public virtual string BatNbr { get; set; }
    public abstract class batNbr : PX.Data.BQL.BqlString.Field<batNbr> { }
    #endregion

    #region InventoryID
    [PXDBInt()]
    [Inventory]
    [PXUIField(DisplayName = "Inventory ID")]
    public virtual int? InventoryID { get; set; }
    public abstract class inventoryID : PX.Data.BQL.BqlInt.Field<inventoryID> { }
    #endregion

    #region OrderNbr
    [PXDBString(15, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Order Nbr")]
    public virtual string OrderNbr { get; set; }
    public abstract class orderNbr : PX.Data.BQL.BqlString.Field<orderNbr> { }
    #endregion

    #region LineNbr
    [PXDBInt()]
    [PXUIField(DisplayName = "Line Nbr")]
    public virtual int? LineNbr { get; set; }
    public abstract class lineNbr : PX.Data.BQL.BqlInt.Field<lineNbr> { }
    #endregion

    #region BaseItemWeight
    [PXDBDecimal()]
    [PXUIField(DisplayName = "Base Item Weight")]
    public virtual Decimal? BaseItemWeight { get; set; }
    public abstract class baseItemWeight : PX.Data.BQL.BqlDecimal.Field<baseItemWeight> {}
    #endregion

I generated the DAC based on members from the DB.

Original column from the InventoryItem table. ^

DAC in DAC browser:

This is my query results from SSMS and it works as expected there:

Does anyone have any ideas as to what can be causing this? I have dropped and created the view too.

Best answer by darylbowman

If you have both [PXDBInt()] and [Inventory] on the InventoryID field, remove [PXDBInt()].

It shouldn’t let you use it that way, but for some reason, it did for me. Until I typed it manually.

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

25 replies

Forum|alt.badge.img+6
  • Captain II
  • 550 replies
  • December 11, 2024

You should probably include the CompanyID joins within your SQL view because you’re potentially getting inadvertent cross joins. Even though the call to the View will be filtered by CompanyID, your joins within the View aren’t.


Forum|alt.badge.img+8
  • Author
  • Captain II
  • 362 replies
  • December 11, 2024

Thanks ​@Django 

 

Thanks for the advice, I’ll make sure in future views I add this join.

I have added the CompanyID joins, however I am still experiencing the issue.


Forum|alt.badge.img+6
  • Captain II
  • 550 replies
  • December 11, 2024

If you run this query in SQL and then look at data types in the newly created table CBAMCalculatorTest, do they match up to what you were expecting?

SELECT *
INTO CBAMCalculatorTest
FROM CBAMCalculator

 

And maybe not related but you don’t have any IsKey parameters for your DAC.


Forum|alt.badge.img+8
  • Author
  • Captain II
  • 362 replies
  • December 11, 2024

The Data types seem fine and as expected:

 


Forum|alt.badge.img+6
  • Captain II
  • 550 replies
  • December 11, 2024

What does the SQL query look like when the GI queries the database? Can you run a SQL trace?


Forum|alt.badge.img+8
  • Author
  • Captain II
  • 362 replies
  • December 12, 2024

Is this what you mean?

 


MichaelShirk
Captain II
Forum|alt.badge.img+4
  • Captain II
  • 132 replies
  • December 12, 2024

This is really far out, and I don’t know how white space is handled, but I immediately noticed the extra space between the table and column in “Inv. BaseItemWeight”

Any chance that’s causing issues?


darylbowman
Captain II
Forum|alt.badge.img+13
MichaelShirk wrote:

but I immediately noticed the extra space between the table and column in “Inv. BaseItemWeight”

I think this would do it.


Forum|alt.badge.img+8
  • Author
  • Captain II
  • 362 replies
  • December 12, 2024

Thanks ​@MichaelShirk & ​@darylbowman 

 

Unfortunately, the issue still persists after re-declaring the view without the space.


darylbowman
Captain II
Forum|alt.badge.img+13

Can you attach your GI?


Forum|alt.badge.img+8
  • Author
  • Captain II
  • 362 replies
  • December 12, 2024

GI Attached, Thanks Daryl.


darylbowman
Captain II
Forum|alt.badge.img+13

@Chris Hackett - I’m getting an access denied. Is there attachment issues again?

EDIT: nevermind, I got it.


darylbowman
Captain II
Forum|alt.badge.img+13
  • 1683 replies
  • Answer
  • December 12, 2024

If you have both [PXDBInt()] and [Inventory] on the InventoryID field, remove [PXDBInt()].

It shouldn’t let you use it that way, but for some reason, it did for me. Until I typed it manually.


Forum|alt.badge.img+6
  • Captain II
  • 550 replies
  • December 12, 2024

Relating to running the trace, I’m more curious to know what SQL is being generated when you run the GI, which should then invoke the Select SQL statement. I’m expecting that’s where the error is being generated from.

Your Select * from CBAMCalculator isn’t the same as what Acumatica will be sending to SQL.

 

 


darylbowman
Captain II
Forum|alt.badge.img+13

I got it to work. The only difference is the attribute, and I had changed the view previously to maintain the CompanyID on the joins (but I don’t actually think that’s the issue):

CREATE VIEW CBAMCalculator AS
SELECT 
	Inv.CompanyID,
	TranLine.DocType,
	TranLine.BatNbr,
	Inv.InventoryID,
	Orders.OrderNbr,
	Line.LineNbr,
	Inv.BaseItemWeight 
FROM InventoryItem AS Inv
INNER JOIN AMMTran AS TranLine ON Inv.InventoryID = TranLine.InventoryID AND Inv.CompanyID = TranLine.CompanyID
INNER JOIN AMBatch AS Batch ON TranLine.DocType = Batch.DocType AND TranLine.BatNbr = Batch.BatNbr AND TranLine.CompanyID = Batch.CompanyID
INNER JOIN SOLine AS Line ON TranLine.OrderType = Line.AMOrderType AND TranLine.ProdOrdID = Line.AMProdOrdID AND TranLine.CompanyID = Line.CompanyID
INNER JOIN SOOrder AS Orders ON Line.OrderType = Orders.OrderType AND Line.OrderNbr = Orders.OrderNbr AND Orders.CompanyID = Line.CompanyID
WHERE Batch.DocType = 'M'
GO

 


Forum|alt.badge.img+6
  • Captain II
  • 550 replies
  • December 12, 2024

Good catch on the attributes!


TimRodman
Pro I
Forum|alt.badge.img+1
  • Pro I
  • 144 replies
  • December 12, 2024

@darylbowman Thanks for the tip about [Inventory], I didn’t know about that. I’ve always used [PXDBInt()] personally. Do you know what the difference is between them?


TimRodman
Pro I
Forum|alt.badge.img+1
  • Pro I
  • 144 replies
  • December 12, 2024

Also, shameless plug here for AugSQL which allows you to run SQL queries directly in a Production SaaS environment:

AugForums.com/AugSQL


darylbowman
Captain II
Forum|alt.badge.img+13
TimRodman wrote:

​Thanks for the tip about [Inventory], I didn’t know about that. I’ve always used [PXDBInt()] personally. Do you know what the difference is between them?

[Inventory] is a selector which does two things: 1) changes what you’re allowed to select and 2) changes the UI value from a number to the Inventory CD.

The reason you can’t have both is because the [Inventory] attribute already contains [PXDBInt] in its backend.


Forum|alt.badge.img+8
  • Author
  • Captain II
  • 362 replies
  • December 13, 2024
darylbowman wrote:

If you have both [PXDBInt()] and [Inventory] on the InventoryID field, remove [PXDBInt()].

It shouldn’t let you use it that way, but for some reason, it did for me. Until I typed it manually.

Thanks Daryl, just saw this on the discord server!

 

I have checked my other projects and it seems that none of my others do it, oh the comfort of acuminator lol!


Forum|alt.badge.img+8
  • Author
  • Captain II
  • 362 replies
  • December 13, 2024
Django wrote:

Relating to running the trace, I’m more curious to know what SQL is being generated when you run the GI, which should then invoke the Select SQL statement. I’m expecting that’s where the error is being generated from.

Your Select * from CBAMCalculator isn’t the same as what Acumatica will be sending to SQL.

 

 

It was 

SELECT COUNT( *)
FROM [CBAMCalculator] [InnerQuery_CBAMCalculator]
WHERE ( [InnerQuery_CBAMCalculator].[CompanyID] = 2) OPTION(OPTIMIZE FOR UNKNOWN)/* 00.00.00.00 */

 


Forum|alt.badge.img+8
  • Author
  • Captain II
  • 362 replies
  • December 13, 2024

@darylbowman ​@Django 

 

When trying to reach the next page in the GI, I am getting an error this is the query Acumatica is sending to the DB: ‘Incorrect syntax near 'OFFSET'.
Incorrect syntax near '22'.’

SELECT [CBAMCalculator].[DocType] AS [CBAMCalculator_DocType], [CBAMCalculator].[BatNbr] AS [CBAMCalculator_BatNbr], [CBAMCalculator].[InventoryID] AS [CBAMCalculator_InventoryID], [CBAMCalculator].[OrderNbr] AS [CBAMCalculator_OrderNbr], [CBAMCalculator].[LineNbr] AS [CBAMCalculator_LineNbr], [CBAMCalculator].[BaseItemWeight] AS [CBAMCalculator_BaseItemWeight]
FROM [CBAMCalculator] [CBAMCalculator]
WHERE ( [CBAMCalculator].[CompanyID] = 2)
 OFFSET (22) ROWS FETCH NEXT (22) ROWS ONLY OPTION(OPTIMIZE FOR UNKNOWN)/*Error thrown in this line*/

However, I can export the data to excel and it grabs all the data, and when i manually type the page number it also works fine.


darylbowman
Captain II
Forum|alt.badge.img+13

Did you modify your view to include the CompanyID in the joins? If not, it could be due to that.


TimRodman
Pro I
Forum|alt.badge.img+1
  • Pro I
  • 144 replies
  • December 13, 2024
darylbowman wrote:
TimRodman wrote:

​Thanks for the tip about [Inventory], I didn’t know about that. I’ve always used [PXDBInt()] personally. Do you know what the difference is between them?

[Inventory] is a selector which does two things: 1) changes what you’re allowed to select and 2) changes the UI value from a number to the Inventory CD.

The reason you can’t have both is because the [Inventory] attribute already contains [PXDBInt] in its backend.

Thanks ​@darylbowman. Do you agree that, since [Inventory] does extra stuff that is specific to the UI, it would probably make sense to stick with [PXDBInt] for SQL Views?


darylbowman
Captain II
Forum|alt.badge.img+13

For the purposes of raw SQL data, it will make no difference. If you’re using it in a GI, the one benefit would be seeing the Inventory CD without joining the Inventory table. I don’t think it makes much difference honestly. Just don’t use both at once.


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