My first problem:
On 23/03/2014 16:21, Lyle wrote:
psql:/tmp/ledgersmb/1.2-1.3-upgrade_.sql:28: ERROR: null value in
column "heading" violates not-null constraint
DETAIL: Failing row contains (2, 0010, Freehold Property, A, , null,
f, f).
CONTEXT: SQL statement "INSERT INTO account (accno, description,
category, gifi_accno,
heading, contra, tax)
VALUES (in_accno, in_description, in_category, in_gifi_accno,
t_heading_id, in_contra, in_tax)"
PL/pgSQL function
account_save(integer,text,text,character,text,integer,boolean,boolean,text[])
line 48 at SQL statement
The actual code for this is:
SELECT account_save(id, accno, description, category, gifi_accno, NULL,
contra,
CASE WHEN link like '%tax%' THEN true ELSE false END,
string_to_array(link,':'))
FROM lsmb12.chart
WHERE charttype = 'A';
At first it looked very odd as you can see NULL being passed explicitly
for the heading column. On closer inspection of the table definition in
Pg-database.sql:43 I can see it's a foreign key field. Looking at the
account_save function definition in Accounts.sql:74 I can see it checks
for NULL and swaps it for an ID from the account_headings table. So my
problem is actually the SQL above:
INSERT INTO account_heading(id, accno, description)
SELECT id, accno, description
FROM lsmb12.chart WHERE charttype = 'H';
That isn't actually inserting anything:
# SELECT id, accno, description
# FROM lsmb12.chart WHERE charttype = 'H';
id | accno | description
----+-------+-------------
(0 rows)
# SELECT DISTINCT charttype FROM lsmb12.chart;
charttype
-----------
A
(1 row)
I don't have any H charttypes, only A. What should I do here?
Lyle
------------------------------------------------------------------------------
Learn Graph Databases - Download FREE O'Reilly Book
"Graph Databases" is the definitive new guide to graph databases and their
applications. Written by three acclaimed leaders in the field,
this first edition is now available. Download your free book today!
http://p.sf.net/sfu/13534_NeoTech
_______________________________________________
Ledger-smb-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
