[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: Dynamic SQR Select Clause
Ted,
*** This is an Oracle Solution *** Character Conversion will vary by
Database ***
There's a lot more to dynamically setting up an SQR select clause than
simply enclosing the entered Table Name in brackets. Your columns are
dynamic as well. Dynamic column names must be enclosed in brackets with
the synonym and type indicated (i.e. &col001=char, etc.). Trouble is you
don't know the data type until after the user has selected a table. It
may be wise to convert all columns to character format in your dynamic
list. In addition - Since you are using PSRECFIELD you have to be wary
of nested SubRecord Definitions. You can also interrogate PSKEYDEFN to
determine the primary keys and possibly prompt the user for values to
add to a where clause (i.e. AND SETID = 'MFG' etc.). Maybe it would be
more helpful to show you a SAMPLE PROGRAM. Here the user enters a Record
name and if valid, is prompted for key values. A Dynamic select list is
created (using dual as a placeholder) and a pipe-character delimited
record is written complete with Column Headings. This is a "quickie"
version that only supports one sub-record definition and does not
support LONG character types. I originally set this up to accomodate up
to 150 columns in a table - the code shows up to 10 to conserve space...
>From your requirements description this program should be exactly what
you need. This was written for PeopleSoft V6.0 and Oracle. Ignore the
Output Prompts (tdfilio.sqc) - Email if you'd like the custom SQC
file(s).
-Tony DeLia
!**********************************************************************
!* *
!* MODULE: TDEXT.SQR *
!* AUTHOR: TONY DELIA. *
!* DATE: 07/22/97. *
!* SYSTEM: TD SQR UTILITY SERIES. *
!* DESC: DYNAMIC RECORD EXTRACT (DELIMITED). *
!* *
!* USE THE -CB SQR OPTION TO VIEW THE COMMUNICATION *
!* BOX ON THE SCREEN. *
!* *
!**********************************************************************
!* *
!* TABLES: psrecdefn - Select *
!* psrecfield - Select *
!* psdbfield - Select *
!* pskeydefn - Select *
!* *
!* < Selected Table(s) are accessed DYNAMICALLY >. *
!* *
!**********************************************************************
!* *
!* OUTPUT: <Enter Output File at Prompt> *
!* *
!**********************************************************************
!* *
!* NOTE: AFTER A RECORD HAS BEEN SELECTED THE PRIMARY KEY *
!* STRUCTURE IS ANALYZED AND THE USER WILL BE PROMPTED *
!* FOR A VALUE FOR EACH KEY. PRESS <ENTER> TO OMIT THE *
!* KEY FROM THE 'WHERE CLAUSE' BEING CONSTRUCTED. *
!* *
!* FOR EXAMPLE, IF THE RECORD 'CUSTOMER' WAS SELECTED *
!* THE USER WOULD BE PROMPTED TO ENTER VALUES FOR *
!* SETID AND CUST_ID (THE PRIMARY KEYS). IF 'MFG' WAS *
!* ENTERED FOR SETID AND CUST_ID WAS BYPASSED <ENTER> *
!* THE WHERE CLAUSE WOULD BE: SETID = 'MFG'. ALL 'MFG' *
!* CUSTOMERS WOULD APPEAR IN THE EXTRACT OUTPUT FILE. *
!* *
!* A DYNAMIC SELECT LIST IS CONSTRUCTED (INCLUDING *
!* SUB-RECORD DEFINITIONS) TO EXTRACT THE DATA FROM *
!* THE CHOSEN TABLE (RECORD). A MAXIMUM OF 150 COLUMNS *
!* MAY BE SELECTED (MORE THAN ENOUGH!). IN ACTUALITY *
!* THE SELECT LIST IS NOT TRUELY DYNAMIC - 150 COLUMN *
!* PLACEHOLDERS ARE DEFINED (INITIALIZED TO BLANK) *
!* AGAINST THE 'DUAL' TABLE AND THE SELECT LIST IS *
!* UTILIZED IN A 'UNION'. IF SELECT LIST IS LESS THAN *
!* 150 COLUMNS BLANK ENTRIES ARE INCLUDED TO MAINTAIN *
!* SYNCHRONIZATION BETWEEN THE DUAL & DYNAMIC TABLES. *
!* *
!* ONCE THE EXTRACT IS COMPLETE IT CAN BE OPENED IN *
!* EXCEL USING THE PIPE (|) CHARACTER DELIMITER. *
!* *
!**********************************************************************
!* *
!* LEGAL: CONFIDENTIALITY INFORMATION. *
!* *
!* This module is the original work of Tony DeLia. It *
!* can be considered ShareWare under the following *
!* conditions. *
!* *
!* A - The author's name (Tony DeLia) remains on any *
!* and all versions of this module. *
!* B - Any modifications must be clearly identified. *
!* C - A "vanilla" copy of this module must be kept *
!* alongside any revised versions. *
!* *
!* Questions/Comments: tdelia@erols.com *
!* *
!**********************************************************************
#include 'setenv.sqc' ! Set environment
!**********************************************************************
!* Set Up *
!**********************************************************************
begin-setup
#Include 'setup02a.sqc' ! Printer and page-size init landscape
end-setup
!**********************************************************************
!* Mainline Processing *
!**********************************************************************
begin-report
do Init-DateTime
do Set-As-Of-Date
do Display-Start-Time
let #td-o-no = 1
let $td-o-warn = 'Y'
let $td-x-msg = '1'
do Enter-TD-Output
if #td-o-stat = 0
open $td-o-file as #td-o-no for-writing record=32000:vary
do Set-Defaults
do Enter-Record
if $rec-ok = 'Y'
do Build-Where
do Process-Record
do Build-Select
do Extract-Record
end-if
close #td-o-no
end-if
do Display-End-Time
end-report
!**********************************************************************
!* Set Defaults *
!**********************************************************************
begin-procedure Set-Defaults
let $ReportTitle = 'TD Record Extract'
let $ReportId = 'TDEXT'
display ' '
display $ReportId noline
display ' ' noline
display $ReportTitle
display ' '
let $TypeString = 'CharLongNbr SignDateTimeDtTm****'
create-array name=DYNdata size=150 field=DYNfield:char -
field=DYNselect:char -
field=DYNvalue:char -
field=DYNtype:char
let #DYNmax = 150
let #SELmax = 150
end-procedure
!**********************************************************************
!* Enter Record *
!**********************************************************************
begin-procedure Enter-Record
let $sw = 'Y'
while upper($sw) = 'Y'
let $sw = 'N'
input $rec maxlen=15 'Enter Record' type=char
uppercase $rec
do Test-Record
if $rec-ok = 'N'
input $sw maxlen=1 'Invalid Record. Retry? (Y/N)' type=char
end-if
end-while
end-procedure
!**********************************************************************
!* Test Record *
!**********************************************************************
begin-procedure Test-Record
let $rec-ok = 'N'
begin-select LOOPS=1
r.recname
r.sqltablename
let $rec-ok = 'Y'
let $DYNrec = &r.sqltablename
if $DYNrec = ' '
let $DYNrec = 'PS_' || $rec
end-if
from psrecdefn r
where r.recname = $rec
and r.rectype = 0
end-select
end-procedure
!**********************************************************************
!* Build Where Clause *
!**********************************************************************
begin-procedure Build-Where
display ' '
display 'Building WHERE CLAUSE for ' noline
display $rec
display ' '
let $q = chr(39)
let $DYNwhere = ''
let $DYNorder = ' order by 1'
let $DYNand = ''
let #DYNkeys = 1
begin-select
k1.fieldname
k2.longname
k2.fieldtype
k2.length
let #pos = (&k2.fieldtype * 4) + 1
let $FieldType = substr($TypeString, #pos, 4)
display &k1.fieldname noline
display ' - ' noline
display &k2.longname noline
display ' ( Type= ' noline
display $FieldType noline
display ' Length= ' noline
display &k2.length 9999 noline
display ' ) '
if &k2.fieldtype <= 4
let $data = ''
input $data 'Enter KEY data'
if not isnull($data)
display $data
if &k2.fieldtype = 4 ! Must be in 'YYYY-MM-DD' format
let $data = 'TO_DATE(' || $data ||
',' || $q || 'YYYY-MM-DD' || $q || ')'
end-if
let $DYNwhere = $DYNwhere || $DYNand ||
&k1.fieldname || ' = ' || $data
let $DYNand = ' and '
end-if
else
display 'Time (5) and Date/Time (6) Field Types excluded'
end-if
let #DYNkeys = #DYNkeys + 1
let $DYNkeys = to_char(#DYNkeys)
let $DYNorder = $DYNorder || ',' || $DYNkeys
display ' '
from pskeydefn k1,
psdbfield k2
where k1.recname = $rec
and k1.indexid = '_'
and k1.fieldname = k2.fieldname
order by k1.keyposn
end-select
if length($DYNwhere) = 0
let $DYNwhere = ' 1 = 1 '
end-if
display $DYNwhere
display $DYNorder
display ' '
end-procedure
!**********************************************************************
!* Process Main *
!**********************************************************************
begin-procedure Process-Record
let $DYNstring = ''
let #idx = 0
begin-select
a.fieldnum
0 &a.subnum
a.fieldname
a.edittable
b.longname
b.shortname
b.length
b.decimalpos
b.fieldtype
decode(k.fieldname,a.fieldname,'Y',' ') &k.key
let $DYNchr = 'NULL'
let #pos = (&b.fieldtype * 4) + 1
let $FieldType = substr($TypeString, #pos, 4)
evaluate $FieldType
when = 'Long'
let $DYNchr = $q || '***LONG***' || $q
when = 'Nbr '
when = 'Sign'
let $DYNchr = 'TO_CHAR(NVL(' || &a.fieldname || ',0))'
when = 'Date'
when = 'DtTm'
let $DYNchr = 'TO_CHAR(' || &a.fieldname ||
',' || $q || 'YYYY-MM-DD' || $q || ')'
when = 'Time'
let $DYNchr = 'TO_CHAR(' || &a.fieldname ||
',' || $q || 'HH:MM:SS' || $q || ')'
when-other
let $DYNchr = &a.fieldname
end-evaluate
let DYNdata.DYNfield (#idx) = &a.fieldname
let DYNdata.DYNselect (#idx) = $DYNchr
let DYNdata.DYNtype (#idx) = $FieldType
let DYNdata.DYNvalue (#idx) = ' '
let $x = '<' || $FieldType || '> '
let $x = $x || lpad(&a.fieldname,20,' ')
display $x
let #idx = #idx + 1
let $DYNstring = $DYNstring || &a.fieldname || '|'
if #idx >= #DYNmax
display 'Dynamic Column Limit Reached...'
exit-select
end-if
from psrecfield a,
psdbfield b,
pskeydefn k
where a.recname = $rec
and a.fieldname = b.fieldname
and k.recname (+) = a.recname
and k.fieldname (+) = a.fieldname
and k.indexid (+) = '_'
!
! Include SubRecord Definitions - 1 Level Only
!
union all
select a.fieldnum,
sub.fieldnum,
sub.fieldname,
sub.edittable,
b.longname,
b.shortname,
b.length,
b.decimalpos,
b.fieldtype,
' '
from psrecfield a,
psrecfield sub,
psdbfield b
where a.recname = $rec
and a.fieldname = sub.recname
and b.fieldname = sub.fieldname
and not exists
(select 'X'
from psdbfield b2
where b2.fieldname = a.fieldname)
order by 1, 2, 3
end-select
display ' '
let #DYNmax = #idx
write #td-o-no from $DYNstring
end-procedure
!**********************************************************************
!* Build Select Statement *
!**********************************************************************
begin-procedure Build-Select
let $DYNselect = $q || 'SELECT' || $q
let #idx = 0
while #idx < #DYNmax
let $DYNselect = $DYNselect || ',' || DYNdata.DYNselect (#idx)
let #idx = #idx + 1
end-while
while #idx < #SELmax ! Complete Union Select List
let $DYNselect = $DYNselect || ',' || $q || ' ' || $q
let #idx = #idx + 1
end-while
end-procedure
!**********************************************************************
!* Dynamic Record Extract (Sort of...) *
!**********************************************************************
begin-procedure Extract-Record
let $TEMPcol0 = $q || 'DUMMY' || $q
let $TEMPcol1 = $q || ' ' || $q
begin-select
[$TEMPcol0] &col000=char
[$TEMPcol1] &col001=char
[$TEMPcol1] &col002=char
[$TEMPcol1] &col003=char
[$TEMPcol1] &col004=char
[$TEMPcol1] &col005=char
[$TEMPcol1] &col006=char
[$TEMPcol1] &col007=char
[$TEMPcol1] &col008=char
[$TEMPcol1] &col009=char
[$TEMPcol1] &col010=char
! Columns 11 - 150 removed
if &col000 = 'SELECT'
do Move-Column-Values
let $DYNstring = ''
let #idx = 0
while #idx < #DYNmax
let $data = DYNdata.DYNvalue (#idx)
let $DYNstring = $DYNstring || $data || '|'
let #idx = #idx + 1
end-while
write #td-o-no from $DYNstring
end-if
from dual
union all
select [$DYNselect]
from [$DYNrec]
where [$DYNwhere]
[$DYNorder]
end-select
end-procedure
!**********************************************************************
!* Move Column Values *
!**********************************************************************
begin-procedure Move-Column-Values
let DYNdata.DYNvalue (001 - 1) = &col001
let DYNdata.DYNvalue (002 - 1) = &col002
let DYNdata.DYNvalue (003 - 1) = &col003
let DYNdata.DYNvalue (004 - 1) = &col004
let DYNdata.DYNvalue (005 - 1) = &col005
let DYNdata.DYNvalue (006 - 1) = &col006
let DYNdata.DYNvalue (007 - 1) = &col007
let DYNdata.DYNvalue (008 - 1) = &col008
let DYNdata.DYNvalue (009 - 1) = &col009
let DYNdata.DYNvalue (010 - 1) = &col010
! Columns 11 - 150 removed
end-procedure
!**********************************************************************
!* Alternate TD Prompt (See TDFILIO.SQC) *
!**********************************************************************
begin-procedure Alternate-TD-Prompt
evaluate $td-x-msg
when = '1'
input $td-x-file 'Enter Extract OUTPUT Path/Filename'
when-other
input $td-x-file 'Enter Path/Filename'
end-evaluate
end-procedure
!**********************************************************************
!* Include Members: *
!**********************************************************************
#Include 'tdfilio.sqc' !File Input/Output Prompts
#Include 'tddispl.sqc' !Set/Display Starting/Ending Time
#Include 'curdttim.sqc' !Get-Current-DateTime procedure
#Include 'datetime.sqc' !Routines for date and time formatting
!**********************************************************************
!* End of Program *
!**********************************************************************
Fecteau, L Ted wrote:
>
> Does anyone know how to dynamically set up a SQR Select clause such that the
> column
> names do not have to be explicitly named within the begin-select/end-select?
> I am
> using PeopleSoft's PSRECFIELD table to determine the columns within a user
> specified
> record in order to select the data from that record. For example, if the
> user specifies
> the record "PROJECT" as a parameter, I would like to do a "select *" from
> that table
> and place the resulting data in an output file.
>
> Any help would be much appreciated.
>
> >
> >
> >
--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com