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

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

Maddy,

This is something you might do.  It really depends on your situation.

Begin-Report
 Open 'q.txt' As 1 For-Writing Record=80:vary
 Write 1 From 'Cust ID   Customer Name'
 Let $Dash = RPad('', 55, '-')
 Write 1 From $Dash
 Begin-Select
cust_num
name
 Add 1 To #Count
 Write 1 From &cust_num:10 &name:45
 from customers
  order by 1
 End-Select
 Let $Count = 'Records = ' || Edit(#Count, '88888')
 Write 1 From ''
 Write 1 From $Count
 Close 1
End-Report


Cust ID   Customer Name
-------------------------------------------------------
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

Records = 17


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: Monday, August 13, 2007 3:12 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

Thanks a lot Peter. After indenting, my code is working. I am able to create
the extract file in the required format but want to add header and trailer
information to this extract.
The header information  in this extract file is hardcoded whereas the
trailer should show the total number of records in the extract.Pleaseadvice.


On 8/13/07, Peter Burton <peter.burton@oracle.com> wrote:
>
> Maddy,
>
> You need to indent the "Write" command by at least one space.
>
> 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: Monday, August 13, 2007 10:49 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
>
> Thanks for your quick response. I have followed your instructions but
> still
> getting the below error.Please take a look at the code and let me know
> if anything needs to be changed in my code. I didn't understand why the
> same
> piece of code is working for you and why it is giving errors to me. Please
> advice.
>
> ***************************ERROR
> ************************************************
>
> (SQR 5528) Sybase dbdesc: ct_results error in cursor 1:
>
> (102) Incorrect syntax near '1'.
>
> Error on line 19:
>
> (SQR 3716) Error in SQL statement.
>
> Errors were found in the program file.
>
> SQR: Program Aborting.
>
>
> 
>***************************************************************************************************
> Begin-Setup
> ask dbnames_file
> #include '{dbnames_file}
> end-setup
>
> Begin-Report
> Open 'q.txt' As 1 For-Writing Record=80:vary
> Begin-Select
> MEME_FIRST_NAME
> Write 1 From &MEME_FIRST_NAME:10
> FROM {BRIO_DB}..CMC_MEME_MEMBER
> WHERE MEME_LAST_NAME = 'DUKE' AND MEME_FIRST_NAME = 'BETTY'
> End-Select
> Close 1
> End-Report
>
> 
>**************************************************************************************************
>
>
> Thanks!
> Maddy
>
>
> On 8/13/07, Peter Burton <peter.burton@oracle.com> wrote:
> >
> > 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
> >
>
> _______________________________________________
> 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


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