[firebird-support] where condition
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
AW: [firebird-support] where condition
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
Re: [firebird-support] where condition
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/
[firebird-support] Date Literals
Hi, I'm in the progress of converting an existing application from Sybase SQL (11.0.3 - from the stoneage) to Firebird. From Sybase (as well as some other databases I work with) I'm used to entering date literals as 'mmdd'. I keep getting Overflow occured during data type conversion. Is it true, that this date format isn't recognized by firebird? Thanks Lutz (3rd try. Sending to newsgroup gmane.comp.db.firebird.user or sending via yahoo's New Topic button didn't seem to work - or am I just too impatient?)
[firebird-support] Date literals
Hi, I'm in the progress of converting an existing application from Sybase SQL (11.0.3 - from the stoneage) to Firebird.From Sybase (as well as some other databases I work with) I'm used to entering date literals as 'mmdd'.I keep getting Overflow occured during data type conversion.Is it true, that this date format isn't recognized by firebird?Which is the most common date format, can you recommend any format?Thanks Lutz
Re: [firebird-support] Date Literals
On 14-8-2014 10:29, Lutz Kutscher grenzhan...@yahoo.com [firebird-support] wrote: (3rd try. Sending to newsgroup gmane.comp.db.firebird.user or sending via yahoo's New Topic button didn't seem to work - or am I just too impatient?) This mailinglist is moderated. So your message might not show up immediately. Instead of using the gmane newsgroup, or Yahoo Groups atrocious webinterface, I suggest you use the mailinglist. Mark -- Mark Rotteveel
Re: [firebird-support] Date literals
grenzhan...@yahoo.com [firebird-support] wrote: Hi, I'm in the progress of converting an existing application from Sybase SQL (11.0.3 - from the stoneage) to Firebird.From Sybase (as well as some other databases I work with) I'm used to entering date literals as 'mmdd'.I keep getting Overflow occured during data type conversion.Is it true, that this date format isn't recognized by firebird?Which is the most common date format, can you recommend any format?Thanks Lutz May be this : http://www.firebirdsql.org/en/firebird-date-literals/ is of help. From a short skimming: Firebird uses the separator to decide, see the chapter Separators in Non-U.S. Dates at the half hth, marcus [Non-text portions of this message have been removed]
Re: [firebird-support] Date literals
On 14-8-2014 10:17, grenzhan...@yahoo.com [firebird-support] wrote: Hi, I'm in the progress of converting an existing application from Sybase SQL (11.0.3 - from the stoneage) to Firebird.From Sybase (as well as some other databases I work with) I'm used to entering date literals as 'mmdd'.I keep getting Overflow occured during data type conversion.Is it true, that this date format isn't recognized by firebird?Which is the most common date format, can you recommend any format?Thanks Lutz You can use the ISO-8601 format: '-MM-dd', optionally with explicit typing: DATE'-MM-dd' Note that Firebird also supports some other local formats (like MM-dd-, MM/dd/ and dd.MM.), but I think it is better to stick to ISO-8601. Mark -- Mark Rotteveel
RE: [firebird-support] Date literals
Lutz,
The following Format ‘-MM-dd’ should work.
Select CAST( LEFT( '20140814',4) ||'-'|| substring('20140814' from 5 for 2)
||'-'||RIGHT('20140814',2) as DATE) from RDB$DATABASE
In the past I had some obstacles converting datetime values from another source
to the latest Firebird DB.
FireBird’s Dialect 1 will store the Date Time value in one field of data type
“DATE”. Using Dialect 3, the Data Type “DATE” only stores the Date part of the
value. You would need to parse it and store the Time part of the field to a
field of Data Type TIME, or use the TIMESTAMP data Type which handles the Date
Time.
Thanks,
Ed Mendez
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Thursday, August 14, 2014 4:18 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Date literals
Hi, I'm in the progress of converting an existing application from Sybase SQL
(11.0.3 - from the stoneage) to Firebird.From Sybase (as well as some other
databases I work with) I'm used to entering date literals as 'mmdd'.I keep
getting Overflow occured during data type conversion.Is it true, that this
date format isn't recognized by firebird?Which is the most common date format,
can you recommend any format?Thanks Lutz
