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_m...@satron.de [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.beckm...@assfinet.de <mailto:thomas.beckm...@assfinet.de> ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de <mailto:i...@assfinet.de> • 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. ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
