Re: Select ALL

[Allen Cunningham <allen.cunningham@SONOMA.EDU>]

I was thinking SELECT * also, but then I thought, 'this is the first thing
you learn in SQL, it can't be this elementary'....
Oh well, disregard, if not applicable.

ac

-----Original Message-----
From: Discussion of SQR, Brio Software's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of the dragon
Sent: Wednesday, March 27, 2002 2:16 PM
To: SQR-USERS@list.iex.net
Subject: Re: Select ALL


Allen,

Maybe I mustunderstand what your procedure does, as it relates to the issue,
but (and pardon me if I am wrong) I think you are solving the wrong problem.

If I understand Maria correctly, she wants to use

select *
from table_name
where conditions.....

and get back all the columns.

If table_a has cola varchar(10), colb number(3,0) and colc date

she wants to type in

select *
from table_a

and have it automatically build the columns and assign the sqr variables
&table_a.cola, &table_a.colb and &table_a.colc...

The last time I checked, this wasn't possible in sqr.

An earlier suggestion was to build the columns dynamically.  On this one
also, the last time I checked, you had to know the datatype to build dynamic
sql.

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.

"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)


Here is a proc I use to build a column list, modify it for your purposes:

begin-procedure Build-Columns($recname, :#fldcount, :$Col_List)
let $Col_List = ' '

begin-select
A.FIELDNAME      &a.fieldname,
A.SUBRECORD      &a.subrecord,
A.FIELDNUM       &a.fieldnum,
B.FIELDTYPE      &a.fieldtype,
B.LENGTH         &a.length,
B.DECIMALPOS     &a.decimalpos

     let $Col_List = $Col_List || &a.FIELDNAME || ' ,'
     if &a.subrecord = 'Y'
         let $subrecfound = &a.subrecord
     end-if
     let #rowcnt = &a.fieldnum
     put &a.fieldnum $recname &a.fieldname &a.fieldtype &a.decimalpos
$Key_Found into STAGE_RECORD_LAYOUT(#rowcnt)

FROM
PSRECFIELD A,
PSDBFIELD B

WHERE
A.RECNAME = $recname and
B.FIELDNAME = A.FIELDNAME
end-select

let #fldcount = &a.fieldnum
let $Col_List = rtrim($Col_List, ',')
SHOW ' $Col_List : ' $Col_List

end-procedure Build-Columns


Allen Cunningham
Sonoma State University
allen.cunningham@sonoma.edu



-----Original Message-----

Is there any way in SQR to select all fields in a table rather than listing
each field out on it's own line in the BEGIN-SELECT statement?

Also does anyone know if you can insert spaces for values for a bunch of
fields that are right next to each other. Right now I enter Let <fieldname>
= ' ' for about 20 different fields that are all in order. So my code gets
very long.

*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
Maria K Brickl
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*



_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx