ApplySimple Syntax ?? Oracle Decode 2

[mstrgrl]

I'm trying to do a decode in Oracle where if a certain value is null we apply text to it in the report results. The decode statement works in SQL+; however, when I run MSTR it shows as missing a right parenthesis. MSTR won't let me add another parenthesis ??? Help -- what did I screw up?


ApplySimple( "DECODE((#0),'CS - Sponsorship/Donation',NULL
,DECODE((#1),NULL,'Subtype
Not Available,wotype2)"
,TYPE, WOTYPE2)

%-)

 

[entaroadun]

You need an extra parenthesis inside the right quote.

ApplySimple( "DECODE((#0),'CS - Sponsorship/Donation',NULL
,DECODE((#1),NULL,'Subtype
Not Available,wotype2))"
,TYPE, WOTYPE2)

 

[mstrgrl]

Thanks a million entaroadun!! I'm still pretty new at the Apply syntax!!

 

[mstrgrl]

The Decode syntax worked fine; however, has anyone ever seen an ApplySimple statement NOT return the same results as SQL+ ?? This one is...!

Thanks!

 

[entaroadun]

Have you checked the SQL in the report to make sure that the MSTR engine is generating the SQL correctly?

 

[mstrgrl]

I originally cut and pasted the syntax from SQL+ into the attribute statement and tweaked it. I just verified taking the SQL generated by MSTR back into SQL+ -- the SQL+ results work as they should; however, the report results are different. Unfortunately this is a "decode within a decode" and isn't a normal statement but it is the only way to get the user required results.

 

[entaroadun]

Please explain the logic behind the decodes in English. I can do CASE statements but not a DECODE to save my life (I cut my teeth on Oracle 8i). I'm sure we can write a CASE statement that consistently does what you need it to do.

 

[mstrgrl]

OK,,, here goes:

IF #0 = 'specific value' THEN NULL
ELSE IF #1 IS NULL THEN 'Not Available'
ELSE #1

#0 = TYPE
#1 = TYPE2

I was told by someone that the CASE statement only works with Teradata and not Oracle. Is that true?? Your help is GREATLY appreciated!!!

 

[entaroadun]

CASE statements are ANSI standard, and Oracle 8i+ support them. Prior to 8i, Oracle didn't support them.

CASE statements can't be nested either, but they can be used with multiple fields:

[tt]
CASE WHEN #0 = 'specific value' THEN NULL
WHEN #1 IS NULL THEN NULL
ELSE #1
END
[/tt]

The CASE statement stops at the first true clause, so the second WHEN clause wouldn't get executed unless the first one failed.

I normally wrap the whole statement with parentheses in my ApplySimple functions: ApplySimple("(CASE...END)".

Also, I'm sure you knew this already, but the CASE statement is a statement, not a function, so it doesn't require any parentheses within itself, unlike DECODE.

I've never used DECODEs, but if they can take multiple fields, then you can use the flattened logic above in a single, un-nested DECODE. That would work too.

 

[mstrNate]

There should be no difference in the output of what you execute in SQL Plus and what you get with an ApplySimple statement. In addition to the right parenthesis entaroadun found missing earlier, it looks like you're still missing something else. Try adding an apostrophe (') after the Subtype Not Available statement. Also, perhaps you need to specify the particular form of the attribute you're using, e.g. type@id, or type@desc. Maybe not, but just checking.

This is what I think you're doing in SQL Plus:
DECODE(type,'CS - Sponsorship/Donation',NULL
,DECODE(wotype2,NULL,'Subtype Not Available',wotype2))

Here's what you might change to in MicroStrategy:
ApplySimple("DECODE(#0,'CS - Sponsorship/Donation',NULL
,DECODE(#1,NULL,'Subtype Not Available',#1))"
,type@id,wotype2@id)

ApplySimple("DECODE(#0,'CS - Sponsorship/Donation',NULL
,DECODE(#1,NULL,'Subtype Not Available',#1))"
,type@desc,wotype2@desc)

 

[mstrgrl]

Error - Object (of type Attribute) not allowed in this place. it is definitely the ID and not DESC.

ApplySimple( "DECODE(#0,'CS - Sponsorship/Donation', NULL
,DECODE(#1,NULL,'Subtype Not Available' ,#1))", TYPE@ID, WOTYPE2@ID )

??

 

[entaroadun]

Quick question: where are you using this ApplySimple expression? What you're using it for determines what you need to supply as arguments.

 

[spod]

You can set up pre-report sql in MicroStrategy that may help out if there are any differences between the result set in SQL+ and MicroStrategy. I've had issues in the past with date formats between the two and added the alter session blah blah blah stuff in pre-report sql to get around this.

 

[mstrgrl]

This statement is an attribute form -- not the main ID. All of the values in the "type" and "wotype2" will be strictly text or nulls.

Thanks

 

[entaroadun]

The arguments should be column objects or attribute form objects. I assume that those names refer to columns in a table, so you need to supply column objects.

Could you post the entire SQL and describe the discrepancies between SQL+ and MSTR? Where in MSTR are you viewing report results?

Also, did you try the CASE statement to see if it gives you what you need?

 

[mstrgrl]

I had MSTR consulting onsite for some other issues last week. They confirmed the DECODE syntax is valid and should work; however, they could not figure out why the results were not the same as SQL+. The only thing they thought was maybe when MSTR applies report XML that somehow it is doing something else??? There is also an issue that the database records may not be entirely unique when there is a null value.

ApplySimple("DECODE(#0,'CS - Scorecard','TEXT1', 'TEXT2')", TYPE)

Results:

TYPE SUBTYPE DECODE

CS - Scorecard Advertising TEXT2
Catalog TEXT2
Finance TEXT2
Marketing TEXT2
"NULL" TEXT1

 

[entaroadun]

Can you modify the report VLDB settings to make MSTR put the results of the final SQL pass into a table so that you can peek at the contents? That way, you could tell which layer is introducing the error.