SQL PreCompile Fails

[OAKEJ]

EXEC SQL SET TRANSACTION ISOLATION LEVEL NO COMMIT
END-EXEC

EXEC SQL

INSERT INTO MELBRCOSP
(LBREGN, LBSITE, LBLEMP, LBLHRS, LBSHRS, LBLETM, LBLDAT,
LBCDAT,LBCRTD, LBCRTT, LBCRTU, LBCRTP)

SELECT CRCTAC, OLSITE, OLEMP, SUM(OLLHRS)/COUNT(DISTINCT TLRETM),
SUM(OLSHRS)/COUNT(DISTINCT TLRETM),
SUM(DISTINCT TLRETM),OLDATE,TLRTIM,
:MDY_DATE2,HHMM, :USRNAM, GMNAM

FROM FLOL LEFT JOIN FLEM ON OLEMP = EMCODE
LEFT JOIN CKTIMHIM ON EMSSNO = TLEMPN
LEFT JOIN FLCR ON OLSITE = CRSITE

WHERE OLDATE BETWEEN SCYMD AND ECYMD
AND TRIM(TLPC01) <> 'HP' AND TRIM(TLPC01) <> 'MLEAD'
AND TRIM(TLPC01) <> 'SK' AND TRIM(TLPC01) <> 'VA'
AND TRIM(TLPC01) <> 'FHP'
AND TLRTIM BETWEEN START_KRONOS AND END_KRONOS


GROUP BY CRCTAC, OLSITE, OLEMP, OLDATE

ORDER BY CRCTAC, OLSITE, OLEMP, OLDATE
For Some Reason the SQL precompile fails stating:

Position 45 Column TLRTIM or expression in SELECT list
not valid.

 

[mikrom]

In your select you mix table fields with host variables. (Host variables are those beginning with ':')

A) If your select should return more rows, you should:
1. Create cursor for the select statement
2. Open cursor
3. Fetch one or more resulting rows
4. Close Cursor

B) If your select returns only one row you should use
SELECT INTO e.g

EXEC SQL
SELECT
MIN(BONUSDAUER), MAX(BONUSDAUER)
INTO
:ZW-BONUSDAUERMIN, :ZW-BONUSDAUERMAX
FROM P400310V
END-EXEC

Here MIN(BONUSDAUER) and MAX(BONUSDAUER) are Minimum and Maximum of the column BONUSDAUER of the table P400310V and these values are selected into host variables
ZW-BONUSDAUERMIN and ZW-BONUSDAUERMAX.
However the host variabless must be defined compatible to table fields.

 

[botatun]

TLRTim is missing on group by list
You have:
GROUP BY CRCTAC, OLSITE, OLEMP, OLDATE
must be:
GROUP BY CRCTAC, OLSITE, OLEMP, OLDATE, TlRtim