CODEWITHSUNDEEP
oracle-databasepeoplesoftpeoplecode
richard
richard

Reputation: 11

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

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'

Upvotes: 1

Views: 507

Answers (1)

Walucas
Walucas

Reputation: 2568

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 enter image description here

and select has 5 fields: enter image description here

Upvotes: 2

Related Questions