Virtual column in sql view

[sume...@kanzlei-wmv.de]

When I needed to add another field to the invoice window I quickly was able to do that following this very instructive guide:

http://wiki.idempiere.org/en/Adding_a_Field_to_an_existing_Window

I had no problem adding the column to the c_invoice table and to add a referring field to the customer invoice window.

I now have to include the field also within the invoice printout and here I am really lost.

I rather would not change the original sql view that the invoice print format is based on because that will get me in trouble once I am going to update idempiere to a newer version.

I also can not use a modified copy of the sql view because there is no way to combine that with the existing print format. At least I could not find one. If there is a way I will be very happy to learn about it.

So I tried to use a virtual column. I have already had some success adding a virtual column to the c_invoice_header_v sql view but that one was just a calculated field that only drew its data from the view.

Now I have to access another table and that does not seem to work at all.

The method given in this guide works just fine to extend a table with a virtual column.

http://www.adempiere.com/Virtual_Columns

However I failed miserably when I tried to use it on the c_invoice_header_v sql view.

Here is m column sql together with the error message I am getting.

(mycolumn is the column that I have successfully added to the c_invoice table in the first step)

Column SQL:

(SELECT mycolumn FROM c_invoice i, c_invoice_header_v h WHERE i.c_invoice_id=h.c_invoice_id)

or

(SELECT mycolumn FROM c_invoice i WHERE i.c_invoice_id=c_invoice_id)

The Error message:

org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression

Does anybody have a clue why the column SQL is not working? Is there a problem with using it on a sql view or is my sql query just buggy?  I also have no real Idea how to debug my query. Since it depends on the state of the application (the invoice_id + my column sql) I also can not reproduce it in an external db management tool.

Is there a way to have a look the query that is finally built by the application?

Thanks for any hints and comments!

[sume...@kanzlei-wmv.de]

I have now modified the original sql view t mke it work.

I needed to get this off the table so I went the short and dirty route.

Still if anybody knows or even has an idea how a cleaner solution could be implemented using a virtual column I will be happy to use it.

When I printed the invoice based on the modified c_invoice_header_v view I got a strange error:

org.postgresql.util.PSQLException: ERROR: column c_invoice_header_vt.mycolumn does not  exist

The error went away after I added mycolumn also to the  c_invoice_header_vt sql view.

Can anybody shed some light on the purpose of the c_some_stuff_vt  views?

There are a lot of them in the database yet they are not referenced in the application dictionary.

It appears they are used by the application and need to be somehow kept in sync with the corresponding
c_some_stuff_v  sql views. Is there any documentation on the topic?

Thanks for any hints and comments.

Andreas Sumerauer

[Nicolas Micoud]

Hello,

..._vt views are the "translated" version of the ..._v views
And yes, they need to be synchronized

Regards,

Nicolas