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

["the dragon" <ceprn@hotmail.com>]

elementary sqr coding.  Anything that is not a column in the table can not 
be in column one of the select...

NOTE: you might want to consider better coding standards when writing sqr 
programs.  While what you're doing will work as soon as you get the code 
correct, it is not the "right" way to code in sqr.

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

peace,
clark 'the dragon' willis


PSA: Salary <> Slavery. If you earn a salary, your employer is renting your 
services for 40 hours a week, not purchasing your soul. Your time is the 
only real finite asset that you have, and once used it can never be 
recovered, so don't waste it by giving it away.

I work to live; I don't live to work.

"Time is the coin of your life. It is the only coin you have, and only you 
can determine how it will be spent. Be careful lest you let other people 
spend it for you." -- Carl Sandburg (1878 - 1967)

It is impossible to defeat an ignorant man in argument. -- William G. McAdoo

Religion is regarded by the common people as true, by the wise as false, and 
by the rulers as useful. -- Seneca

"I distrust those people who know so well what God wants them to do because 
I notice it always coincides with their own desires." - Susan B. Anthony




----Original Message Follows----

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

_________________________________________________________________
Booking a flight? Know when to buy with airfare predictions on MSN Travel. 
http://travel.msn.com/Articles/aboutfarecast.aspx&ocid=T001MSN25A07001


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