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

[Dattatraya Shetty <datta_shetty@yahoo.com>]

Bob /Kaz 

    See the Solution below and let me know what you guys think. 

begin-program
    let $data1 = 'IV*14625458*23.17*23.4*.23\'
    let $data2 = 'DL*13377031PRGCB*PO*-25735.33*-25735.33*0\'

    unstring $data1 by '*' into $f1 $f2 $f3 $f4

    let #num1 = to_number($f3)

    show 'Num1 = |' $f3 '|' #num1 '|'


    unstring $data2 by '*' into $f1 $f2 $f3 $f4 $f5

    let #num2 = to_number($f4)

    show 'Num2 = |' $f4 '|' #num2 '|'
end-program

Kaz. You can replace the Play with $f1 and decide if you what $f3 or
$f4 to make the Routine Generic. Also I am not sure if you have 2 **
for a Reason or its a Typo on Line with IV. 

If it is not Typo then the solution is even more Simple as on both line
$f4 will have data and all you do is 
   
    let #num = to_number($f4) 
HTH 
Datta. 

--- Bob Stone <bstone@fastenal.com> wrote:

> 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
> 



                
__________________________________ 
Do you Yahoo!? 
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250

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