[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: Select * - sample SQR for Oracle
- Subject: Re: Select * - sample SQR for Oracle
- From: Jamie Harris <JHarris@FCC.CC.MD.US>
- Date: Mon, 1 Apr 2002 13:17:59 -0500
That's very similar to one of my programs. It's nice to see that it
really *can* be done in SQR. :)
-----------------------------------------------------
Jamie Harris
Junior Systems Programmer/Analyst
Information Technology Division
Frederick Community College
-----------------------------------------------------
>>> Dmueller@NMH.ORG 3/29/2002 3:29:43 PM >>>
Below is an SQR that I wrote that will prompt for a TABLE NAME and an
optional WHERE clause,
and dump the data to a spreadsheet format.
It is written for Oracle, but could be easily modified to handle other
database types.
(Just change the table name and column names in the
"build-column-list"
procedure)
Basically you select one BIG character field, which is all the columns
concatenated together and separated by commas, then write it to an
output
file,
rather than issue PRINT commands. Using .CSV (Comma Separated Values)
output format allows you to open the file with Excel, which keeps
formatting
nice and eliminates wrapping. Excel also allows printing in a variety
of
formats.
Here it is:
!---------------------------------------------
! Select * from a table (ORACLE version)
!---------------------------------------------
begin-report
input $Table-Name 'Enter Table Name'
let $Table-Name = upper($Table-Name)
input $Where 'Enter optional WHERE clause'
open 'c:\temp\sel.csv' as 2 for-writing record=5000:vary
do build-column-list
let $dis= 'Column list for table '||$Table-Name||':'
display $dis
display $col-list
display 'Where clause: '
display $where
write 2 from $col-hdr
do get-data
end-report
!---------------------------------------------
begin-procedure get-data
!---------------------------------------------
begin-SELECT
[$col-list] &record=char
write 2 from &record
from
[$table-name]
[$where]
end-SELECT
end-procedure
!---------------------------------------------
begin-procedure build-column-list
!---------------------------------------------
let $col-hdr = ''
let $col-list = ''
begin-SELECT
c.column_id
c.column_name
c.data_type
let $type = substr(&c.data_type ,1,4)
let $col = rtrim(&c.column_name,' ')
let $col-hdr = $col-hdr||','
||$col
! build for each field: ||','||'"'||$col||'"'
! then strip first 7 after loop
evaluate $type
when = 'VARC' !Character field
let $col-list = $col-list
||'||'
||''''
||','
||''''
||'||'
||''''
||'"'
||''''
||'||'
||$col
||'||'
||''''
||'"'
||''''
when = 'DATE' !Date field
let $col-list = $col-list
||'||'
||''''
||','
||''''
||'||'
||''''
||'"'
||''''
||'||'
||'to_char('||$col||','||''''||'yyyy-mm-dd'||''''||')'
||'||'
||''''
||'"'
||''''
when = 'NUMB' !Number field
let $col-list = $col-list
||'||'
||''''
||','
||''''
||'||'
||''''
||'"'
||''''
||'||'
||'to_char('||$col||','||''''||'999999999.99'||''''||')'
||'||'
||''''
||'"'
||''''
end-evaluate
from all_tab_columns c
where c.table_name = $table-name
order by 1
end-SELECT
!remove first 7 characters from list
let #tmp = length($col-list) - 7
let $col-list = substr($col-list,8,#tmp)
!remove first comma from header
let #tmp = length($col-list) - 1
let $col-hdr = substr($col-hdr,2,#tmp)
end-procedure
> -----Original Message-----
> From: Jamie Harris [SMTP:jharris@FCC.CC.MD.US]
> Sent: Thursday, March 28, 2002 7:41 PM
> To: SQR-USERS@list.iex.net
> Subject: Re: Select ALL
>
> Peter, you seem to be saying that nobody would ever want to "select
*
> from" in an SQR program, so it's not necessary for the language to
> support it.
>
> If so, I have to respectfully disagree with that. :) For reporting
> purposes, you're absolutely right: "select * from" isn't very
useful.
> You're also right that adding the ability to SQR wouldn't gain much
at
> all because you wouldn't be able to do anything with the data
returned
> (without changing the rules of the language).
>
> However, I have several utility programs I've written for
> troubleshooting/testing purposes (where the data itself is much more
> important than how it looks) which make use of "select * from" and
are
> *quite* useful to me. If I had to give these programs up, I'd
> definitely put up a fight since I use them very frequently.
>
> Since SQR just doesn't do such things I've had to write these
programs
> using Perl and one of the database modules (I personally use
Win32::ODBC
> but DBI [which is much more platform & database-independent] would
work
> too). It works for me, I know both languages and to me they are the
> screwdriver and the hammer -- two valuable tools that this
particular
> "carpenter" cannot live without.
>
> For someone who doesn't know both Perl and SQR, but can still think
of a
> need for "select * from", it can be frustrating that SQR doesn't let
> them do it.
>
> To everyone: please, we don't need another "SQR vs. Perl" debate.
I'm
> just pointing out that I've had the need for "select * from" on a
few
> occasions and I've had to turn to another language for it.
>
> [note: lengthy example below to illustrate one of the things I use
> "select * from" to do.]
>
> I have one program that I use to generate test populations at random.
I
> give it an SQL statement (pasted right out of query analyzer,
comments
> and all) and a row count. It then picks that many rows at random
and
> dumps the data to a tab-delimited file (with the first row
containing
> the field names). If I care about field names and the order they
are
> in, I can specify all that in the SQL statement like this:
>
> select
> A.EMPLID 'Student ID',
> A.NAME 'Name',
> B.CUM_GPA 'Cumulative GPA',
> B.CUR_GPA 'Current GPA',
> C.* -- I don't care what table C looks like
> from (...)
> where (...)
>
> If I'm not picky about it, I just use "select * from" and it works
just
> as well: why type the names all out if you want everything and don't
> care what order? I can then open the output in Excel or feed it
into
> another program for further processing.
>
> A re-usable program such as this would be very hard (as far as I
know)
> to write in SQR if it can be done at all. Sure, you could have an
SQR
> that asks for the table name and then generates another SQR that will
do
> this to that table, but I can't see a way to write one program that
will
> do this for *any* table.
>
> This isn't the best example of why "select * from" might be
something
> you'd want to do in SQR, but it's an example of the *kinds* of
things
> one *could* do with that ability. You'd also have to re-work the
SQR
> language itself in order to allow something to be *done* with the
data.
> I don't know if it would be worth it in the long run to modify SQR
in
> such a way.
>
> ps. Some of my other programs would be better examples, but
explaining
> what they do and how I use them would make for a *much* longer email
> than this one. :)
>
> -----------------------------------------------------
> Jamie Harris
> Junior Systems Programmer/Analyst
> Information Technology Division
> Frederick Community College
> -----------------------------------------------------
> >>> pburton@BRIO.COM 03/28/02 10:48 AM >>>
> Greetings,
>
> Supporting "SELECT * FROM TABLE" will get you absolutely nothing.
>
> Let us suppose SQR did support that functionality. How would
manipulate
> a
> column value? What about placement on the page? What about
formatting?
> In
> order to do these things you need to know the name of the column. I
> doubt
> that anybody would accept blindly printing a database table in the
order
> of
> the columns defined and using default formatting settings. You
wouldn't
> have any idea of what is being printed.
>
> You can use ReportBuilder to browse the database schema and create
an
> SQR
> report.
>
> Peter
BEGIN:VCARD
VERSION:2.1
X-GWTYPE:USER
FN:Harris, Jamie
TEL;WORK:2668
ORG:Work;Division of Information Technology
EMAIL;WORK;PREF:JHarris@fcc.cc.md.us
N:Harris;Jamie
TITLE:Systems Analyst, PeopleSoft
ADR;DOM;WORK;PARCEL;POSTAL:;L-205
LABEL;DOM;WORK;PARCEL;POSTAL;ENCODING=QUOTED-PRINTABLE:Harris, Jamie=0A=
L-205
END:VCARD