Substring function in Cognos Report Studio 8.4

[JaiCognos]

Hi All,

I have problem with substr function in Report Studio Tool. My Requirement is below

I need to extract the last three digits from a number string

for Eg: ORBER_NUMBER=89909

I want to check the records whose order number ends with 909 and this values will be passed by user PROMPT.

I first applied cast function to convert NUMBER into VARCHAR and then applied SUBSTR(ORDER_NUMBER,-3,3) then cognos doest not read the last three strings it returns empty.

In Oracle i know this will work at any cost but in Cognos its not working can any one help me or suggest any alternative solution.

Thanks in Advance.

[MFGF]

You're almost there.

Your substr function at the moment specifies the to start extracting at character position -3.

Try SUBSTR([ORDER_NUMBER],3,3)  - extract three characters starting with the character at position 3.

MF.

[JaiCognos]

Hi,

Thanks for your response.

I forgot to mentioned the number lenght varies over here so substr(order_number,3,3) do not work.

Can you suggest any other workaround for this issue.


Thanks in Advance

[MFGF]

In that case, use the Char_Length function too:

SUBSTR([ORDER_NUMBER],CHAR_LENGTH([ORDER_NUMBER])-2,3)

MF.

[JaiCognos]

Hi MF,

Thanks a lot for your good logic.

I applied the logic but the cognos return null column.I am investigating the problem and let you know.



Thanks a lot once again.

[MFGF]

If you just use the Char_Length function on the ORDER_NUMBER, what results do you get?  Is the length being returned correctly?

MF

[JaiCognos]

Hi MF,

Thanks a Lot Cognos is returning the values perfectly.


Thanks Once Again. Its was Data base problem yesterday where it was returning the values null.

So you logic worked. Hope i could also think like you one day.


Thanks
Jai

[MFGF]

Great news!  ;D

Hope i could also think like you one day.

One day you too may become a grumpy old man like me! :)

[sir_jeroen]

And he's grumpy...  Here's his picture http://truersourcesofsound.com/wp-content/uploads/2009/04/grumpy-old-men.jpg MFGF is the one on the right... :D

[MFGF]

And he's grumpy...  Here's his picture http://truersourcesofsound.com/wp-content/uploads/2009/04/grumpy-old-men.jpg MFGF is the one on the right... :D

Lol!  Yep - that's me and my best mate ReportNet Addict! :D

[JaiCognos]

Thanks a lot for sharing your picture.

[MFGF]

It's not really me - ReportNet Addict was just having a joke :)

I'm far older than the chap in the picture!  ;D

[Priyanka_Jharaaik]

Hello,

I am new on Cognoise and I need your help regarding substr,cast and _add_months() function.
I have a string which is like " 6 days or 14 days or 60 days". I need to extract the number from this string and I am using a substr function for that and it works fine.
Now I have to add these days to a particular date for which I tried using _add_days(date,days) and _add_days() requires the second argument to be an integer.

I tried to use cast and convert the string to integer but it is not working.

Can you please help me with this.

PS

[BigChris]

An old school method that might work is to multiply days by 1. There's a chance that that would have the effect of turning it into an integer.