Subject AW: [firebird-support] where condition
Author checkmail

This works:

and (d.bsnr = coalesce((select ag from p_getmaxag(a.teilenr,:typ,1)),d.bsnr))

but how can I get the records without a bsnr, without an workstep, no record in the table d.bsnr..)






1st, the following condition does not work unfortunately. (call a stored procedure in the where-condition in a coalesce..)

and d.bsnr = coalesce(select ag from p_getmaxag(:teilenr,:typ,1),d.bsnr) and d.bsnr < coalesce (select ag from p_getmaxag(:teilenr,:typ,2),100)


I have some article, several with worksteps, other without this.


For example

ArticleA =lamp

ArticleB      Workstep2= bicycle mill-cut

ArticleB      Workstep2= bicycle varnish


Now I have a stored procedure where I can get the entire stock, input parameter the type (material (no workstep), unfinish products (workstep < the last workstep) = typ2 and finished products (last workstep = typ 3 input parameter)

If the input typ = 1, I should get all material, typ = 2 all unfinished products…


And this I will write in a sql where condition, preferably without an execute statement. IIF and Case do not work in the condition, neither the procedure call. (p_getmaxag)


How can I realize this simply?


Thanks in advance


Best regards