Re: Writing a data file dynamically

[Ashish Bhatt <ashish_bhatt@HOTMAIL.COM>]

Walter, yes it is possible.

I have done this.

You will need to read user_tab_columns table if you are using ORACLE as the
database.

Its very simple.

Call me if you need any help.

Have fun.

Regards.

Ashish Bhatt.
(408) 765 6595


----- Original Message -----
From: "Walter Fitch" <Walter.Fitch@OREGONSTATE.EDU>
To: <SQR-USERS@list.iex.net>
Sent: Monday, September 09, 2002 1:16 PM
Subject: Writing a data file dynamically


> We use SQR to write data files (reports) on a daily basis.
> Usually we output the data to a tab delimited file, that our users can
> open and view in Excel.
> When writing the data file, it's extremely cumbersome to specify all
> the fields of the query and the "Write" command.
>
> Question: Is there a way to build and execute the write funcation
> dynamically, writing these values on the fly? (The code below is only
> the Procedure in question. I assign values to my table and delimiter
> variables elsewhere.)
>
> Any suggestions? With the "Before" I have to hard code my variable names,
> whereas I'd like to build them dynamically ala my "After".
>
> Thanks,
> Walter Fitch
>
> Before:
> Begin-Procedure WriteReport
> BEGIN-SELECT
> f1,  f2,  f3,  f4,  f5,  f6,  f7,  f8,  f9,  f10,
> f11, f12, f13, f14, f15, f16, f17, f18, f19, f20
>
>  write 1 from
>     &f1  $_dltr
>     &f2  $_dltr
>     &f3  $_dltr
>     &f4  $_dltr
>     &f5  $_dltr
>     &f6  $_dltr
>     &f7  $_dltr
>     &f8  $_dltr
>     &f9  $_dltr
>     &f10 $_dltr
>     &f11 $_dltr
>     &f12 $_dltr
>     &f13 $_dltr
>     &f14 $_dltr
>     &f15 $_dltr
>     &f16 $_dltr
>     &f17 $_dltr
>     &f18 $_dltr
>     &f19 $_dltr
>     &f20
>
> From    [$_printTable]
> END-SELECT
> End-Procedure WriteReport
>
> After: (I have something like this in mind.)
> Begin-Procedure WriteReport(#fieldCt)
> ! *** So far futile attempts to make this procedure dynamic. Ideally I
want
> !  this to write out a variable number of fields.
> !
>    let #i=1
>    while #i<=#fieldCt
>        let $selectStr = $selectStr || 'f' || to_char(#i) || ' &f' ||
> to_char(#i) || ', '
>        if #i=1
>           let $writeStr = ' write 1 from ' || '&f' || to_char(#i) || '
> $_dltr '
>        else
>           let $writeStr = $writeStr || '&f' || to_char(#i) || ' $_dltr '
>        end-if
>        let #i=#i+1
>    end-while
>
> BEGIN-SELECT
> [$selectStr]
> [$writeStr]
>
> From    [$_printTable]
> END-SELECT
> End-Procedure WriteReport
>
> > Walter Fitch
> > Information Systems
> > (541) 737-9098
> > OSU Foundation
> >
>