CODEWITHSUNDEEP
sqloracle-databasepeoplesoft
Link
Link

Reputation: 171

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!

Upvotes: 1

Views: 1742

Answers (2)

Robin Debruyne
Robin Debruyne

Reputation: 438

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.

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142713

It looks as if you took create view some_view (col1, col2, ..., coln) as from one of its previous incarnations, modified the select statement you posted (added or removed some columns) and - once you stick it together - number of columns doesn't match any more.

The simplest way out of it is to let Oracle do it, i.e. don't name columns:

create view some_view as
  select <your select goes here>

Though, pay attention to column names select returns; you can't have something like

select max(some_value),  --> this - must have an alias
       my_name

because columns query returns must have their aliases.

Upvotes: 0

Related Questions