[firebird-support] COALESCE
So I'm getting errors when there's a call to COALESCE with only a single
parameter. (Why would we write code like that? - because nobody thought
to spot it as a special case in our query generation code, perhaps.)
So what does the Firebird book say? - well, my copy appears to say
COALESCE (value 1> { , value 2 [, ...value n })
where {} indicates "mandatory", so it looks like there must be at least
two parameters, so our query generation code is wrong.
Jolly good. So why am I pointing this out? (Other than that if the
designer of the syntax for COALESCE had considered the possibility of
machine-generated code they might have reached a different decision
about the second parameter, or even the first, being mandatory?)
I'm just pointing out the typo in the book - there's no closing square
bracket in that definition.
--
Tim Ward
++
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/
Re: AW: AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?
Hi, Thank you for providing a script, this is much easier comparing. I can now reproduce the error you get. Please enter a ticket with this script into the tracker, so they can fix this issue. Enter these both queries, so they can quickly find the bug: The query with the wrong results: SELECT a.tnr, a.minb, coalesce(b.amount,0) as m FROM t_main a left join t_stock b on (a.tnr = b.tnr) WHERE ((a.minb > coalesce(b.amount,0))) A workaround by ignoring use of index: SELECT a.tnr, a.minb, coalesce(b.amount,0) as m FROM t_main a left join t_stock b on (a.tnr = b.tnr || '') WHERE ((a.minb > coalesce(b.amount,0))) Kind Regards, Arno
AW: AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?
Hello,
I have created an example:
SET SQL DIALECT 3;
CREATE TABLE T_MAIN (
TNR VARCHAR(16) NOT NULL,
MINB INTEGER
);
ALTER TABLE T_MAIN ADD CONSTRAINT PK_T_MAIN PRIMARY KEY (TNR);
SET SQL DIALECT 3;
CREATE TABLE T_STOCK (
TNR VARCHAR(16),
AMOUNT INTEGER
);
ALTER TABLE T_STOCK ADD CONSTRAINT FK_T_STOCK_1 FOREIGN KEY (TNR) REFERENCES
T_MAIN (TNR) ON DELETE CASCADE ON UPDATE CASCADE;
Redords:
INSERT INTO T_MAIN (TNR, MINB)
VALUES ('AAA', 0);
INSERT INTO T_MAIN (TNR, MINB)
VALUES ('BBB', 10);
INSERT INTO T_MAIN (TNR, MINB)
VALUES ('CCC', 10);
INSERT INTO T_MAIN (TNR, MINB)
VALUES ('DDD', 10);
COMMIT WORK;
INSERT INTO T_STOCK (TNR, AMOUNT)
VALUES ('AAA', 100);
INSERT INTO T_STOCK (TNR, AMOUNT)
VALUES ('BBB', 5);
INSERT INTO T_STOCK (TNR, AMOUNT)
VALUES ('CCC', 15);
COMMIT WORK;
The sql query
SELECT
a.tnr, a.minb, coalesce(b.amount,0) as m
FROM
t_main a
left join t_stock b on (a.tnr = b.tnr)
WHERE
((a.minb > coalesce(b.amount,0)))
ORDER BY
a.tnr
_-
I get ccc too. Why? Allthough I have a stock from 15.
If I delete the foreign key from table t_stock, CCC does not display! But with
this foreign key it does not work, I get CCC but CCC has a stock greater the
minimum amount (minb) and should not be displayed (like in firebird 2.5, 2.1,
2.0)
Thanks
Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Mittwoch, 9. März 2016 13:25
An: firebird-support@yahoogroups.com
Betreff: Re: AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?
Hello,
> your are right. But in FB 3, it will be handled different.
> I need the left join tlager_sum, because I’d like to have all records too
> there no records in tlager_sum.
> But in this case I get now in fb 3 the tteile.teilenr clean without the
> amaount of tlager_sum (the a.minb is greater than the coalesce(b.menge,0)
> and in this case, I get no referenced Record from tlager_sum), the one
> from tteile now in every case (left join).
> Finally, I would get all records with a.minb > b.menge and all records,
> where a.minb is present an there is no record from the teilenr in
> tlager_sum.
Please, give some DDL with example data.
I've tried to reproduce your issue on FB3.0 RC2, but i could not reproduce
it.
Kind Regards,
Arno Brinkman
AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?
Hello, your are right. But in FB 3, it will be handled different. I need the left join tlager_sum, because I’d like to have all records too there no records in tlager_sum. But in this case I get now in fb 3 the tteile.teilenr clean without the amaount of tlager_sum (the a.minb is greater than the coalesce(b.menge,0) and in this case, I get no referenced Record from tlager_sum), the one from tteile now in every case (left join). Finally, I would get all records with a.minb > b.menge and all records, where a.minb is present an there is no record from the teilenr in tlager_sum. Thanks Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Mittwoch, 9. März 2016 09:04 An: firebird-support@yahoogroups.com Betreff: Re: AW: [firebird-support] coalesce bug in fb 3.0 rc2? Hello, > sorry, outlook corrects me in German. At the moment, I have no sulution > for the problem. Anything ideas? Query: SELECT a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ FROM tteile a left join tlager_sum b on (a.teilenr = b.teilenr) WHERE ((a.minb > coalesce(b.menge,0)) and (a.typ = 1)) ORDER BY a.teilenr Result: TEILENR BEZEICHNUNG MINB M TYP PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm 280 420 1 I would say your TLAGER_SUM contains a record with: TEILENR MENGE PrSt110x44x3 420 So results are correct? If not give sample records for tteile and tlager_sum with DDL Kind Regards, Arno Brinkman ABVisie
Re: AW: [firebird-support] coalesce bug in fb 3.0 rc2?
On 2016-03-09 7:57, 'Checkmail' check_m...@satron.de [firebird-support] wrote: > Hello again, > > sorry, outlook corrects me in German. At the moment, I have no > sulution for the problem. Anything ideas? I think you should create a sample database to demonstrate this problem, and create a ticket in the tracker with all the details. Mark
AW: [firebird-support] coalesce bug in fb 3.0 rc2?
Hello again, sorry, outlook corrects me in German. At the moment, I have no sulution for the problem. Anything ideas? Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Dienstag, 8. März 2016 16:39 An: firebird-support@yahoogroups.com Betreff: AW: [firebird-support] coalesce bug in fb 3.0 rc2? Hell Mark, I would get all material, where the minimum inventory amount iss maller the the inventory amount. Tlager_sum contains the inventory amount, tteile.minb the theoretical minimum amount it should be. Now, the simplified query gets all records where the amount is to small and with the „where a.minb > coalesce(b.menge)) I get also the records, where is no inventory amount present. If I let the code: select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) where ((a.minb > coalesce(b.menge,0)) and (a.typ = 1)) order by a.teilenr I get m = 0 for records, who has a inventory amount greater than the minimal inventory amount. If I change the where ((a.minb > coalesce(b.menge,0)) to where ((a.minb > b.menge).. the records who has a greater inventory amount than the minimal one will be not displayed, but also not these who has no inventory amount (null in tlager_sum) In firebird 2.5 and earlier versions it works fine in both situations. The result of the unchanged code: TEILENRBEZEICHNUNGMINBMTYP PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm280 0 1 Inventory amount = 0 The result without coalesce: The record is no present Without the condition >.. TEILENRBEZEICHNUNGMINB M TYP PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm280 420 1 I get 420 as inventory amount, 280 should it be as minimal inventory amount, aka minb What can be the problem? Von: firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> [mailto:firebird-support@yahoogroups.com] Gesendet: Dienstag, 8. März 2016 16:26 An: firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> Betreff: Re: [firebird-support] coalesce bug in fb 3.0 rc2? On 2016-03-08 16:18, 'Checkmail' check_m...@satron.de <mailto:check_m...@satron.de> [firebird-support] wrote: > Hello, > > in the further version of firebird I can execute the following and > get > the real value of m (exists in tlager_sum) > > select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, > a.typ > > from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) > > where ((a.minb > coalesce(b.menge,0)) > > and (a.typ = 1)) order by a.teilenr > > In any case when it is no amount in the table tlager_sum (null), it > should seen as 0 (not present) and if the minimum amount iss higher.. > > But, since fb 3 I get 0, why? The tlager_sum has for many records a > mass. If I change to > > a.minb > b.menge > > it works for these records, but not for any with no amount. > > What can be the issue? It is not clear to me what your problem is. Could you describe it in more detail and provide a sample dataset, expected results and actual results? Mark
AW: [firebird-support] coalesce bug in fb 3.0 rc2?
Hell Mark, I would get all material, where the minimum inventory amount iss maller the the inventory amount. Tlager_sum contains the inventory amount, tteile.minb the theoretical minimum amount it should be. Now, the simplified query gets all records where the amount is to small and with the „where a.minb > coalesce(b.menge)) I get also the records, where is no inventory amount present. If I let the code: select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) where ((a.minb > coalesce(b.menge,0)) and (a.typ = 1)) order by a.teilenr I get m = 0 for records, who has a inventory amount greater than the minimal inventory amount. If I change the where ((a.minb > coalesce(b.menge,0)) to where ((a.minb > b.menge).. the records who has a greater inventory amount than the minimal one will be not displayed, but also not these who has no inventory amount (null in tlager_sum) In firebird 2.5 and earlier versions it works fine in both situations. The result of the unchanged code: TEILENRBEZEICHNUNGMINBMTYP PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm280 0 1 Inventory amount = 0 The result without coalesce: The record is no present Without the condition >.. TEILENRBEZEICHNUNGMINB M TYP PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm280 420 1 I get 420 as inventory amount, 280 should it be as minimal inventory amount, aka minb What can be the problem? Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Dienstag, 8. März 2016 16:26 An: firebird-support@yahoogroups.com Betreff: Re: [firebird-support] coalesce bug in fb 3.0 rc2? On 2016-03-08 16:18, 'Checkmail' check_m...@satron.de <mailto:check_m...@satron.de> [firebird-support] wrote: > Hello, > > in the further version of firebird I can execute the following and > get > the real value of m (exists in tlager_sum) > > select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, > a.typ > > from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) > > where ((a.minb > coalesce(b.menge,0)) > > and (a.typ = 1)) order by a.teilenr > > In any case when it is no amount in the table tlager_sum (null), it > should seen as 0 (not present) and if the minimum amount iss higher.. > > But, since fb 3 I get 0, why? The tlager_sum has for many records a > mass. If I change to > > a.minb > b.menge > > it works for these records, but not for any with no amount. > > What can be the issue? It is not clear to me what your problem is. Could you describe it in more detail and provide a sample dataset, expected results and actual results? Mark
Re: [firebird-support] coalesce bug in fb 3.0 rc2?
On 2016-03-08 16:18, 'Checkmail' check_m...@satron.de [firebird-support] wrote: > Hello, > > in the further version of firebird I can execute the following and > get > the real value of m (exists in tlager_sum) > > select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, > a.typ > > from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) > > where ((a.minb > coalesce(b.menge,0)) > > and (a.typ = 1)) order by a.teilenr > > In any case when it is no amount in the table tlager_sum (null), it > should seen as 0 (not present) and if the minimum amount iss higher.. > > But, since fb 3 I get 0, why? The tlager_sum has for many records a > mass. If I change to > > a.minb > b.menge > > it works for these records, but not for any with no amount. > > What can be the issue? It is not clear to me what your problem is. Could you describe it in more detail and provide a sample dataset, expected results and actual results? Mark
[firebird-support] coalesce bug in fb 3.0 rc2?
Hello, in the further version of firebird I can execute the following and get the real value of m (exists in tlager_sum) select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) where ((a.minb > coalesce(b.menge,0)) and (a.typ = 1)) order by a.teilenr In any case when it is no amount in the table tlager_sum (null), it should seen as 0 (not present) and if the minimum amount iss higher.. But, since fb 3 I get 0, why? The tlager_sum has for many records a mass. If I change to a.minb > b.menge it works for these records, but not for any with no amount. What can be the issue? Thanks Best regards Olaf
AW: [firebird-support] coalesce
Okay, Thanks @ll! It was in fact a char instead a varchar item. Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Mittwoch, 25. März 2015 22:11 An: firebird-support@yahoogroups.com Betreff: RE: [firebird-support] coalesce one short question, why is the Result of coalesce(:variableVarchar(10),'NULL') not ,NULL' but rather 'NULL ' with blanks from varchar(10) length? What tool/application are you using to evaluate the result? As Dmitry and Mark have already pointed out, the result should be a VarChar(10) (i.e. without any trailing spaces) It may be the tool/application handling the result which is adding the extra blanks. Sean
[firebird-support] coalesce
Hey everyone, one short question, why is the Result of coalesce(:variableVarchar(10),'NULL') not ,NULL' but rather 'NULL ' with blanks from varchar(10) length? Thank you. Best regards. Olaf
RE: [firebird-support] coalesce
one short question, why is the Result of coalesce(:variableVarchar(10),'NULL') not ,NULL' but rather 'NULL ' with blanks from varchar(10) length? What tool/application are you using to evaluate the result? As Dmitry and Mark have already pointed out, the result should be a VarChar(10) (i.e. without any trailing spaces) It may be the tool/application handling the result which is adding the extra blanks. Sean
[firebird-support] Coalesce can't be optimised?
I've got a query like: SELECT ... FROM TBL WHERE COALESCE( FIELD1, FIELD2 ) = 12345 where FIELD2 is the primary key and FIELD1 also has an index (in fact it's a foreign key back to FIELD2). This results in a table scan. However expanding the COALESCE by hand: SELECT ... FROM TBL WHERE ( FIELD1 = 12345 ) OR ( FIELD1 IS NULL AND FIELD2 = 12345) results in use of both indices and no table scan. (In the vast majority of cases FIELD1 is null so I just want a lookup of a single record by primary key; just occasionally FIELD1 is not null and I might want two or three records in the result set.) So, my questions: (1) Have I got this right? Is the second query a correct expansion of COALESCE that produces the same results as the first query? (2) If so, is it indeed the case that the optimiser doesn't understand how to expand COALESCE? -- Tim Ward
Re: [firebird-support] COALESCE() + CAST() converts timestamp format
RE: [firebird-support] COALESCE() + CAST() converts timestamp format
By the way, Bhavbhuti, CAST(COALESCE(tPB.dBillDt, '') AS TIMESTAMP) AS tDocDt will fail if tPB.dBillDt is null since '' cannot be converted to a timestamp (it is neither a timestamp nor null). Set
Re: [firebird-support] COALESCE() + CAST() converts timestamp format
RE: [firebird-support] COALESCE() + CAST() converts timestamp format
Thanks Svein. Would you suggest I double COALESCE it? I think it will be an overkill and I might end up with an ANSI datetime again. Or should I not COALESCE it at all? I wanted a blank date time in case of a NULL for tBillDt and if there is a date in tBillDt I need it for date calculation later on as post processing from the front ends. What is a blank date, Bhavbhuti? Either it is NULL (unknown) or an actual date. Blank as in '' is a (var)char concept, it doesn't exist for dates or timestamps and give an error. So you basically have to choose between having a date for calculation or a string for display. If you're thinking in terms of WHERE clauses, you may sometimes use IS [NOT] DISTINCT FROM as an alternative to = or . HTH, Set
Re: [firebird-support] COALESCE() + CAST() converts timestamp format
Re: [firebird-support] COALESCE() + CAST() converts timestamp format
Svein, Bhavbhuti has a background as a Visual FoxPro developer and in that language exist empty dates. Probably is the reason why he had casted an empty string as a timestamp. The same thing had happened to me when I was learning SQL. Greetings. Walter. On Tue, May 13, 2014 at 4:02 AM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] firebird-support@yahoogroups.com wrote: Thanks Svein. Would you suggest I double COALESCE it? I think it will be an overkill and I might end up with an ANSI datetime again. Or should I not COALESCE it at all? I wanted a blank date time in case of a NULL for tBillDt and if there is a date in tBillDt I need it for date calculation later on as post processing from the front ends. What is a blank date, Bhavbhuti? Either it is NULL (unknown) or an actual date. Blank as in '' is a (var)char concept, it doesn't exist for dates or timestamps and give an error. So you basically have to choose between having a date for calculation or a string for display. If you're thinking in terms of WHERE clauses, you may sometimes use IS [NOT] DISTINCT FROM as an alternative to = or . HTH, Set
Re: [firebird-support] COALESCE() + CAST() converts timestamp format
Hi Bhavbhuti, I do not know the exact solution, but I'd say: COALESCE(CAST(tPB.dBillDt AS TIMESTAMP), '') AS tDocDt converts timestamp inside servers environment to a string - the returning client receives a string and does not know about it's datatype - yet does not a apply specific translations. With CAST(COALESCE(tPB.dBillDt, '') AS TIMESTAMP) AS tDocDt it's just the other way round: While the server deals with string and timestamp, the client receives a timestamp an does know what to with it. I'd say, this behavior is expectable... Best regards, Thomas Am 10.05.2014 15:21, schrieb venussof...@gmail.com [firebird-support]: Hi all I have the following in my SELECT statement COALESCE(CAST(tPB.dBillDt AS TIMESTAMP), '') AS tDocDt The COALESCE() converts the CAST()ed TIMESTAMP to ANSI standard 2014-05-03 00:00:00. CAST(COALESCE(tPB.dBillDt, '') AS TIMESTAMP) AS tDocDt The CAST() converts the COALESCE()ed TIMESTAMP to my regional and expected format 03.05.2014, 00:00:00. Is this an expected behavior? Please advise. Thanks and regards Bhavbhuti -- 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.
