COGNOiSe.com - The IBM Cognos Community

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.

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.

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

In that case, use the Char_Length function too:

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

MF.

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.

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

MF

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

Great news!  ;D

Quote from: JaiCognos on 07 Jan 2010 01:31:30 AMHope i could also think like you one day.

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

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

Quote from: ReportNet Addict on 08 Jan 2010 02:16:29 PM
And he's grumpy...  Here's his picture http://truersourcesofsound.com/wp-content/uploads/2009/04/grumpy-old-men.jpg (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

Thanks a lot for sharing your picture.

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

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

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

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.