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

[maddy <madhudvm@gmail.com>]

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