OK, custom fields are a potentially very powerful instrument.
In Example: When I have four fields in a database named “revenues”:
“id”, “date”, “revenue” and “account_number”
and I want to increase all the revenues that’s are in the database with a timestamp before October, 31st by 20% and I want put them in a custom field with the name “new_revenue”, how should the SQL look like?
It doesn’t make sense to write a select-statement because the SELECT already happened. But as far as I know “revenue * 1.2” is not a full SQL-statement. How should the SQl-statement look like?
Any help is very welcome.
P.S. There are some more questions, I don’t expect to be answered by the forum members:
1) Why is the CASE clause not recognized by the SQL-editor (at least its not getting blue) but therefore an IF-statement (that’s specific for MySQL – as far as I know)?
2) In case Quantrix support all the specific functions within the various databases, why is the word “datanav” not even mentioned in the Help-Fuction?
Hi Ody – it appears that the custom columns in DataNAV work for operands on existing columns, but there is no way to insert a WHERE statement to specify the query in more detail. I think you will probably have to do this type of column data manipulation at the database level.
Your idea certainly has merit and we can consider for future releases in Quantrix.
Hi Mike, thanks for your answer. I already had the documentation. My question was a bit different from the example in documentation: Let me refer to the screenshot you’ve attached to your answer. There you show how to setup a custom field (new_age) where the original age is increased by 1.
1) How should this formula look if I want to increase only the age of those people where the salutation is “Mr.”?
2) Is a combined statement possible where I increase the age of those who have a “Mr.” in their salutation and I furthermore decrease the age of all “Mrs.” by 5?
You should be able to enter the command by just calling the column names and the Quantrix DataNAV will do the rest. See my simple example in the attached image.
Each database is different so it is hard to put a SQL editor up that is compatible for all. For example, H2 did not like this simple query, while SQL Server processed it fine.
DataNAV is not yet incorporated into the main documentation. There is a separate addendum that can be downloaded here.