error while building a view on Application designer "invalid number of column names specified "

Question

SELECT EMPLID
, LST_ASGN_START_DT  
  FROM PS_JOB A 
 WHERE A.EFFDT = ( 
 SELECT MAX(EFFDT) 
  FROM PS_JOB 
 WHERE A.EMPLID = EMPLID 
   AND A.EMPL_RCD = EMPL_RCD 
   AND EFFDT <= SYSDATE ) 
   AND A.EFFSEQ = ( 
 SELECT MAX(EFFSEQ) 
  FROM PS_JOB 
 WHERE A.EMPLID = EMPLID 
   AND A.EMPL_RCD = EMPL_RCD 
   AND A.EFFDT = EFFDT ) 
   AND HR_STATUS='A'

When running it directly on the db i get no errors but when i compile and build it on application designer I get an error of "invalid number of column names specified "

I have also tried this but still getting the same error.

SELECT A.EMPLID 
 , A.LST_ASGN_START_DT 
  FROM PS_JOB A 
 WHERE A.EFFDT = ( 
 SELECT MAX(EFFDT) 
  FROM PS_JOB 
 WHERE A.EMPLID = EMPLID 
   AND A.EMPL_RCD = EMPL_RCD 
   AND EFFDT <= SYSDATE ) 
   AND A.EFFSEQ = ( 
 SELECT MAX(EFFSEQ) 
  FROM PS_JOB 
 WHERE A.EMPLID = EMPLID 
   AND A.EMPL_RCD = EMPL_RCD 
   AND A.EFFDT = EFFDT ) 
   AND A.HR_STATUS='A'

and this also but nothing..

SELECT A.EMPLID 
 , A.LST_ASGN_START_DT 
  FROM PS_JOB A 
 WHERE A.EFFDT = ( 
 SELECT MAX(EFFDT) 
  FROM PS_JOB B 
 WHERE A.EMPLID = B.EMPLID 
   AND A.EMPL_RCD = B.EMPL_RCD 
   AND B.EFFDT <= SYSDATE ) 
   AND A.EFFSEQ = ( 
 SELECT MAX(EFFSEQ) 
  FROM PS_JOB C 
 WHERE A.EMPLID = C.EMPLID 
   AND A.EMPL_RCD = C.EMPL_RCD 
   AND A.EFFDT = C.EFFDT ) 
   AND A.HR_STATUS='A'

score 2 · Answer 1 · answered May 22 '19 at 20:23

You have two columns on your select, and probably have a different number of columns on the record definition. The sql statement and the record definition should have the same number of columns (but not necessarily the same field names)

Look at this example: record has 5 fields

and select has 5 fields:

error while building a view on Application designer "invalid number of column names specified "

1 Answers1