KIRIX SQL

[SLSJax]

Is there some difference in Strata's internal SQL interpretation? I'm not able to use CAST or CONVERT on Strata-local data. I've tried it through the console with a select command, and it won't accept it (i.e. Error: Unable to process SELECT statement).

Works fine-> SELECT str(trade+vol,2) as p4, "1" as p5 INTO test1 FROM test2
Doesn't work -> SELECT str(trade+vol,2) as p4, CAST("1" as numeric(5,2)) as p5 INTO test1 FROM test2
Doesn't work -> SELECT str(trade+vol,2) as p4, CAST("1" as decimal(5,2)) as p5 INTO test1 FROM test2
Doesn't work -> SELECT str(trade+vol,2) as p4, CONVERT(decimal(5,2),"1") as p5 INTO test1 FROM test2

Also, I can't get Strata to return the proper decimal places:

Trade & Vol are both fields with two decimal places.

SELECT trade+vol as p4 INTO test1 FROM test2

returns an integer with n decimals for p4


I get the feeling I'm going to need to do all my processing in SQL and then load the final results to Strata, but that defeats the purpose of what I am doing.

[Aaron]

Every RDMS supports its own version of SQL, and each version is slightly different from the others. So in general, when working with different databases, you won't have all the features you would like all the time. This same rule applies when working with local data.

When trying to solve a particular data processing problem, a good approach is to start with the end goal in mind, and then figure out a way to implement that using the particular features supported. Kirix Strata is used to solve many of these kinds of problems, so it's most likely possible to do what you'd like to do.

For this particular problem, it looks like you'd like to insert the results into a specific database structure. To do this, create a table structure with a SQL CREATE statement, then insert into that table structure using a SQL INSERT statement and you'll have the correct field types as well as decimal places.