×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL PreCompile Fails

SQL PreCompile Fails

SQL PreCompile Fails

(OP)
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, :PGMNAM                       
                                                                   
 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.                                             

RE: SQL PreCompile Fails

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.  
 

RE: SQL PreCompile Fails


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close