On 26/08/10 13:38, Simon Slavin wrote:
>
> On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
>
>> This time it reported that the view it would have created failed because
>> the table (view) already existed.
>
> I'm sorry to ask this, but can you check for us whether a VIEW by that name
> really does exist ? Don't forget, VIEWs get saved in the file, they're not
> part of the attachment.
Yes it does - this VIEW outputs the transactions in the default currency
(rather than the currency of the transaction) and the whole objective of
my this particular php script is to update this view when the default
currency changes.
a...@kanga:~/dev/money/db[master]$ sqlite3 money.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema dfxaction
CREATE VIEW dfxaction AS
SELECT t.id,t.date,t.version, src, srccode, dst,
dstcode,t.description, rno, repeat,
CASE
WHEN t.currency = 'GBP' THEN t.amount
WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN
t.srcamount
WHEN t.dstamount IS NOT NULL AND da .currency = 'GBP' THEN
t.dstamount
ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS
INTEGER)
END AS dfamount
FROM
xaction AS t
LEFT JOIN account AS sa ON t.src = sa.name
LEFT JOIN account AS da ON t.dst = da.name
LEFT JOIN currency ON
t.currency != 'GBP' AND
(t.srcamount IS NULL OR sa.currency != 'GBP') AND
(t.dstamount IS NULL OR da.currency != 'GBP') AND
t.currency = currency.name;
sqlite>
--
Alan Chandler
http://www.chandlerfamily.org.uk
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
