Search code examples
sqloracle-databasepeoplesoft

Error Position: 27 Return: 1730 - ORA-01730: invalid number of column names specified

I need help in creating a view in PeopleSoft using PeopleTools Application Designer. When running the view SQL below in ORACLE SQL Developer, it selects perfectly fine but when I create it as view in App Designer I get the error below:

Error: UM_7902_VW - SQL Error. Error Position: 27 Return: 1730 - ORA-01730: invalid number of column names specified.

Here is my sql:

SELECT A.EMPLID, B.NAME, A.ADM_APPL_NBR, C.ADMIN_FUNCTION, A.STDNT_CAR_NBR, 
A.ACAD_CAREER, D.APPL_PROG_NBR, D.ADMIT_TERM, C.CHECKLIST_CD, E.DESCRSHORT, 
C.CHECKLIST_STATUS, C.STATUS_DT, C.DUE_DT AS C_DUE_DT, C.COMM_COMMENTS, 
C.SEQ_3C, F.CHKLST_ITEM_CD,
G.DESCR, F.ITEM_STATUS, F.STATUS_DT, F.DUE_DT AS F_DUE_DT, F.RESPONSIBLE_ID
FROM PS_ADM_APPL_DATA A
JOIN PS_PERSON_NAME B ON B.EMPLID = A.EMPLID
JOIN PS_PERSON_CHECKLST C ON C.COMMON_ID = A.EMPLID
JOIN PS_ADM_APPL_PROG D ON D.EMPLID = A.EMPLID
AND D.EMPLID = A.EMPLID
AND D.ACAD_CAREER= A.ACAD_CAREER
AND D.STDNT_CAR_NBR= A.STDNT_CAR_NBR
AND D.ADM_APPL_NBR= A.ADM_APPL_NBR
AND D.EFFDT = 
(SELECT MAX(D2.EFFDT) FROM PS_ADM_APPL_PROG D2
WHERE D.EMPLID = D2.EMPLID
AND D.ACAD_CAREER = D2.ACAD_CAREER
AND D.STDNT_CAR_NBR = D2.STDNT_CAR_NBR
AND D.ADM_APPL_NBR = D2.ADM_APPL_NBR
AND D.APPL_PROG_NBR = D2.APPL_PROG_NBR
AND D2.EFFDT <= SYSDATE)
AND D.EFFSEQ =
(SELECT MAX(D3.EFFSEQ) FROM PS_ADM_APPL_PROG D3
WHERE D.EMPLID = D3.EMPLID
AND D.ACAD_CAREER = D3.ACAD_CAREER
AND D.STDNT_CAR_NBR = D3.STDNT_CAR_NBR
AND D.ADM_APPL_NBR = D3.ADM_APPL_NBR
AND D.APPL_PROG_NBR = D3.APPL_PROG_NBR
AND D.EFFDT <= D3.EFFDT)
JOIN PS_CS_CHKLST_TBL E ON E.CHECKLIST_CD = C.CHECKLIST_CD
AND E.EFFDT = (SELECT MAX(E2.EFFDT) FROM PS_CS_CHKLST_TBL E2 WHERE 
E2.INSTITUTION = E.INSTITUTION AND E2.CHECKLIST_CD = E.CHECKLIST_CD)
AND E.EFF_STATUS = 'A'
JOIN PS_PERSON_CHK_ITEM  F ON F.COMMON_ID = C.COMMON_ID
AND F.SEQ_3C = C.SEQ_3C
JOIN PS_SCC_CKLSITM_TBL  G ON G.CHKLST_ITEM_CD = F.CHKLST_ITEM_CD
AND G.EFF_STATUS = 'A'
AND G.EFFDT = (SELECT MAX(G2.EFFDT) FROM PS_SCC_CKLSITM_TBL  G2 WHERE 
G2.CHKLST_ITEM_CD = G.CHKLST_ITEM_CD)
JOIN PS_TERM_TBL H ON H.INSTITUTION = E.INSTITUTION
AND H.ACAD_CAREER = A.ACAD_CAREER

I would appreciate any help i can get. Thanks!

Solution

  • In App Designer when modeling the record: do you have an equal amount of fields in the record definition as you have in your select SQL? These must match, otherwise this error appears when building in App Designer.