IBM Support

DB2 select script (run inside TI process) does not show all expected values (missing blank/null value entries), caused by incorrect SQL syntax relating to DB2 script 'SELECT' (run in Turbo Integrator TI script)

Troubleshooting


Problem

Customer wants to read some information from the 'main' Controller database, via Planning Analytics (TM1).
  • For example, they want to read from the table XDB19
To achieve this, they read the data via a DB2 script, running inside TM1 Turbo Integrator (TI)
However, the results do not include all expected rows
  • Specifically, the script cannot find rows where the select answers are set to blank / empty / null

Symptom

Example:
Imagine a scenario where the customer runs a script similar to:
SELECT SUBSTR(perakt,1,4), bol, vkod, konto, belopp, motbol, dim2, btyp FROM XDB19
WHERE SUBSTR(perakt, 5,2) = 'AC'
AND SUBSTR(perakt,1,4) = '1907' AND ktypkonc <> 'LE'
AND ktypkonc <> 'AB'
AND (btyp = ''  OR btyp = 'BC' OR btyp = 'CD')

The idea of the script is that it wants to show all the values where:
  • Actuality = AC
  • Period = 1907
  • Group (relating to elimination) is *not* 'LE' or 'AB'
  • Journal type is either:
    • Blank / empty / null
    • or BC
    • or CD
If you run that script, then the TI gives a preview of the first 10 results. None of the rows have btype = ''

This is not what the customer is expecting. Specifically, inside the database table, there are lots of rows where the above is satisfied, where btype = ''.

Cause

Incorrect syntax for DB2 statement.
Specifically, when looking for a seemingly null/blank/empty value, in some situations you may need to use the value ' ' (with a space) not ''.

Resolving The Problem

Modify the script slightly so that the null value is searched for by:    ' '
Example:
In one real-life example, the solution was to use this script:
SELECT SUBSTR(perakt,1,4), bol, vkod, konto, belopp, motbol, dim2, btyp FROM XDB19
WHERE SUBSTR(perakt, 5,2) = 'AC'
AND SUBSTR(perakt,1,4) = '1907' AND ktypkonc <> 'LE'
AND ktypkonc <> 'AB'
AND (btyp = ' '  OR btyp = 'BC' OR btyp = 'CD')

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSMRTZ","label":"IBM Cognos Controller on Cloud"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.3.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSD29G","label":"IBM Planning Analytics"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
12 October 2021

UID

ibm10997710

Page Feedback