Pentaho

Just a couple of notes depending of versions, dialects etc. If I was to reference a column which is the same as a reserved keyword (name), I would encapsulate it like this: `name`. Also if I were to take an equal test like you have in with the "where", I'd encapsulate it in single or double quotes like this: '${name}'.

Could you share the error message?

looks like it thinks, that '${serial}' is the required value. So, it eliminates the call function of $ and treats it just like a simple text. 

This is a different case? Are you not supposed to finish the sql query with a semi-colon? This query would ONLY return rows IF the variable "serial" has the value "Serial Number".

In PDI i guess you can ignore the semi-colon, but anyway  '${variable}' doesn't work, as it treats everything inside '...' as a simple text.

If i run the query, i just have no rows provided.

Please try this

SELECT * FROM ${asset} WHERE `Serial Number` = '${serial}'

It seems it is causing a problem because you have space in your column name.

I tried in MySQL, it works.

~Ravik

So, everything works. Thanks all

You should make yourself some improved practices when working with SQL.

1) Never use space in names (column names, table names, database names...)

And when you ask your question here, you quickly jumped from one example to another. I suggest sticking to one example at the time. In your second example you declare a string within double quotes ("Serial Number"), was this supposed to be a column name? If that's the case, I suggest joining a more general SQL forum and work on your SQL skills, because column names are not declared using double quotes.

Lastly, I suggest fiddling around in some sql client (for example mysql workbench) to work on your queries and make sure they work before moving onto Spoon.

Thanks for you comments. Actually I knew the answer from the begining, but PDI didn't work correctly with my request. (don't know why, but I've mentioned this working with kettle a little bit)

That's why I was confused and everyone in this chat as well. After the restart of kettle it provided me with the correct result. 

P.S.

"column names are not declared using double quotes" - in PDI it works in Table Input at least and you don't need a semi-colon on the end of the query.

Good luck

Ok thanks, you are right it doesn't matter with the semi-colon. I guess it doesn't matter to explicitly declare the end when it's only a single query.

But the double quotes sounds very strange to me. Take this example:

SELECT * FROM TEST_TABLE WHERE "ID" = 1;

This query is returning all rows where the String "ID" is equal to the value 1 (contradiction, always false). The result will always be empty (no rows).

Some times I use this kind of logic comparing a string to a variable to simplify logic on job level, effectively pushing program logic to be solved on the database.

In SQL-92 standard, column names can be put in double-quotes. In Postgres I tested this and the column with name id can be selected as "id"=17: