10.2.300.24
Hello EpiUsers,
I’ve made an External BAQ.
I’m setting up a Query Parameter for Project on the other table:
The database column is showing varchar:
I’m getting this error:
I’ve looked in the ICE Tools manual, but I don’t see a solution on my own.
Does anyone know how I fix this?
Ben
You could try to convert the nvarchar to varchar during that join. The Table Relations can apparently be a field OR any expression. So rather than just putting the field, add a convert(varchar, @ParameterName). Hopefully that works.
The external BAQ convert many values incorrectly. Make your parameter just varchar.
However, why the external BAQ?
Where is that option? I only see nvarchar in Parameters.
This is a separate Project table for our Sales Sharepoint site. It is not the Epicor Project table and it links to other tables not represented in Epicor.
Adam,
The varchar Project ID is already on the primary table of the query.
The Table Criteria Filter Value does not allow expressions.
Darn, I haven’t had much time in the EXTERNAL baq designer. You could try doing the convert in a calculated field (using that parameter) in a subquery, then filter on that converted field in your main query.
You can use an expression for the table criteria. Just choose “Specified expression” as the type instead of “Parameter”
Then in the expression:
cast(@ProjectID AS varchar(80))
You’ll end up with
Thank you, smart man!
Now I no longer get that error.
The Query Phrase from Epicor is generate a “Bad SQL statement”:
select
[Opportunity].[ProjectID] as [Opportunity_ProjectID],
[Opportunity].[OpportunityID] as [Opportunity_OpportunityID],
[Opportunity].[Name] as [Opportunity_Name],
[Employee].[FirstName] as [Employee_FirstName],
[Employee].[LastName] as [Employee_LastName],
[Order].[OrderNum] as [Order_OrderNum],
[WorkOrder].[WorkOrderID] as [WorkOrder_WorkOrderID],
(convert(nvarchar,Opportunity.ProjectID)) as [Calculated_Project]
from dbo.Opportunity as Opportunity
full outer join dbo.Order as Order on
Opportunity.ProjectID = Order.ProjectID
and Opportunity.OpportunityID = Order.OpportunityID
*and ( Order.OrderNum = @SalesOrder )*
full outer join dbo.WorkOrder as WorkOrder on
Order.OrderNum = WorkOrder.OrderNum
and ( not WorkOrder.WorkOrderID like 'x%' )
full outer join dbo.Employee as Employee on
Opportunity.SalesRepID = Employee.EmployeeID
where (Opportunity.OpportunityID = @Opportunity or Opportunity.ProjectID = cast(@Project AS varchar(25)))
order by Opportunity.ProjectID, Opportunity.OpportunityID, Order.OrderNum, WorkOrder.WorkOrderID
When I move that Order.OrderNum = @SalesOrder to the where clause in SSMS it works correctly:
Declare @SalesOrder int;
Declare @Opportunity int;
Declare @Project varchar(25);
Set @SalesOrder = 14259;
select
[Opportunity].[ProjectID] as [Opportunity_ProjectID],
[Opportunity].[OpportunityID] as [Opportunity_OpportunityID],
[Opportunity].[Name] as [Opportunity_Name],
[Employee].[FirstName] as [Employee_FirstName],
[Employee].[LastName] as [Employee_LastName],
[Order].[OrderNum] as [Order_OrderNum],
[WorkOrder].[WorkOrderID] as [WorkOrder_WorkOrderID],
(convert(nvarchar,Opportunity.ProjectID)) as [Calculated_Project]
from dbo.Opportunity as Opportunity
full outer join denyse.dbo.[Order] as [Order] on
Opportunity.ProjectID = [Order].ProjectID
and Opportunity.OpportunityID = [Order].OpportunityID
--and ( [Order].OrderNum = 14259 )
full outer join dbo.WorkOrder as WorkOrder on
[Order].OrderNum = WorkOrder.OrderNum
and ( not WorkOrder.WorkOrderID like 'x%' )
full outer join dbo.Employee as Employee on
Opportunity.SalesRepID = Employee.EmployeeID
where (Opportunity.OpportunityID = @Opportunity or Opportunity.ProjectID = cast(@Project AS varchar(25)) *or [Order].OrderNum = @SalesOrder *)
order by Opportunity.ProjectID, Opportunity.OpportunityID, [Order].OrderNum, WorkOrder.WorkOrderID
How do I fix the placement of the @SalesOrder?
First off… @Asz0ka gave you the answer.
What are the asterisks for, in lines like:
full outer join dbo.Order as Order on
Opportunity.ProjectID = Order.ProjectID
and Opportunity.OpportunityID = Order.OpportunityID
*and ( Order.OrderNum = @SalesOrder )*
Is that you just trying to highlight important code parts for us here?
To denote where things are bad, yes. Bold didn’t work in the code block.
The subassembly is the answer you are referring to of Adam’s?
The snippet:
full outer join dbo.Order as Order on
Opportunity.ProjectID = Order.ProjectID
and Opportunity.OpportunityID = Order.OpportunityID
*and ( Order.OrderNum = @SalesOrder )*
Should only have relationships between the tables.
( Order.OrderNum = @SalesOrder ) is a table criteria so it belongs down in the WHERE clause
I agree.
And I don’t want it there. But the Epicor code put it in there, when I used the Table Criteria on the dbo.Order:
I just want it in the Where Clause.
When I rearrange things in SSMS, it works perfectly. That’s what I was trying to say before.
I don’t understand why the BAQ puts the table criteria as a Join condition.
Try removing it from the Order table criteria and adding it as Sub-query criteria
Thanks Calvin!
I will try this when I return.
Thanks so much for the first solution!
Ben
This is the working code:
cast(‘@Project’ AS varchar(25))
Needed some apostrophes.
All working!
Thanks again!
Really? Needed the single quotes? To get it to not provide an error, or to return the expected results?
I’d have bet the farm that putting the quotes in there would turn it into a literal sting, and no longer be the parameter’s value.
It kept calling “Bad SQL Statement”.
I had to go to the server log to get the code:
"Error:System.Data.SqlClient.SqlException (0x80131904): "
Need to declare @Project.
The single quotes did the trick…
And you get different results when you run it with different values of the input parameter Project ?
100%.
It works perfectly now.
Run it with a known good value for @ProjectID, and known bad values (values that should have no match) for @SalesOrder and @Oppurtunity. I bet it comes up with no records