SQL query based on a Model

[meep]

Hello all

Models, and native Tables, can serve as the source for GUI queries.
However, if you convert a GUI Query based on a Model to an SQL query, then the Table name in the SQL is "source" eg source.fieldname
But it does not evaluate. So it appears that you cannot base an SQL query on a Model, even though it offers the convert option. Is this correct?

[Luiggi]

you can do

select * from {{#<question_id>}}

the model is a question in the end, so it has an ID in the same way you base a sql question from another question

[meep]

Thanks @Luiggi

Even though the autocomplete activates and I can select a saved Query/Model, once I've selected the Model, the SQL does not evaluate.

SELECT * FROM {{#78-m-hp-collections-month-all-origin}}

Could it be because the Model uses both Segment and Metric definitions?

[Luiggi]

does query #78 run correctly?

[meep]

Hello @Luiggi

Yes, no problem with the Model as is, it executes without error. But an SQL query based on the Model does not execute. It seems that the SQL editor adds a line, since the error has to do with line 2, which does not exist. Irrespective of whether I end the line in a ";" or not, the same error occurs. I tried a different Model and the same behaviour ensues. Even when I add a WHERE clause, the context menu recognises the Model and provides the correct field names in the selection, eg "Book Name".

But when I attempt to run the query I get an error, on line 2, but no such line exists ?

SELECT * FROM {{#86-m-hp-matters-all}} WHERE col_PK = 1;

[meep]

Okay, this works:

SELECT t1.col_PK FROM {{#86-m-hp-matters-all}} AS t1;

and so does

SELECT t1.* FROM {{#86-m-hp-matters-all}} AS t1;

Note that for MariaDB I have to use back-tick characters to quote the various names, which got lost in the above blockquotes:

SELECT `t1`.`Book Name` FROM {{#86-m-hp-matters-all}} AS t1 WHERE col_PK = 1;

Interestingly, I do not need to reference t1 (or m below) anywhere as long as I have an AS clause

SELECT `Book Name` FROM {{#86-m-hp-matters-all}} AS m WHERE col_PK = 1;

or

SELECT * FROM {{#86-m-hp-matters-all}} AS m