RE: [sqr-users] Trailing spaces in extract file

["Peter Burton" <peter.burton@oracle.com>]

Arun,

You are incorrect ... SQR will convert numeric and date column variables to 
their string format (using the default rules) when referenced in a WRITE 
statement.  Look at my example, I am referencing "cust_num" which is defined as 
a numeric field (an int) and it worked without errors.

Peter

-----Original Message-----
From: sqr-users-bounces+peter.burton=oracle.com@sqrug.org 
[mailto:sqr-users-bounces+peter.burton=oracle.com@sqrug.org] On Behalf Of Arun 
Kumar
Sent: Monday, August 13, 2007 9:14 AM
To: This list is for discussion about the SQR database reporting language from 
Hyperion Solutions.
Subject: Re: [sqr-users] Trailing spaces in extract file

Remember you can only use character variable in write statements.

If sqr interprets the & variable as numeric then write statement will give 
errors.

Also I didnt notice any move statement or any Let statement for your $ 
variables.. This explains why your out put file was empty.


----- Original Message ----
From: Peter Burton <peter.burton@oracle.com>
To: This list is for discussion about the SQR database reporting language from 
Hyperion Solutions. <sqr-users@sqrug.org>
Sent: Monday, August 13, 2007 5:09:40 AM
Subject: RE: [sqr-users] Trailing spaces in extract file

Maddy,

Seems to work for me ..

  q.sqr
  =====
Begin-Report
 Open 'q.txt' As 1 For-Writing Record=80:vary
Begin-Select
cust_num
name
 Write 1 From &cust_num:10 &name:45
 from customers
 order by 1
End-Select
 Close 1
End-Report

   q.txt 
   =====
100001    Joe Smith and Company
100002    Corks and Bottles, Inc.
100003    Harry's Landmark Diner
100004    Jerry's Junkyard Specialties
100005    Kate's Out of Date Dress Shop
100006    Sam Johnson
100007    Harriet Bailey
100008    Clair Butterfield
100009    Quentin Fields
100010    Eliot Richards
100011    Isaiah J Schwartz and Company
100012    Harold Alexander Fink
100013    Gregory Stonehaven
100014    Alfred E Newman & Company
100015    Hammerhead Hardware
100016    Lights R Us
100017    Office Building Contractors 

Peter

-----Original Message-----
From: sqr-users-bounces+peter.burton=oracle.com@sqrug.org 
[mailto:sqr-users-bounces+peter.burton=oracle.com@sqrug.org] On Behalf Of maddy
Sent: Saturday, August 11, 2007 6:53 PM
To: This list is for discussion about the SQR database reporting language from 
Hyperion Solutions.
Subject: Re: [sqr-users] Trailing spaces in extract file

If I use the "&" sign in the 'write' statement it gives an error
message 'variable
not declared'.To resolve this I have used "$" sign to declare the text
variables and used in the 'write' statement; SQR runs successfully but does
not write anything to the file.

*********************** Code  *********************
Begin-Setup

ask dbnames_file

#include '{dbnames_files}'

Declare-Variable

Text     $MEME_SSN

Text     $MEME_FIRST_NAME

Text     $MEME_LAST_NAME

End-Declare

end-setup

Begin-Report

open 'DBresults.txt' as 1 For-Writing Record=80:Vary

Begin-sql On-Error=Db_Error(301)

select

MEME_SSN  (,1)

MEME_FIRST_NAME  (,25)

MEME_LAST_NAME    (,50)

position (+1)

write 1 from $MEME_SSN:10 $MEME_FIRST_NAME:40 $MEME_LAST_NAME:30

FROM  CMC_MEME_MEMBER_test

WHERE

MEME_LAST_NAME = 'DUKEE'

AND

MEME_FIRST_NAME = 'BETTYY'

End-Sql

POSITION (+2)

Close 1

End-Report

Please let me know whether this is the right way to use write to print
trailing spaces.
Thanks!
Maddy



