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

seem to be a possible, but probably not very performant approach.

Doing a left join with the procedure in case the stuff before your
leading "and" holds true could be a solution...

Thomas


Am 14.08.2014 15:49, schrieb 'checkmail' check_mail@...
[firebird-support]:

>
>
> 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..)
>
>
>
> Thanks
>
> ----
>
> Hello,
>
>
>
> 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
>
>
>
> Olaf
>
>
>
>
>
>

--
Mit freundlichen Grüßen,

Thomas Beckmann
Diplom-Informatiker


Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckmann@... <mailto:thomas.beckmann@...>

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
info@... <mailto:info@...> • www.assfinet.de
<http://www.assfinet.de/>

Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.