BAQ with Case statement in calculated field?

I have a BAQ that looks like this:

select 
	[UD16].[Key2] as [UD16_Key2],
	[UD16].[Date01] as [UD16_Date01],
	[UD16].[Character01] as [UD16_Character01],
	[UD16].[Date02] as [UD16_Date02],
	[UD16].[Character02] as [UD16_Character02],
	[UD16].[Number01] as [UD16_Number01],
	[UD16].[CheckBox01] as [UD16_CheckBox01],
	[UD16].[CheckBox02] as [UD16_CheckBox02],
	[UD16].[Date03] as [UD16_Date03],
	[EmpBasic].[SupervisorID] as [EmpBasic_SupervisorID],
	[UD16].[Character03] as [UD16_Character03]
from Ice.UD16 as UD16
inner join Erp.EmpBasic as EmpBasic on 
	UD16.Key1 = EmpBasic.EmpID
where (UD16.Key1 = @EmpID  and UD16.Date02 >= Constants.Today)
order by UD16.Date02 Desc

The problem is that I want to pull the name of the person whose employee ID is stored in UD_Character03. If it isn’t filled in I want to just return an empty string. In my SQL experience I would have done something like:

select 
	[UD16].[Key2] as [UD16_Key2],
	[UD16].[Date01] as [UD16_Date01],
	[UD16].[Character01] as [UD16_Character01],
	[UD16].[Date02] as [UD16_Date02],
	[UD16].[Character02] as [UD16_Character02],
	[UD16].[Number01] as [UD16_Number01],
	[UD16].[CheckBox01] as [UD16_CheckBox01],
	[UD16].[CheckBox02] as [UD16_CheckBox02],
	[UD16].[Date03] as [UD16_Date03],
	[EmpBasic].[SupervisorID] as [EmpBasic_SupervisorID],
	[UD16].[Character03] as [UD16_Character03],
       case when [UD16].[Character03]  is null then '' else (select Name from EmpBasic where EmpID = [UD16].[Character03] ) end as SuperName
from Ice.UD16 as UD16
inner join Erp.EmpBasic as EmpBasic on 
	UD16.Key1 = EmpBasic.EmpID
where (UD16.Key1 = @EmpID  and UD16.Date02 >= Constants.Today)
order by UD16.Date02 Desc

However, I get the Possible SQL injection: ‘Select’ error. I can’t seem to figure out how within the BAQ environment to get that to work. What do I need to do to get this to work?

Utilize the tools that Epicor provides. As you can see in my screenshot, there is a case statement that Epicor will insert into the Editor for you. You can then edit as needed if you need more or less WHEN statements.

image

Putting the Case statement in isn’t the issue. The problem is getting the Select statement into the Else portion of the Case. If I try to put in anything starting with the work Select it seems to throw the SQL Injection error.

Why are you doing the select statement when you already have the join defined. Just do

else EmpBasic_Name

You can do another join with EmpBasic (alias it appropriately) on UD_Character03 = AliasEmpBasic.EmpID.

It’s not the same data. The empBasic in the join is to get the employee’s supervisor number. However, the person who does the approval may not be their supervisor but another authorized user. Therefore I need to see what the number is stored in the UD16.Character03 to get who it is then look up their name. As far as another join goes, if the UD16.Character03 is null I don’t get all records returned if I try to add that to the query itself. That is why I was trying to do the Case within a display field so that I get all records whether they have been approved (the value filled in) or not.

Add the EmpBasic a second time and do a left outer join.

The left outer join is only returning the rows that have the UD16.Character 03 filled in. Unless I am linking it wrong. Here is what the sql looks like:

select 
	[UD16].[Key2] as [UD16_Key2],
	[UD16].[Date01] as [UD16_Date01],
	[UD16].[Character01] as [UD16_Character01],
	[UD16].[Date02] as [UD16_Date02],
	[UD16].[Character02] as [UD16_Character02],
	[UD16].[Number01] as [UD16_Number01],
	[UD16].[CheckBox01] as [UD16_CheckBox01],
	[UD16].[CheckBox02] as [UD16_CheckBox02],
	[UD16].[Date03] as [UD16_Date03],
	[EmpBasic].[SupervisorID] as [EmpBasic_SupervisorID],
	[UD16].[Character03] as [UD16_Character03],
	[EmpBasic1].[Name] as [EmpBasic1_Name]
from Ice.UD16 as UD16
inner join Erp.EmpBasic as EmpBasic on 
	UD16.Key1 = EmpBasic.EmpID
left outer join Erp.EmpBasic as EmpBasic1 on 
	UD16.Character03 = EmpBasic1.EmpID
where (UD16.Key1 = @EmpID  and UD16.Date02 >= Constants.Today)
order by UD16.Date02 Desc

Can you post a screenshot of the BAQ? Specifically the Phrase Build.

That’s right, there is a way to do a join that allows for nulls. I’ll have to see if I can find it or maybe someone else will jump in.

Maybe you can make something like this to work?

select 
	[UD16].[Key2] as [UD16_Key2],
	[UD16].[Character03] as [UD16_Character03],
	(case when [UD16].[Character03]  is null then ''
 else SubQuery2.EmpBasic1_Name end) as [Calculated_SuperName]
from Erp.EmpBasic as EmpBasic
inner join Ice.UD16 as UD16 on 
	UD16.Key1 = EmpBasic.EmpID
left outer join  (select 
	[EmpBasic1].[EmpID] as [EmpBasic1_EmpID],
	[EmpBasic1].[Name] as [EmpBasic1_Name]
from Erp.EmpBasic as EmpBasic1
right outer join Ice.UD16 as UD161 on 
	UD161.Character03 = EmpBasic1.EmpID)  as SubQuery2 on 
	UD16.Character03 = SubQuery2.EmpBasic1_EmpID

I could not find the information on how I did it before. Play around with doing two lines for the join enclosed in parenthesis and with an OR. Make the first one Character03 = EmpID and the second one something like Character03 = ISNULL. I know it can be done, just forget how.