If/Then based on prompt character length

[Patrick77]

Hi,
I am trying to let the character length of a prompt dictate the path of an if/then statement:

Code Select Expand

If ( character_length(?prompt('MyPar', 'string')?) > 1)
THEN
( Do this )
ELSE
( Do that )


But it doesn't work and just returns everything for this optional prompt.

Any ideas how to get this to work?

[BigChris]

Could you try something like:

Code Select Expand

if(?MyPar? is missing)
then (do this)
else (do that)

[Patrick77]

Thanks Chris,
I'll keep that in my pocket, but in this instance I am wanting to do one thing if the prompt is a single character and something else if it is longer.

[BigChris]

What you've got looks rather like a prompt macro...Also, you don't say whether you're using SQL server as the backend.

I've just tried this and it worked the way you wanted...

Code Select Expand

if(character_length (?pTest?)>1)then ('Yes') else ('No')

[Lynn]

Adding on to what BigChris has provided, can we assume your package is relational? Is this expression going to populate a value for a data item or will it be used as the expression in a detail filter? Your first post mentioned something about returning all records so I assume the latter.

When filtering I generally recommend a Boolean type of expression rather than case statements or if/then/else logic:

Code Select Expand


(
  length ( ?YourParameter? ) = 1
  and
  [Your Query Item] = 'Do This'
)
or
(
  length ( ?YourParameter? ) = 2
  and
  [Your Query Item] = 'Do That'
)


I think length is a Cognos function and will generate SQL using whatever the corresponding function is for your database (such as character_length, strlen, etc.). Not 100% certain on this last point but easy enough to try it out.

[AnalyticsWithJay]

I think length is a Cognos function and will generate SQL using whatever the corresponding function is for your database (such as character_length, strlen, etc.). Not 100% certain on this last point but easy enough to try it out.

I think you accidentally flipped them around :) char_length is a Cognos function, and you are correct in that the generated SQL uses the database function. For Oracle, it'll use length().

OP, it's generally a good idea to use Cognos functions instead of DB functions in case you change database vendors in the future.

[Lynn]

Thanks Jay!

[Patrick77]

Thank you all for your time and help. 

I was able to get this to work with the boolean-type expression that Lynn suggested, however still not with the if/then/else form.

I apologize for not providing necessary information. I am somewhat new to using Cognos and am self taught. I am using Cognos 10.2 Report Studio and the package I am using is relational. I generally prefer to write my queries (including filters and prompts) in SQL, but I have resorted to using the Cognos GUI query filter so that the prompts can be literally optional (i.e. no default value, they are ignored if blank).  I am still getting used to how to actually use the prompts when using them in SQL vs. in the query filter (I hope you understand what I mean by GUI filter).

In summary, the following worked and I need to rethink why I thought I needed to wrap it with prompt or use single quotes.   

Code Select Expand

( character_length(?MyPar?) > 1
AND
DO THIS )
OR
( character_length(?MyPar?) = 1
AND
DO THAT  )

The fact that this doesn't work still seems odd:

Code Select Expand

If ( character_length(?MyPar?) > 1)
Then
( Do This )
Else
( Do That )


[AnalyticsWithJay]

The fact that this doesn't work still seems odd:

Code Select Expand

If ( character_length(?MyPar?) > 1)
Then
( Do This )
Else
( Do That )


It would seem logical to do this, but if you think of the SQL it does not make sense. I'll explain this at the end of the post.

The correct syntax is like this:

Code Select Expand


(
CASE
WHEN [Country] = 'United States'
THEN 'US'
ELSE [Country]
END
)
= 'US'


You could also have Case or If statements on both sides of the equal sign.

As for why the syntax doesn't make sense, imagine the WHERE clause in the SQL statement as such:

Code Select Expand


WHERE
    CASE WHEN country = 'united states'
             THEN 'US' = 'US'
    ELSE country = 'US'
    END
... As you can see, this is malformed SQL, and that's why we have to structure the filter similar to the example I provided.

[Patrick77]

Thanks Jay,
I wasn't thinking about the filter in terms of how it would be inserted into the actual SQL 'select', 'from', 'where' structure.
Not sure why.  Thanks again to all who took the time to chime in.