Substr in a View on SQLite

Hi,
I am giving another shot at Metabase (narly a year later). I have created a small SQLite database with 2 tables Customers and Invoices.
As I understand that Metadata does not really handle joins and to prepare data, I’ve created a view that joins the table Customers and Invoices to get the Invoiced amount, and country, city and postcode.
It all works fine but as soon as I want to add a computed column [substr(PostCode, 1, 2)] to get the State, Metadata fails to get the value from the view. At the best I don’t get the new column (synching the database) at the worst, it fails retrieving data (dropping and recreating the database in Metabase).

Is there an easy way to fix that (without creating another table an “transferring” data from the view to the table) ?

Hi @ComputingFroggy

Which version of Metabase?

If you have created a view, then Metabase only sees the returned data - not the query underneath.
So in Metabase you’re running SELECT * FROM myview?
What errors are you getting? When syncing or trying to run the query.

Metabase can do almost all of it directly in the Query Browser. It’s only the substr that would require SQL or a view.
But it depends on how your structure is linked - Admin > Data Model > (database) > (table) > (column) :gear:

This is an example with chinook sample database:

Hi,
thanks for taking some time to look into my problem.
Here is the log when performing a sync : https://framabin.org/p/?481422f6b2449ddd#4+PE6lkyf57ubkpQ9aXWIPVBN9v+xoHEUN/R5pXAFRw=

Eventually, by moving the columns around in the View, and by adding another alias (the_column AS alias_name), it works. So it’s ok for me, but there’s something wrong somewhere.

@ComputingFroggy
Without seeing the view query, it’s difficult to know, so what column type is for instance PDat and what is the Field Type of the same column?
So before moving around with the view, what happened when you ran SELECT * FROM myview via Native Query?

Before I had the error message and the SUSTR column was not displayed !

Here is the view working now :
CREATE VIEW Factures as SELECT PNum, substr(Ccp,1,2) as Dept, Cpay as Pays, Pn1, PCt, Pcti, Ccp, Cvil, Csoc FROM P JOIN C ON Pct = Ccod

Pdat is a date. At least it should be a date, it is displayed as a date, but in the table structure it is listed as an integer ! ? !

So if you just run the query directly in Native Query, you can test if it works:

SELECT PNum, substr(Ccp,1,2) as Dept, Cpay as Pays, Pn1, PCt, Pcti, Ccp, Cvil, Csoc FROM P JOIN C ON Pct = Ccod

I’m not sure what you mean. What is the SQLite column type of Pdat? And what is the Field Type in Metabase?