I am working hard with upgrading Cognos 10.2.2 (CQM) to Cognos Analytics 12.0.1 (DQM) on Windows with an underlying SQL server.

There are lots of functions not supported.  I have a data item expression as:

if len([query.item])=0 then ([query.item1]) else ([query.item2])

Unfortunately, I have tried to use len / length / datalength (as shown as Vendor specific function for MSSQL).  All of them are returning the error below.  Please suggest.

XQE-PLN-0098 The vendor specific function "datalength" is not supported.=== Please see http://www.ibm.com/search?q=XQE-PLN-0098 for more information ===.

Somewhere something thinks it is not dealing with ms sql server.  That is one thing you should observe.  There is insufficient information to make a determination why.  It is possible that you have an overly elaborate set of queries in your report rather than making use of a properly designed model.

Using datalength works for me.

You might want to use the common functions character_length or char_length instead.


Also, you would need to be aware of nulls.   The expression might need to be rewritten. 

if len([query.item])is null then ([query.item1]) else ([query.item2])

if (datalength ( [query item 0]   ) is null)
then ([query item 1])
else ([query item 2])

Hope that helps.

Quote from: absolutemusik on 19 Mar 2024 04:51:43 AMI am working hard with upgrading Cognos 10.2.2 (CQM) to Cognos Analytics 12.0.1 (DQM) on Windows with an underlying SQL server.

There are lots of functions not supported.  I have a data item expression as:

if len([query.item])=0 then ([query.item1]) else ([query.item2])

Unfortunately, I have tried to use len / length / datalength (as shown as Vendor specific function for MSSQL).  All of them are returning the error below.  Please suggest.

XQE-PLN-0098 The vendor specific function "datalength" is not supported.=== Please see http://www.ibm.com/search?q=XQE-PLN-0098 for more information ===.

Hi

If len is not supported then len([query.item]) is not supported then it is 'len' that is it supported and hence no matter how you use the 'len' function, whether to compare it with 0 or null will not be supported.

Try using - If [queryitem] is null then [queryitem1] else [queryitem2]

By the way [query.item] seems to be unrealistic. Is it [queryitem] ? 

Thanks everyone.

For the null value, it is good to have something like:
 if([Conn].[Query].[Data Item 1]='ABC') is null then (1) Else (2)

However, it could NOT solve some problems alike:
if([Conn].[Query].[Data Item 1]='ABC') then (3) else (datalength([Conn].[Query].[Data Item 2]))

In the second example, the length is basically an output value.

It is still giving:
XQE-PLN-0098 The vendor specific function "datalength" is not supported.=== Please see http://www.ibm.com/search?q=XQE-PLN-0098 for more information ===.

Anyway, I have given up "datalength" or any MSSQL database function.  There are lots of things not working.  I have no idea why the MSSQL with CA12 DQM not likely working with NVL, LEN, DATALENGTH, etc.

I have changed some of the reports to those Cognos common functions, such as coalesce, char_length, etc.  The related report is running now.  To be honest, I was dealing with Cognos connecting to Oracle and DB2 mostly.  It is the 1st time to connect to MSSQL for upgrade.

At this point, I would like to have a complete list of mapping of those unsupported functions from IBM - still researching.  There are still hundreds of reports to be reviewed.

Quote from: absolutemusik on 19 Mar 2024 09:21:44 PMThanks everyone.

For the null value, it is good to have something like:
 if([Conn].[Query].[Data Item 1]='ABC') is null then (1) Else (2)

However, it could NOT solve some problems alike:
if([Conn].[Query].[Data Item 1]='ABC') then (3) else (datalength([Conn].[Query].[Data Item 2]))

In the second example, the length is basically an output value.

It is still giving:
XQE-PLN-0098 The vendor specific function "datalength" is not supported.=== Please see http://www.ibm.com/search?q=XQE-PLN-0098 for more information ===.

I thought your DB is SQL Server and for that 'if([Conn].[Query].[Data Item 1]='ABC')' is definitely not going to work. That's wrong syntax. I have SQLs embedded in reports and it works with correct syntax which I provided - If [queryitem] is null then [queryitem1] else [queryitem2].

Case
  When FieldName is null
  Then Field1
  Else Field2
End

will also work.

Keep in mind that Cognos will regenerate the SQL regardless of whether you specify it as 'Cognos' or 'Native' SQL. Moreover, I don't even know where are you putting these codes. In the SQL itself which is feeding the query, in the Data Item or somewhere else? Your syntax is absurd when it says -[Conn].[Query].[Data Item 1].
Neither does it look like inside a Data Item, nor referring to a package because doesn't look like a MUNs.

The problem is the way you are trying to use and understand it. I have tons of reports written using custom SQLs and all work using the null command I gave you. In your original post, you posted:

if len([query.item])=0 then ([query.item1]) else ([query.item2])

So if it has to check only the length being zero then that means it just checks whether there is any data in that field and that being the case, checking for null would be sufficient !