QRYCONVERT_VW

index back

Crystal Enterprise Qry Convert

Crystal Enterprise Query Conversion View This is used by pscrconv.exe in PeopleTools bin client directory. After all Crystal Reports have been converted to Business Objects Enterprise this view would no longer be needed.
SELECT D.QRYNAME , F.QRYFLDNAME , %TrimSubstr(%TrimSubstr(D.FIELDNAME,1,18) %Concat '.' %Concat G.CORRNAME, 1,24) FROM PSQRYFIELD D , PSQRYFIELD F , PSQRYRECORD G WHERE D.QRYNAME = F.QRYNAME AND D.OPRID = F.OPRID AND D.QRYNAME = G.QRYNAME AND D.OPRID = G.OPRID AND D.SELNUM = G.SELNUM AND D.FLDRCDNUM = G.RCDNUM AND D.COLUMNNUM <> 0 AND F.SELNUM = 1 AND D.FIELDNAME <> ' ' AND D.COLUMNNUM = F.COLUMNNUM AND D.SELNUM <> 1 AND NOT EXISTS ( SELECT 'X' FROM PSQRYFIELD E WHERE D.QRYNAME = E.QRYNAME AND D.OPRID = E.OPRID AND D.FIELDNAME = E.FIELDNAME AND D.COLUMNNUM = E.COLUMNNUM AND E.SELNUM = 1) UNION SELECT D.QRYNAME , D.QRYFLDNAME , D.FIELDNAME FROM PSQRYFIELD D WHERE D.FIELDNAME <> ' ' AND D.SELNUM = 1 AND NOT EXISTS ( SELECT 'X' FROM PSQRYFIELD E WHERE D.QRYNAME = E.QRYNAME AND D.OPRID = E.OPRID AND D.QRYFLDNAME <> E.QRYFLDNAME AND (D.FIELDNAME = E.FIELDNAME OR D.FIELDNAME = E.QRYFLDNAME) AND (D.FLDNUM >= E.FLDNUM OR E.COLUMNNUM <> 0) AND E.SELNUM = 1) UNION SELECT D.QRYNAME , D.QRYFLDNAME , D.FIELDNAME FROM PSQRYFIELD D WHERE D.FIELDNAME <> ' ' AND D.SELNUM = 1 AND D.COLUMNNUM <> 0 AND NOT EXISTS ( SELECT 'X' FROM PSQRYFIELD E WHERE D.QRYNAME = E.QRYNAME AND D.OPRID = E.OPRID AND D.QRYFLDNAME <> E.QRYFLDNAME AND D.FIELDNAME = E.FIELDNAME AND E.COLUMNNUM <> 0 AND D.FLDNUM >= E.FLDNUM AND E.SELNUM = 1) UNION SELECT DISTINCT F.QRYNAME , F.QRYFLDNAME , G.FIELDNAME FROM PSQRYFIELD F , PSQRYFIELD G WHERE F.QRYNAME = G.QRYNAME AND F.OPRID = G.OPRID AND F.FLDEXPNUM > 0 AND G.COLUMNNUM = F.COLUMNNUM AND F.COLUMNNUM <> 0 AND G.FIELDNAME <> ' ' AND G.SELNUM <> 1 AND F.SELNUM = 1 AND NOT EXISTS ( SELECT 'X' FROM PSQRYFIELD D WHERE D.QRYNAME = F.QRYNAME AND D.OPRID = F.OPRID AND D.FIELDNAME = G.FIELDNAME AND D.COLUMNNUM <> 0 AND D.SELNUM = 1 ) UNION SELECT F.QRYNAME , F.QRYFLDNAME , F.QRYFLDNAME FROM PSQRYFIELD F WHERE F.FLDEXPNUM > 0 AND F.SELNUM = 1 UNION SELECT B.QRYNAME , B.QRYFLDNAME , %TrimSubstr(%TrimSubstr(B.FIELDNAME,1,18) %Concat '.' %Concat A.CORRNAME, 1,24) FROM PSQRYFIELD B , PSQRYRECORD A WHERE A.QRYNAME = B.QRYNAME AND A.OPRID = B.OPRID AND A.SELNUM = B.SELNUM AND A.RCDNUM = B.FLDRCDNUM AND A.SELNUM = 1 AND EXISTS ( SELECT 'X' FROM PSQRYFIELD C WHERE C.QRYNAME = B.QRYNAME AND C.OPRID = B.OPRID AND C.FIELDNAME = B.FIELDNAME AND C.QRYFLDNAME <> B.QRYFLDNAME) UNION SELECT B.QRYNAME , C.QRYFLDNAME , %TrimSubstr(%TrimSubstr(B.FIELDNAME,1,18) %Concat '.' %Concat A.CORRNAME, 1,24) FROM PSQRYFIELD B , PSQRYRECORD A , PSQRYFIELD C WHERE B.COLUMNNUM <> 0 AND A.QRYNAME = B.QRYNAME AND A.OPRID = B.OPRID AND A.SELNUM = B.SELNUM AND A.RCDNUM = B.FLDRCDNUM AND B.SELNUM <> 1 AND B.QRYNAME = C.QRYNAME AND B.COLUMNNUM = C.COLUMNNUM AND B.OPRID = A.OPRID AND B.OPRID = C.OPRID AND C.SELNUM = 1

PeopleSoft Field NameField TypeColumn TypeDescription
QRYNAMECharacter(30)VARCHAR2(30) NOT NULLQuery Name
QRYFLDNAMECharacter(30)VARCHAR2(30) NOT NULLSource Field
DESCRCharacter(30)VARCHAR2(30) NOT NULLDescription
index back(c)David Kurtz 2019, www.go-faster.co.ukPeopleTools 8.54
PTRef generated on 09-JUN-19