[firebird-support] Validation error for unknown column
Hi, I created a table with a numeric non null field. Afterwards I realized that it could be a calculated field, so I dropped the field and recreated it accordingly. Before: NETO CURRENCY NOT NULL, IVA CURRENCY NOT NULL, TOTAL CURRENCY NOT NULL After: NETO CURRENCY NOT NULL, IVA CURRENCY NOT NULL, TOTAL COMPUTED BY (NETO + IVA) ( Being CURRENCY a domain NUMERIC(18,2) ) Then I tried to insert a record in the table using a stored procedure, but the insertion failed giving the following strange message : "validation error for column *** unknown ***, value "*** null ***" Then I tried to insert the record directly in the table, using no stored procedure, but I got the same result. I guess that the "unknown" column has to do with the dropped and recreated TOTAL field, and something remainend in memory related to its non null constraint. I dropped and recreated the stored procedure, and then error dissapeared. I can insert records with no problem now. For security, I restarted Firebird and checked the database using gfix validate. No errors were shown. Does anyone have any idea of what produced such a strange error ? Thanks in advance, Aldo Caruso ++ 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: [firebird-support] Firebird migration to increase perfomances
>Yes I just checked my original query will return 6 rows without the distinct
OK, then lets add another CTE so that the calculations aren't multiplied:
with tmp1(surname, name, trxdate, source, job_number, creditnr, sourceid,
serviceadvisOR) as
(select distinct trim(c.name||' '||c.surname), trim(ca.name), j.jdate,
j.source, i.job_number, j.source2, j.sourceid, s.name||' '||s.surname
from journal j
join invoices i on j.source = i.inv_nr
left join customer c on j.cid= c.cid
left join cashcustomer ca on j.source = ca.invnr
and i.job_number = ca.jobnr
left join staff s on j.serviceadvisOR = s.staff_id
where j.source not starting 'STOCK UNIT'
and j.jdate between '2015/06/01' AND '2015/06/22'
and j.source not starting 'DEPOSIT'
and j.cid <> 0
and j.module <> 2
and ((j.source2 not starting 'S/D'
and j.source2 not starting 'P/D')
or j.source2 is null)
and j.description not starting 'SPLIT INV'
and j.jtype in (1, 4)),
tmp2(surname, name, trxdate, source, job_number, creditnr, sourceid,
serviceadvisOR, credVAT, VAT,invoicetotalINC, credinc, creditnote,
invoiceINCRedo, invoiceVATRedo, cost) as
(select t.surname, t.name, t.jdate, t.source, t.job_number, t.creditnr,
t.sourceid, t.serviceadvisOR
sum(iif(j.accnr = '5995300' and j.module in (66, 77) and t.source2 =
j.source2 and j.jtype = 1, coalesce(j.creditamount, 0) -
coalesce(j.debitamount, 0), 0)),
sum(iif(j.accnr = '5995300' and j.jtype = 1, coalesce(j.creditamount,
0) - coalesce(j.debitamount, 0), 0)),
sum(iif(j.accnr = '3993100' and j.jtype = 1, j.debitamount, 0)),
sum(iif(j.accnr = '1501500' and j.jtype = 1, coalesce(j.debitamount,
0)-coalesce(j.creditamount, 0), 0)),
sum(iif(j.accnr = '1501500' and j.jtype = 4, coalesce(j.creditamount,
0)-coalesce(j.debitamount, 0), 0)),
sum(iif(j.accnr = '3993100' and j.jtype = 1 and j.module = 77,
j.creditamount, 0)),
sum(iif(j.accnr = '5995300' and j.jtype = 1 and j.module = 77,
coalesce(j.debitamount, 0)-coalesce(j.creditamount, 0), 0)),
sum(iif(j.accnr starting '6' and j.jtype = 1, coalesce(j.debitamount,
0)-coalesce(j.creditamount, 0), 0))
from tmp1 t
join journal j on t.source = j.source
and t.trxdate = j.jdate
and j.jtype in (1, 4)
and (j.accnr in ('1501500', '3993100', '5995300')
or j.accnr starting '6')
group by 1, 2, 3, 4, 5, 6, 7, 8)
select surname, name, trxdate, source, job_number, creditnr, sourceid,
serviceadvisOR, credVAT, VAT, invoicetotalINC, invoicetotalINC-VAT+credvat as
invoicetotal,
credinc+creditnote-invoiceINCRedo+invoiceVATRedo credits,
invoicetotalINC-VAT+credinc+creditnote-invoiceINCRedo as nett, cost,
invoicetotalINC-VAT+credinc+creditnote-cost-invoiceINCRedo as gp,
invoicetotalINC-VAT+credinc+creditnote-cost-invoiceINCRedo/NULLIF(invoicetotalINC-VAT+credinc+creditnote,
0)*100 as gppersent
from tmp
where invoiceINC-invoicevat+credvat <> 0
order by 1,3,4
Does this get you the right result?
By the way, I'm positively surprised if the performance improved from almost 16
seconds to 0.16 seconds, I'd hoped for up to a 10-fold improvement, not
100-fold. I'm sure you didn't mean 0.16 milliseconds, Firebird is simply not
that quick.
HTH,
Set
Re: [firebird-support] Issues migrating from FB 1.5 to 2.5
srotyli...@gmail.com [firebird-support] schrieb am 23.06.2015 14:31: > Hi Karol, I think you are right on, I found this which is what I suspect is > throwing the error in 2.5 (but not so in 1.5). I might have to go back to my > old box running 1.5. Do you know if it is possible to convert and downgrade a > 2.5 *.fdb file to a 1.5 version ? > Multiple Hits to Same Column Now Illegal > It is no longer allowed to make multiple “hits” on the same column in an > INSERT or UPDATE statement. Thus, a statement like > INSERT INTO T(A, B, A) ... > or > UPDATE T SET A = x, B = y, A = z > will be rejected in Firebird 2.n, even though it was tolerated in > InterBase and previous Firebird versions. You may check out the "OldSetClauseSemantics" legacy configuration option in firebird.conf. Mind the note in the release notes that this configuration option might be removed in future releases. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Issues migrating from FB 1.5 to 2.5
Hi Karol, I think you are right on, I found this which is what I suspect is throwing the error in 2.5 (but not so in 1.5). I might have to go back to my old box running 1.5. Do you know if it is possible to convert and downgrade a 2.5 *.fdb file to a 1.5 version ? Multiple Hits to Same Column Now Illegal It is no longer allowed to make multiple “hits” on the same column in an INSERT or UPDATE statement. Thus, a statement like INSERT INTO T(A, B, A) ... or UPDATE T SET A = x, B = y, A = z will be rejected in Firebird 2.n, even though it was tolerated in InterBase and previous Firebird versions.
RE: [firebird-support] Firebird migration to increase perfomances
Yes I just checked my original query will return 6 rows without the distinct Stef From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 23 June 2015 01:16 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] Firebird migration to increase perfomances >Hi Set > >Wow your query runs like a bat out of hell J 0.16 Milliseconds !! and it >easier to maintain as you say. > >The only issue is that it somehow multiplies the totals by 6 on all results >as compared mine? > >i.e. mine will return an invoice total of say 18500.00 and yours will return >111000.00 > >This is across all the results any idea why? Would you get six times as many rows in your result set with your original query if you didn't have the DISTINCT, Stef? Or are any of the JOINs returning more than one row - e.g. are there six invoices for each entry in journal? This should be fairly simple to fix, but more information about the tables and how unique each JOIN are would be required. Set [Non-text portions of this message have been removed]
RE: [firebird-support] Firebird migration to increase perfomances
Hi Set Below is the actual data from the journal table as per the example I mentioned in my previous email Both the customer and cashcustomer tables only have 1 record each JOURID JDATE JTYPE MODULE TRXDATE SOURCEID SOURCE DEBITAMOUNT DESCRIPTION STAFFID STATUS ACCNR ACCCLASS LEDGERTYPE CREDITAMOUNT DEPARTMENT COMPANY SOURCE2 CID VSID SUPPID DDATE SOURCENAME FPERIOD RECONCILED BATCH HASVAT SERVICEADVISOR TRANSNR CLOSED 646358 05.06.2015 1 1 05.06.2015 51424 40451 0.00 PART SALES 16 NEW 1501200 1 Cr 18 500.00 50 51424 0 0 05.06.2015 51424 CASH SALE SHARON 2015-6 16 63619 646359 05.06.2015 1 1 05.06.2015 51424 40451 2 556.25 COS PARTS 16 NEW 6506200 6 Cr 0.00 50 51424 0 0 05.06.2015 51424 CASH SALE SHARON 2015-6 16 63619 646360 05.06.2015 1 1 05.06.2015 51424 40451 0.00 PART SALES 16 NEW 1501200 1 Cr 0.00 50 51424 0 0 05.06.2015 51424 CASH SALE SHARON 2015-6 16 63619 646361 05.06.2015 1 1 05.06.2015 51424 40451 0.00 PARTS 16 NEW 3993200 3 Cr 2 556.25 99 51424 0 0 05.06.2015 51424 CASH SALE SHARON 2015-6 16 63619 646362 05.06.2015 1 1 05.06.2015 51424 40451 21 090.00 ACCOUNTS RECEIVABLE 16 NEW 3993100 3 Cr 0.00 99 51424 0 0 05.06.2015 51424 CASH SALE SHARON 2015-6 16 63619 646363 05.06.2015 1 1 05.06.2015 51424 40451 0.00 VAT ON SALES 16 NEW 5995300 5 Cr 2 590.00 99 51424 0 0 05.06.2015 51424 CASH SALE SHARON 2015-6 16 63619 647446 09.06.2015 4 1 08.06.2015 51424 40451 21 090.00 BANK ACOUNTS 16 NEW 3993002 3 Dr 0.00 99 51424 0 0 05.06.2015 51424 CASH SALE SHARON 2015-6 35273 16 63836 647447 09.06.2015 4 1 08.06.2015 51424 40451 0.00 ACCOUNTS RECEIVABLE 16 NEW 3993100 3 Dr 21 090.00 99 51424 0 0 05.06.2015 51424 CASH SALE SHARON 2015-6 35273 16 63836 Hope this helps Stef From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 23 June 2015 01:16 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] Firebird migration to increase perfomances >Hi Set > >Wow your query runs like a bat out of hell J 0.16 Milliseconds !! and it >easier to maintain as you say. > >The only issue is that it somehow multiplies the totals by 6 on all results >as compared mine? > >i.e. mine will return an invoice total of say 18500.00 and yours will return >111000.00 > >This is across all the results any idea why? Would you get six times as many rows in your result set with your original query if you didn't have the DISTINCT, Stef? Or are any of the JOINs returning more than one row - e.g. are there six invoices for each entry in journal? This should be fairly simple to fix, but more information about the tables and how unique each JOIN are would be required. Set [Non-text portions of this message have been removed]
RE: [firebird-support] Firebird migration to increase perfomances
>Hi Set > >Wow your query runs like a bat out of hell J 0.16 Milliseconds !! and it >easier to maintain as you say. > >The only issue is that it somehow multiplies the totals by 6 on all results >as compared mine? > >i.e. mine will return an invoice total of say 18500.00 and yours will return >111000.00 > >This is across all the results any idea why? Would you get six times as many rows in your result set with your original query if you didn't have the DISTINCT, Stef? Or are any of the JOINs returning more than one row - e.g. are there six invoices for each entry in journal? This should be fairly simple to fix, but more information about the tables and how unique each JOIN are would be required. Set
RE: [firebird-support] Firebird migration to increase perfomances
Hi Set Wow your query runs like a bat out of hell J 0.16 Milliseconds !! and it easier to maintain as you say. The only issue is that it somehow multiplies the totals by 6 on all results as compared mine? i.e. mine will return an invoice total of say 18500.00 and yours will return 111000.00 This is across all the results any idea why? Stef From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 22 June 2015 09:29 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Firebird migration to increase perfomances >Select distinct trim(surname)as surname, trim(name)as name, trxdate, source, job_number, creditnr, sourceid, serviceadvisOR, (credvat)as credvat, (invoiceVAT) AS VAT, > (invoiceINC)AS invoicetotalINC, (invoiceINC-invoicevat+credvat)as invoicetotal, (credinc+creditnote-(invoiceINCRedo-invoiceVATRedo)) as credits, > (invoiceINC-invoicevat+(credinc))+(creditnote)-(invoiceINCRedo)as nett, (invcost) as cost, > ((invoiceINC-invoicevat+(credinc))+(creditnote))-(invcost)-(invoiceINCRedo) as gp, > invoiceINC-invoicevat+(credinc))+(creditnote))-(invcost)-(invoiceINCRedo )))/NULLIF(((invoiceINC-invoicevat+(credinc))+(creditnote)),0)*100 as gppersent >from (select (jdate)as trxdate, source, sourceid,(source2) as creditnr,i.job_number, > (select name||' '||surname from staff where staff_id = j.serviceadvisOR)as serviceadvisOR, > (select name||' '||surname from customer where cid = j.cid)as surname, > (select ca.name from cashcustomer ca > where ca.invnr = j.source AND ca.jobnr = i.job_number) as name, > (Select COALESCE(sum(debitamount),0 ) from journal > where accnr = '3993100' AND source = j.source AND jtype =1 AND jdate = j.jdate )as invoiceINC, > (Select COALESCE(sum(creditamount-debitamount),0 ) from journal > where accnr = '5995300' AND source = j.source AND jtype =1 AND jdate = j.jdate )as invoiceVAT, > (Select COALESCE(sum(creditamount),0 ) from journal where accnr = '3993100' AND source = j.source AND jtype =1 AND jdate = j.jdate AND module = 77) as invoiceINCRedo, > (Select COALESCE(sum(debitamount-creditamount),0 ) from journal > where accnr = '5995300' AND source = j.source AND jtype =1 AND jdate = j.jdate AND module = 77)as invoiceVATRedo, > (Select COALESCE(sum(creditamount-debitamount),0 ) from journal > where accnr = '5995300' AND (module = 77 OR MODULE = 66 )AND source = j.source AND source2 = j.source2 AND jtype =1 AND jdate = j.jdate) as credVAT, > (Select COALESCE(sum(creditamount-debitamount),0 ) from journal > where accnr = '5995300' AND (module = 77 OR MODULE = 66 ) AND source = j.source AND source2 = j.source2 AND jtype =4 AND jdate = j.jdate) as creditnoteVAT, > (Select COALESCE(0-sum(creditamount-debitamount),0 ) from journal > where accnr = '1501500' AND source = j.source AND jtype =1 AND jdate = j.jdate)as credinc, > (Select COALESCE(0-sum(debitamount-creditamount),0 ) from journal > where accnr = '1501500' AND source = j.source AND jtype =4 AND jdate = j.jdate)as creditnote, > (Select (COALESCE(SUM(debitamount-CREDitamount),0)) from journal > where accnr like '6%' AND source = j.source AND jtype = 1 AND jdate = j.jdate)as invcost > from journal j, invoices i > where j.source not like 'STOCK UNIT%' > AND (j.jdate >= '2015/06/01' > AND j.jDATE <= '2015/06/22') > AND j.source not like 'DEPOSIT%' > AND j.cid <> 0 > AND i.inv_nr = j.source > AND j.module <> 2 > AND (j.source2 not like 'S/D%' > AND j.source2 not like 'P/D%' > OR j.source2 is null) > AND j.description not like 'SPLIT INV%' > AND (j.jtype = 1 OR j.jtype = 4) >-- Below is where my problem lies >-- Why can I not use the result fields >-- i.e (invoiceINC-invoicevat+credvat) <> 0 >-- instead of the sql below for my <> 0 condition?? > AND ( > (Select COALESCE(sum(debitamount),0 ) > from journal where accnr = '3993100' AND source = j.source AND jtype = 1 AND jdate = j.jdate ) > - > (Select COALESCE(sum(creditamount-debitamount),0 ) > from journal where accnr = '5995300' AND source = j.source AND jtype = 1 AND jdate = j.jdate ) > + > (Select COALESCE(sum(creditamount-debitamount),0 ) > from journal where accnr = '5995300' > AND (module = 77 OR MODULE = 66 )AND source = j.source AND source2 = j.source2 AND jtype =1 AND jdate = j.jdate) > ) <> 0 > ) >Order by 1,3,4 Don't know how much quicker it will be, but with Fb 2.5 you could change your query to something like: with tmp(surname, name, trxdate, source, job_number, creditnr, sourceid, serviceadvisOR, credVAT, VAT,invoicetotalINC, credinc, creditnote, invoiceINCRedo, invoiceVATRedo, cost) as (select trim(c.name||' '||c.surname), trim(ca.name), j.jdate, j.source, i.job_number, j.source2, j.sourceid, s.name||' '||s.surname, sum(iif(j2.accnr = '5995300' and j2.module in (66, 77) and j2source2 = j2.source2 and j2.jtype = 1, coalesce(j2.creditamount, 0) - coalesce(j2.debitamount, 0), 0)), sum(iif(j2.accnr = '5995300' and j2.jtype = 1, coalesce(j2.creditamount, 0) - coalesce(j2.debitamount, 0), 0)), su
RE: [firebird-support] Firebird migration to increase perfomances
The common mistake many porogrammers make when coming from a DB like Paradox is to treat FB like Paradox. Especially when using a tool like Delphi. Been in that boat many years ago. To solve the problem, just apply one simple rule: No data goes into or comes out of FB without an explicit SQL command. Often it means a major redesign of your app (especially Delphi apps), but you just have to go that route. From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 22 June 2015 10:01 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Firebird migration to increase perfomances >> There can be lots of reasons for a database application being slow. If >> the database have huge tables, and the application is written thinking >> in terms of tables as opposed to datasets, then things will be slow > >Svein. > An interesting comment, to me anyway, regarding tables vs datasets. I'm >no SQL guru, I just use Firebird as the backend of an app I'm writing, >but would you mind elaborating on what you've said. Just some >"buzzwords"/key words I should use on a google search would be greatly >appreciated. Sure, Andrew, this is nothing complicated at all. Once upon a time I used Paradox, a desktop database that I consider quite OK for small tables and single users. However, tables with one hundred thousand rows or so made things slow down considerably. Using Firebird the Paradox way, i.e. loading all records and all fields every time is a bad idea except for tiny lookup tables. Firebird is good at finding the fields of the records you're interested in - including joining tables or doing some minor calculations upon the fields. Using Firebird simply as a place to store tables and doing all selection in code in your app rather than using a WHERE clause will make things slower - you may compare it to me prefering to take what I want to eat out of the fridge rather than take everything out of the fridge, find what I want to eat and put the rest back into the fridge. Similarly, I prefer to name the fields I need, rather than use SELECT * (there are exceptions, of course). Generally speaking, a result set should only contain the data that you require for whatever you're doing, and the smaller the result set is, the better. Using Firebird the client/server way with appropriate indexing, it doesn't normally matter all too much how big tables and databases are, although I must admit that I'm only used to working with tables with a few million rows, and have no experience with really big databases. Set [Non-text portions of this message have been removed]
[firebird-support] Is this a bug or common behaviour? (conversion error from string "")
Hello guys,
today I've stumbled on some strange (in my opinion) behaviour.
Firstly, create these two structures:
CREATE GLOBAL TEMPORARY TABLE GLB_CS_TEMP
(
ID_MON_OBJECT INTEGER NOT NULL,
ID_PARAM INTEGER NOT NULL,
MEASUREMENT_DATE TIMESTAMP,
PARAM_VALUE VARCHAR(64) DEFAULT '',
CONSTRAINT PK_GLB_CS_TEMP PRIMARY KEY (ID_MON_OBJECT,ID_PARAM)
);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON GLB_CS_TEMP TO SYSDBA WITH GRANT OPTION;
SET TERM ^ ;
CREATE PROCEDURE PROC_SPLIT_STRING (
P_STRING VARCHAR(32000),
P_SPLITTER CHAR(1) )
RETURNS (
PART VARCHAR(32000) )
AS
DECLARE VARIABLE LASTPOS INTEGER;
DECLARE VARIABLE NEXTPOS INTEGER;
BEGIN
P_STRING = :P_STRING || :P_SPLITTER;
LASTPOS = 1;
NEXTPOS = POSITION(:P_SPLITTER, :P_STRING, LASTPOS);
IF (LASTPOS = NEXTPOS) THEN
BEGIN
PART = SUBSTRING(:P_STRING FROM :LASTPOS FOR :NEXTPOS - :LASTPOS);
SUSPEND;
LASTPOS = :NEXTPOS + 1;
NEXTPOS = POSITION(:P_SPLITTER, :P_STRING, LASTPOS);
END
WHILE (:NEXTPOS > 1) do
BEGIN
PART = SUBSTRING(:P_STRING FROM :LASTPOS FOR :NEXTPOS - :LASTPOS);
LASTPOS = :NEXTPOS + 1;
NEXTPOS = POSITION(:P_SPLITTER, :P_STRING, LASTPOS);
SUSPEND;
END
END
^
SET TERM ; ^
GRANT EXECUTE
ON PROCEDURE PROC_SPLIT_STRING TO SYSDBA;
Then run this query:
SELECT A.MO, A.PA, G.PARAM_VALUE
FROM
(
SELECT 0 AS MO, CAST(PAR.PART AS INTEGER) AS PA
FROM PROC_SPLIT_STRING('', ',') PAR
WHERE PAR.PART <> ''
) A
,
GLB_CS_TEMP G
WHERE
A.MO=G.ID_MON_OBJECT AND A.PA = G.ID_PARAM
You should get an error: conversion error from string ""
Now change the WHERE condition and instead A.PA = G.ID_PARAM write A.PA =
COALESCE(G.ID_PARAM,0)
and now it works!?
I do not understand why it is not working at the first place, GLB_CS_TEMP is
empty, and the result from subquery A is also empty. Very strange, should I
report this as a bug or is there an explanation for this?
Firebird version 2.5.4.26856
Thank you.
