TM1 Forum

Hi all,

I am wondering why this simple SQL syntax against an Oracle database works just fine in some other 3rd party tools, but it doesn't work in TM1?

SELECT
DECODE(SUBSTR(MODULE_DENSITY,1,1),'?' ,' ' , MODULE_DENSITY) as "Module Density"
FROM
PL_SHRD.D_ENTERPRISE_PRODUCT

The syntax is being used on the Data Source tab in the Query section. The Data Source is added and I know my credentials are okay. I have connected via other TI processes to the same database without issue. It has something to do with that particular line (DECODE....)

But again, no issues with that EXACT syntax in other tools, but receive "SQL statement failed" error in TM1 - sigh.

Any ideas? Thanks a lot!

Just a shot in the dark, but what if you would change the question mark in your query to something else just for testing purpose? I am asking, as if you write SQL in Data Source of TI, you can use TI variables in this SQL and the way to put them there is to use apostrophes and question marks, e.g. So I am guessing this question mark could confuse TI.

If this is the case, you will need to come up with some other way to replace questionmark starting densities with space, but at least this will mean DECODE itself is not a problem.

Thanks jstrygner!

Hmmm. I tried that... replaced the " ? " with a " G " b/c I knew my data has a " G " in it... thus, my statement:

SELECT
DECODE(SUBSTR(MODULE_DENSITY,1,1),'G' ,' ' , MODULE_DENSITY) as "Module Density"
FROM
PL_SHRD.D_ENTERPRISE_PRODUCT

Thus, what's in Red was the ONLY change... and boom, it worked.

The problem is... all my columns that I am pulling in (around 30) all have a " ? " in them in certain spots due to them not having data. Instead of leaving them blank, the DBA put a " ? " and now that is causing me fits

Because of those " ? " I get an error when I run my process. Thus, was trying to get rid of them in my SQL syntax.

I'm finding TM1 seems quite quirky and picky

Now, I am at a loss... it appears it's not DECODE... it's the " ? " i am TRYING to replace in the first place.

I have no way to test, but this link:
http://www.techonthenet.com/ascii/chart.php
suggests such a code might work:

SELECT
DECODE(SUBSTR(MODULE_DENSITY,1,1), CHR(63) ,' ' , MODULE_DENSITY) as "Module Density"
FROM
PL_SHRD.D_ENTERPRISE_PRODUCT

If 63 is not the right code, you may try to find out the correct one. Good luck!

Edit: Changed "CHAR" (variable type) to "CHR" (seems to be the right function, but am still not sure as am not able to test.

Brilliant jstrygner!

That did it!!!!

I changed all 40 lines of my SQL syntax to say CHR(63) versus '?'

And I just got the wonderful pop-up after running my process that says "Completed Successfully!"

Thanks a lot for your help! I am glad this one was an easy one and all worked out well! Thanks again.

I am glad it solved the problem, thanks for feedback.

One thing I did not mention and definitely should, you always have an option to make a pure select on the source and then process the decoding or whatever else you need within TI on Metadata or Data tabs.

jimicron wrote:I'm finding TM1 seems quite quirky and picky

In this particular case I would say TM1 was more consistent and coherent rather than quirky and picky, as I think it is nice feature, this '?variable?' functionality.

TM1 has its pains, but lots of people still like it despite that for many different advantages.
Even if someone complains it is more like "TM1 is fine and great, it is just Iboglix that does not allow it to be ideal".