Filter Criteria 1

[jennilein]

I have a field with a product number that can vary from 10 to 19 digits (of which the 10th and 11th positions are spaces). I would like to query products with a 80 on the 14th and 15th position.

In access, I would specify under the criteria for that field: like "????????????80" - and it would work.

How can I use similar commands in Cognos? Or shall I say, can I?

Thank you in advance for your thoughts!
Jenni

 

[nagrajm]

Jenni

The substring function is very efficient

substring (string_exp, integer_exp1, integer_exp2)

Returns the substring of string_exp that starts at position integer_exp1 for integer_exp2 characters. The first character in string_exp is at position 1.

Example : POS_14_to_15 = sunstring(PROD_NO, 14, 2)

Hope that helps.

Nagraj

 

[jennilein]

Nagraj,

This sounds like it could work, however, I am still missing one element. How do I request only product numbers with an "80" on the 14th and 15th position.

Where does the "80" fit in?

Thanks again!
Jenni

 

[nagrajm]

Continuing from my earlier post, create a calculation called

POS_14_to_15 = substring(PROD_NO, 14, 2)

In the detail filter create a condition that says

POS_14_to_15 = "80"

In the alternative, without creating the calculation, you could simply say in the filter condition - substring(PROD_NO, 14, 2) = "80"

Does that help?

 

[jennilein]

Yes, that works!!

Thank you soooo much!

Jenni