Specified Cast Is Not Valid with SQL view

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.

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.

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.

The Data types seem fine and as expected:

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

Is this what you mean?

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?

I think this would do it.

Thanks ​@MichaelShirk & ​@darylbowman 

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

Can you attach your GI?

GI Attached, Thanks Daryl.

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

EDIT: nevermind, I got it.

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.

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

Good catch on the attributes!

​@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?

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

AugForums.com/AugSQL

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

It was 

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

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

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

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?

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.