On 8/9/07, Arun Kumar <arunkumar13@yahoo.com> wrote:
>
> Because you use Print command to create the extract file.
>
> you could try creating a variable with 25 space and printing at the first
> line.
>
> As rule a thumb I always using write command to create all my extracts
> where its easy to handle such issues.
>
> ----- Original Message ----
> From: Peter Burton <peter.burton@oracle.com>
> To: This list is for discussion about the SQR database reporting language
> from Hyperion Solutions. <sqr-users@sqrug.org>
> Sent: Thursday, August 9, 2007 6:30:55 AM
> Subject: RE: [sqr-users] Trailing spaces in extract file
>
> Maddy,
>
> If you are using "PRINT" statements to create the export file ...
>
> Begin-Report
> Print 'Hello' (1,1)
> Print ' ' (1,26)
> Print 'Goobye (2,1)
> End-Report
>
> If you run this as "SQR {pgm} / -xl" all the lines will be trimmed,
> howvere if you run this as "SQR {pgm} / -xl -xtb" the first line will have
> trailing blanks.  Please note that the -XTB flag only applies to the default
> line printer output (i.e. -PRINTER:LP).
>
>
> If are creating the export file via the File-IO commands ...
>
> Begin-Report
> Open 'q.dat' As 1 For-Writing Record=32767:Vary
> Let $String = RPad('Last-Line', 25, ' ')
> Write 1 From $String
> Close 1
> End-Report
>
> You can also force a field to be a specific width and trailing blanks will
> be added
>
> Begin-Report
> Open 'q.dat' As 1 For-Writing Record=32767:Vary
> Let $String = 'Final-Line'
> Write 1 From $String:25        ! Pad/Truncate to 25 characters
> Close 1
> End-Report
>
> Either method will create the file "q.dat" and the line will have trailing
> blanks.
>
> Peter
>
> -----Original Message-----
> From: sqr-users-bounces+peter.burton=oracle.com@sqrug.org [mailto:
> sqr-users-bounces+peter.burton=oracle.com@sqrug.org] On Behalf Of maddy
> Sent: Wednesday, August 08, 2007 4:22 PM
> To: This list is for discussion about the SQR database reporting language
> from Hyperion Solutions.
> Subject: [sqr-users] Trailing spaces in extract file
>
> I have a extract file that needs to be generated using a Brio SQR program
> in
> which I want to fill the end of the 1st line with 25
> spaces.Someone in the SQR users group has suggested me to use XTB flag in
> the batch file.
>
> I have included the -XTB flag in the following batch file and didn't see
> any
> difference. Do I have to change the location of the flag in the batch file
> to see the trailing spaces. Can someone please send me a sample batch file
> ?
>
>
> C:\PROGRA~1\Brio\SQRSe
> rver\SYB\BINW\sqrw.exe "D:\RIN6000\BRR296M1.sqr" -vsybDfyTst04era
> syC606240/facets -XTB -XMB -e -o -keep "-IC:\PROGRA~1\Brio\REPORT~1
> \PROGRAM\;D:\RIN6000\" "-MD:\RIN6000\Mfile.txt"
> 'D:\RIN6000\dbnames_file_inc441 txt.txt'rem C:\Program
> Files\Brio\SQRServer\SYB\BINW\sqrw.exe "D:\RIN6000\BRR296M1.sqr"
> -vsybDfyTst04era syC606240/facets > BRR296M1.log
>
> Thanks!
> Maddy
>
> _______________________________________________
> 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
>
>
>
>
>
>
>
>
> 
>____________________________________________________________________________________
> Park yourself in front of a world of choices in alternative vehicles.
> Visit the Yahoo! Auto Green Center.
> http://autos.yahoo.com/green_center/
>
> _______________________________________________
> 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


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







       
____________________________________________________________________________________
Yahoo! oneSearch: Finally, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

_______________________________________________
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