This may sound crazy, but I would like to have a way to return the parameter that is passed in to a BAQ if there are no records found. So, instead of “No records found” I would just get the parameter that I passed into the query as the return. Any thoughts?
Not to sure the context of your query so this may not be completely applicable to your problem but you can do a calculated field at the top level that checks for count of rows on a subquery, if no rows then return the parameter.
Can you provide any details on your BAQ?
Fernando,
This sounds like exactly what I need. Unfortunately, when I try to create it I get the error below:
Clearly I am missing something.
My subquery2 is one table, Erp.OrderHed with one display field OrderHed_OrderNum. The criteria is ECCOrderNum=@WebOrderNum
With @WebOrderNum as a required parameter.
Any thoughts?
In your screenshot, I don’t see “Subquery2” as an available table. You may need to bring it in the query builder tab.
Also, I should have mentioned but I didnt know what you were trying to return, in that case statement you should return the OrderNum if the rows are greater than 0 because as it sits, you’ll only ever get results when the rows returns are 0. Unless that’s what you’re going for.
Something like this.
CASE
WHEN COUNT(SubQuery2.OrderHed_OrderNum) = 0 THEN @WebOrderNum
ELSE SubQuery2.OrderHed_OrderNum
END
Edit, just thought if you do add the “else” part of the statement it will probably will expect SubQuery2.OrderHed_OrderNum to be a display field on the top level which will mess with some things.
Thanks Fernanado, you solved my problem! Actually, returning nothing is what I am looking for in this case. I am using this with the REST services to see if a web order already exists and I need it to return the parameter I pass if it is not a duplicate. I clearly need to brush up on sub-queries in BAQ.
1 Like