PeopleSoft Query

select 'PSQRYDEFN ',a1.OPRID, QRYNAME, DESCR, VERSION, QRYTYPE, SELCOUNT, EXPCOUNT, BNDCOUNT, QRYVALID, LASTUPDDTTM, LASTUPDOPRID, QRYAPPROVED, CREATEOPRID, CREATEDTTM, APPROVEOPRID, APPROVEDTTM, EXECLOGGING, QRYDISABLED, QRYFOLDER, QRYJOINOPTIMIZE, QRYIMGTYPE, DESCRLONG 

from PSQRYDEFN a1 

where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- stores high-level query definitions with version numbers. 

select 'PSQRYSELECT ',a1.OPRID, QRYNAME, SELNUM, SELECTTYPE, PARENTSELNUM, RCDCOUNT, FLDCOUNT, CRTCOUNT, QRYDISTINCT, HAVECRTCOUNT 

from PSQRYSELECT a1 

where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;-- stores all SELECT requirements by select type, i.e. union, subselect, join,….  

select 'PSQRYDEFNLANG ',a1.OPRID, QRYNAME,language_cd,DESCR,DESCRlong 

from PSQRYDEFNLANG a1 

where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- Non-English detail is stored in it as well as in PSQRYHEADLANG

-- 1. Records

select 'PSQRYRECORD ',a1.OPRID, QRYNAME, SELNUM, RCDNUM, RECNAME, JOINTYPE, JOINRCDNUM, JOINFLDNUM, CORRNAME, QRYRECPRUNABLE ,

decode,jointype,5,'left outer join') rpJoinType

from PSQRYRECORD a1 

where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;-- stores all records used in all aspects of query creation

-- 2. Query
-- 3. Expressions

Refer 6. Criteria

-- 4. Prompts

select 'PSQRYBIND ',a1.OPRID, QRYNAME, BNDNAME, BNDNUM, FIELDNAME, HDGTYPE, HEADING, FIELDTYPE, 

LENGTH, DECIMALPOS, FORMAT, EDITTABLE, USECOUNT, USEEDIT, QRYREQUIREDPROMPT, QRYPROMPTDEFAULT 

from PSQRYBIND a1 

where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- stores all run-time 'prompt' data

select 'PSQRYBINDLANG ',a1.OPRID, QRYNAME, BNDNAME,language_cd,heading 

from PSQRYBINDLANG a1 

where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--

-- 5. Fields

select 'PSQRYFIELD ',a1.OPRID, QRYNAME, QRYFLDNAME, SELNUM, FLDNUM, RECNAME, FIELDNAME, FLDRCDNUM, FLDEXPNUM, HDGTYPE, HEADING, 

COLUMNNUM, GROUPBYNUM, ORDERBYNUM, ORDERBYDIR, TTLTYPE, SUBTTLNUM, USECOUNT, XLATTYPE, XLATEXPRTYPE, XLATFLDNUM, XLATEXPNUM, AGGREGATEFUNC 

, decode (columnum,0,'4nonFld',

    decode(recname,' ','2expr',

       decode((selnum,1,'1fld','3'))) rpQryFld

from PSQRYFIELD a1 

where QRYNAME = 'LM_LG_A_PROGRAM' 

and columnnum <> 0; and rownum <= 12 ;--- stores all fields used in all aspects of query operation

note:

psqryrecord.oprid,qryname,selnum,recname,Rcdnum match

psqryfield.oprid,qryname,selnum,recname,fldRcdnum

select 'PSQRYFIELDLANG ',a1.OPRID, QRYNAME,QRYFLDNAME,language_cd,heading  

from PSQRYFIELDLANG a1 

where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--

-- 6. Criteria

select 'PSQRYCRITERIA ',a1.OPRID, QRYNAME, SELNUM, CRTNUM, COMBTYPE, NEGATION, LPARENLVL, LCRTSELNUM, LCRTFLDNUM, CONDTYPE, /* condition 

2 =,3 <>,

4>,5not>,5<,7not>,

8 in list,9 not in list,

10 between,11 not between,

11 exists,12 not exists,

14 like,15 not like,

16 is null,17 is not null,

18 in tree,19 not in tree,

20 effdt <=,21 effdt >=,22 effdt<,23 effdt >,

24 first effdt,25 last effdt

*/

EXPRTYPE/*

0 eff seq,1 const,2field,3 expression,4 subquery,5 list,6 current date,7 tree,8 prompt,

9 between const-const

10 bet cosnt-field

11 bet const-expr

12 bet fld-const

13 between fld fld

14 between field expr

15 between exp const

16 bet expr fld

17 bet exp expr

18 tree prompt

*/, 

R1CRTSELNUM, R1CRTFLDNUM, R1CRTEXPNUM, R2CRTSELNUM, R2CRTFLDNUM, R2CRTEXPNUM, RPARENLVL, QRYOJSELNUM ,

decode(combtype,4,'hv1',5,'hv2',6,'hv3',

1,'cr1',2,'cr2',3,'cr3') /*hv is having, cr is criteria */

from PSQRYCRITERIA a1 

where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- stores all criteria expressions in code format

select 'PSQRYEXPR ',a1.OPRID, QRYNAME, EXPNUM, FIELDTYPE, LENGTH, DECIMALPOS, USECOUNT, EXPRESSIONTEXT 

from PSQRYEXPR a1 

where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- stores the text associated with each criteria expression

-- 7. Having
-- 8. Dependency
-- 9. Transformations
-- 10. View SQL
-- 11. Run

select 'PSQRYTRANS ',a1.QRYNAME, USERID, OPRID, QRYMACHINENAME, QRYDOMAINID, PROCESSID, HOSTNAME, STATUS, QRYSTARTTIME, QRYENDTIME 

from PSQRYTRANS a1 

where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--

select 'PSQRYSTATS ',a1.OPRID, QRYNAME, EXECCOUNT, AVGEXECTIME, AVGFETCHTIME, AVGNUMROWS, LASTEXECDTTM, NUMKILLS 

from PSQRYSTATS a1 

where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--

select 'PSQRYEXECLOG ',a1.OPRID, QRYNAME, APPLNAME, EXECDTTM, RUNOPRID, EXECTIME, FETCHTIME, NUMROWS, MAXROWLIMIT, KILLEDREASON 

from PSQRYEXECLOG a1 

where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--

    

    

select 'PSQRYACCLSTRECS ',a1.VERSION, CLASSID, RECNAME, RECDESCR from PSQRYACCLSTRECS a1 where  rownum <= 12 ;

--select 'PSQRYHEADLANG ',a1.* from PSQRYHEADLANG a1 where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- Non-English detail is stored in it as well as in PSQRYDEFNLANG

select 'PSQRYDEL ',a1.OPRID, QRYNAME, VERSION from PSQRYDEL a1 where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--

select 'PSQRYFAVORITES ',a1.OPRID, QRYNAME,qryowner from PSQRYFAVORITES a1 where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--

select 'PSQRYFLAGS  ',a1.QRYTIMEOUT, QRYSTATSQL from PSQRYFLAGS  a1 where rownum <= 12 ;--

select 'PSQRYLINK ',a1.qryname,qrynamechild from PSQRYLINK a1 where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;-- stores the relationships to child queries

select 'PSQRYPREFS ',a1.OPRID, QRYAUTOJOIN, QRYNAMESTYLE from PSQRYPREFS a1 where  rownum <= 12 ;--

select 'PSQRYXFORM ',a1.OPRID, QRYNAME,qryxfirmname,cdm_file_type,qryformxsl from PSQRYXFORM a1 where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--