cancel
Showing results for 
Search instead for 
Did you mean: 

WebI SQL engine code generation for RANK in a UNX universe

former_member292809
Discoverer
0 Kudos

Community,

We are on Business Objects 4.2 SP4 and use UNX universes.

When using the RANK functionality in building a query, it will generate syntax similar to this, directly prior to the RANK function.

FieldA + '\' + FieldB (FieldA and FieldB are the ones being used in the RANK function). This code is fine and will work, but we are trying to get this to work on the Hadoop platform. I can switch the concatenation syntax to ||, not a problem, but the '\' is a character that cannot be used in a concatenation in Hadoop. Is there anywhere to change the character being used here? Note that this is not any syntax defined in the UNX, it seems its baked into the SQL generation when using RANK.

Thanks for any help.

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member292809
Discoverer
0 Kudos

This is a very small snippet of the code generated in a report. The syntax for RANK is correct, but the section highlighted in Green is added whenever RANK is used. It splits this out as a dimension, so it is not harmful, but also not necessary. That's not a big deal though.

The issue is that it is concatenating using '\' to split the two fields. This would work fine in anything except Hive/Hadoop, but against that environment, '\' is a reserved character that cannot be concatenated.

Note that I have tested this with oracle and ms sql and the behavior is the same. It is not problematic in those environments because '\' is not a reserved character in regards to concatenation.

former_member292809
Discoverer
0 Kudos

Ayman,

'\\' would work, but the issue is that I'm not finding anywhere to change that syntax with the unx PRM files or anywhere really. It seems like it might be baked into the SQL engine and not something that can be modified. The user can do custom SQL and edit that portion, but we generally do not enable that for end users.

Basically I was looking for either confirmation that this is part of the SQL engine and cannot be changed, or maybe pointed in the right direction where it can be changed.

ayman_salem
Active Contributor
0 Kudos

can you give us a screenshot for the generated SQL

ayman_salem
Active Contributor
0 Kudos

have you tried with '\\'