RE: [sqr-users] How to extract data in an SQR

[Bob Stone <bstone@fastenal.com>]

you can use an unstring.  The only problem you might have is whether or not
you can 'move' a negative from a string to a number.  If you cannot, then
you'll have to do some check for the first character, if its negative then
make it positive, cast it into a number, and then make it negative again.

Unstringing used for pattern matching is a relatively important part of SQR.
I recommend looking for a decent SQR book if you are having trouble.


let $data = 'IV*14625458**23.17*23.4*.23\'
unstring $data by '*'  into $a $b $c $amount_field $e $f
 !  now '23.27' or '-25735.33' is in $amount_field. make a #var.
move $amount_field to #amount_field

OR

let $data = 'IV*14625458**23.17*23.4*.23\'      
unstring $data by '*'  into $a $b $c $amount_field $e $f
 !  now '23.27' or '-25735.33' is in $amount_field. make a #var.

if substring(1,1,$amount_field) = '-'
        let $pos = substring (2, len($amount_field), $amount_field)
        move $pos into #pos
        let #amount_field = 0 - #pos
else
        move $amount_field to #amount_field
end-if



-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Kaz.Narayanan@averydennison.com
Sent: Thursday, December 02, 2004 3:37 PM
To: This list is for discussion about the SQR database reporting
language from Hyperion Solutions.
Subject: [sqr-users] How to extract data in an SQR



Hi,

I want to extract an amount field from a data set like this below:

IV*14625458**23.17*23.4*.23\
DL*13377031PRGCB*PO*-25735.33*-25735.33*0\

I want to only extract the amount of 23.17 in the first line and
-25735.33 in the 2nd line.

Only logical construct here is that these amounts always comes after the
3rd '* '
in the data set.

Could anyone help please?

Thanks
Regards
KM Narayanan




_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users