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