Inserting value for SQR Report correctly

Question

I need help getting the job code DESCR included in this insert statement.

I keep getting this error SQL Status = 1400, SQL Error = ORA-01400: cannot insert NULL into ("SYSADM"."PS_J_ACTION_COUNTS"."JOBCODE_DESCR")

begin-SELECT Loops=1
E.BUSINESS_UNIT
E.JOBCODE 
EA.DESCR
E.EFFDT
E.EFFSEQ

  do Insert-XferOut

FROM  PS_JOB E,
PS_JOBCODE_TBL EA,
      PS_PERSON PP3
WHERE E.EMPLID    = &B.EmplID
  AND E.EMPLID    = PP3.EMPLID
  AND E.JOBCODE = EA.JOBCODE
  AND E.PER_ORG = 'EMP'
  AND E.EMPL_RCD = &B.EMPL_RCD
  AND (E.EFFDT < &B.EffDt OR E.EFFDT = &B.EffDt AND E.EFFSEQ < &B.EffSeq)
ORDER BY E.EFFDT DESC, E.EFFSEQ DESC

end-SELECT
end-procedure

begin-procedure Insert-XferOut


let $sql-statement = 'MP2_JOB.SQR,Insert-XferOut,Insert,PS_J_ACTION_COUNTS'

display 'Insert-XferOut-&E.BUSINESS_UNIT: ' noline
display &E.BUSINESS_UNIT

begin-SQL On-Error=SQL-Error

INSERT INTO PS_J_ACTION_COUNTS(JOBCODE,JOBCODE_DESCR,BUSINESS_UNIT,BEGINS,HIRES,RETIRES,XFERSIN,XFERSOUT,TERMINATIONS,ENDS) VALUES(&E.JOBCODE,&EA.DESCR,&E.BUSINESS_UNIT,0,0,0,0,1,0,0)

end-SQL

Answer 1

Execute first part of your query and check whether EA.DESCR is null or not.

I think EA.DESCR is returning null value which you are trying to insert in JOBCODE_DESCR column. In that case you need to have a value other than null or you need to allow null in JOBCODE_DESCR column.

Answer 2

First, I would confirm records do not have null values in your PS_JOBCODE_TBL table. (Unless you have customized this table, the field is typically defined as "required" in AppDesigner, so it should not have null values.)

SELECT *
FROM PS_JOBCODE_TBL
WHERE DESCR IS NULL

If you have null values on the source table, but still want to insert records into the PS_J_ACTION_COUNTS table, you can wrap the field in the NVL function and provide a default value (i.e. a space) to use in cases of null values.

begin-SELECT Loops=1
E.BUSINESS_UNIT
NVL(E.JOBCODE, ' ') &EA.DESCR
EA.DESCR
E.EFFDT