Is there a way to apply a Query Calculation Column in Cognos that determines 'real' (Built) from 'interim' (Not Built) regarding VINs

Question

After downloading the intended report out of Cognos, in Excel I use the following formula as a column to inform if the VIN is built or unbuilt.

The formula I use in excel:

When I inputted this as a Query Calculation Column in Cognos, I am receiving QE-DEF-0260 Parsing error before or near position, QE-DEF-0261 qfwp - parsing text, qe-def-0459 CCLException errors.

How do I write this formula/equation using Cognos 'language'?

Answer 1

If you are using SQL Server you can use the ISNUMERIC function.

if (isnumeric(substring ([VIN], 14, 15)) = 1)
then ('Real VIN')
else ('Interim')

or

case
    when isnumeric(substring ([VIN], 14, 15)) = 1
        then 'Real VIN'
    else 'Interim'
end

Using Oracle, MySQL, etc. would require that you use a different function.

If your data source is an uploaded Excel file or similar, you may need to create more complicated logic.