How to handle values in statement

[64waves]

Good morning, building sql in my program to collect data for a subfile.

It is in a procedure, I get a compile error on the 'not in ( )' statement. here is main code:

----------------------------------------------------------------------------------------------

Exec SQL
create table qtemp/ScrDetails (
PORT dec (5,0) NOT NULL WITH DEFAULT,
PAYTYP CHAR ( 1) ccsid 37 NOT NULL WITH DEFAULT,
COMPCD CHAR ( 3) ccsid 37 NOT NULL WITH DEFAULT,
DIVSN CHAR ( 1) ccsid 37 NOT NULL WITH DEFAULT,
JOBDS CHAR ( 4) ccsid 37 NOT NULL WITH DEFAULT,
FIRST CHAR (10) ccsid 37 NOT NULL WITH DEFAULT,
LAST CHAR (15) ccsid 37 NOT NULL WITH DEFAULT,
WSTAT CHAR ( 1) ccsid 37 NOT NULL WITH DEFAULT,
ESTAT CHAR ( 1) ccsid 37 NOT NULL WITH DEFAULT);

// Read through USRDEPT file to get all the COMP/DIV/DEPT combinations
// this user oversees

Setll (Ldausrid)USRDEPT;

Dou %EOF(USRDEPT);
Reade (Ldausrid)USRDEPT;
If %Found(USRDEPT) and DSTSCD <> 'D';


Exec Sql
insert into qtemp/ScrDetails
select MPORT,MPAYTP,MCOCDE,MDIVSN,MJOBDS,FNAME,MNAME,
WRKSTS from PRLIBRF/PRMSTR where MCOCDE = COMCD and
MDIVSN = DIVSN and MJOBDS = DEPT and MSTAT not
in('T','L','Y','Z','D','X') order by MCOCDE,MDIVSN,MJOBDS;




Enddo;

-------------------------------------------------------------------------------------------------
Compile error is: SQL1103 10 532 Position 27 Column definitions for table SCRDETAILS in
QTEMP not found.

I do have a file created in QTEMP and in the main library of the program at compile time.
Any help would be appreciated. Thank you.

[jj_dahlheimer]

If it is only a severity 10 then you shouldn't have to worry, all that error is saying is that it can't find scrdetails which unless you created it before compiling you will get this error. I just ignore these. Alternatively if you run your create statement in the session you are compiling the program in this error will go away.

[64waves]

Hello, I did create my file through a command line SQL statement, made sure it was there in QTEMP, and tried to compile again. Still no compile. It is error 10 but there is no program object. I noticed in my compile log that when I try to do a real compile from DDS source the file object is created, then deleted in the same job.

Am I missing something else? Don't know what to make of it.

Thanks.

[jj_dahlheimer]

You are missing an endif, Also looks like you are only inserting 8 values even though your temp table has nine, this will fail unless you tell it what columns you are trying to populate.

[64waves]

Ok, checked all my fields, found one misnamed by one letter, fixed that, had file in QTEMP, added the ENDIF, still no good. Here is all my info:

Exec SQL drop table qtemp/ScrDetails;

Exec SQL
create table qtemp/ScrDetails (
PORT dec (5,0) NOT NULL WITH DEFAULT,
PAYTYP CHAR ( 1) NOT NULL WITH DEFAULT,
COMPCD CHAR ( 3) NOT NULL WITH DEFAULT,
DIVSN CHAR ( 1) NOT NULL WITH DEFAULT,
JOBDS CHAR ( 4) NOT NULL WITH DEFAULT,
FIRST CHAR (10) NOT NULL WITH DEFAULT,
LAST CHAR (15) NOT NULL WITH DEFAULT,
WSTAT CHAR ( 1) NOT NULL WITH DEFAULT,
ESTAT CHAR ( 1) NOT NULL WITH DEFAULT);

// Read through USRDEPT file to get all the COMP/DIV/DEPT combinations
// this user oversees

Setll (Ldausrid)USRDEPT;

Dou %EOF(USRDEPT);
Reade (Ldausrid)USRDEPT;
If %Found(USRDEPT) and DSTSCD <> 'D';


Exec Sql
insert into qtemp/ScrDetails
select MPORT,MPAYTP,MCOCDE,MDIVSN,MJOBDS,FNAME,LNAME,
WRKSTS,MSTAT from PRLIBRF/PRMSTR where MCOCDE = COMCD and
MDIVSN = DIVSN and MJOBDS = DEPT
order by MCOCDE,MDIVSN,MJOBDS;


Endif;
Enddo;

return;

/End-free
p CrtScrData E
************************************************** ***************

Created file on SQL command line:

create table qtemp/ScrDetails (
PORT dec (5,0) NOT NULL WITH DEFAULT,
PAYTYP CHAR ( 1) NOT NULL WITH DEFAULT,
COMPCD CHAR ( 3) NOT NULL WITH DEFAULT,
DIVSN CHAR ( 1) NOT NULL WITH DEFAULT,
JOBDS CHAR ( 4) NOT NULL WITH DEFAULT,
FIRST CHAR (10) NOT NULL WITH DEFAULT,
LAST CHAR (15) NOT NULL WITH DEFAULT,
WSTAT CHAR ( 1) NOT NULL WITH DEFAULT,
ESTAT CHAR ( 1) NOT NULL WITH DEFAULT)


Compile results:

Compiler options..........DBGVIEW(*all) DFTACTGRP(*no) ACTGRP(*new)
Source member changed on 05/05/16 13:15:15
5770ST1 V7R1M0 100416 Create SQL ILE RPG Object EC00045
126 COMPILEOPT = 'DBGVIEW(*all) DFTACTGRP(*no) ACTGRP(*new)';
508 Exec SQL drop table qtemp/ScrDetails;
537 order by MCOCDE,MDIVSN,MJOBDS;
5770ST1 V7R1M0 100416 Create SQL ILE RPG Object EC00045
DIAGNOSTIC MESSAGES
MSG ID SEV RECORD TEXT
SQL5066 0 125 Precompile option COMMIT changed by SET OPTION statement.
SQL5066 0 125 Precompile option COMPILEOPT changed by SET OPTION
statement.
SQL5066 0 125 Precompile option COMMIT changed by SET OPTION statement.
SQL5066 0 125 Precompile option COMPILEOPT changed by SET OPTION
statement.
SQL1103 10 508 Position 37 Column definitions for table SCRDETAILS in
QTEMP not found.
SQL1103 10 533 Position 27 Column definitions for table SCRDETAILS in
QTEMP not found.
Message Summary
Total Info Warning Error Severe Terminal
6 4 2 0 0 0
10 level severity errors found in source
563 Source records processed



Thanks.

[64waves]

Hello, for some reason when I try to compile to QTEMP from source the object gets deleted right after it's created:
DATE(*SYSVAL) SWS(00000000) MSGQ(QUSRSYS/SHARTEST) CCSID(37)
SRTSEQ(*N/*HEX) LANGID(ENU) CNTRYID(US) JOBMSGQMX(64) JOBMSGQFL(*WRAP)
ALWMLTTHD(*NO) SPLFACN(*KEEP) ACGCDE().
*NONE Request 05/05/16 13:25:26.304237 QWTSCSBJ *N QCMD QSYS 0195
Message . . . . : - CRTPF FILE(QTEMP/SCRDETAILS) SRCFILE(ECOS/QDATA)
SRCMBR(SCRDETAILS) OPTION(*SOURCE)
CPC7301 Completion 00 05/05/16 13:25:26.326564 QDDCPF QSYS 056D QCMD QSYS 01C8
Message . . . . : File SCRDETAILS created in library QTEMP.
CPC7305 Completion 00 05/05/16 13:25:26.350331 QDDCPFM QSYS 005B QCMD QSYS 01C8
Message . . . . : Member SCRDETAILS added to file SCRDETAILS in QTEMP.
CPC2191 Completion 00 05/05/16 13:25:26.360619 QLIDLFIL QSYS 006F QLICLLIB QSYS 05DC
Message . . . . : Object SCRDETAILS in QTEMP type *FILE deleted.
CPF1164 Completion 00 05/05/16 13:25:26.360730 QWTMCEOJ QSYS 014A *EXT *N
Message . . . . : Job 232710/SHARTEST/SCRDETAILS ended on 05/05/16 at
13:25:26; .011 seconds used; end code 0 .
Cause . . . . . : Job 232710/SHARTEST/SCRDETAILS completed on 05/05/16 at
13:25:26 after it used .011 seconds processing unit time. The job had

Is there a setting I need to change on my profile or JOBD for this?

Thanks.

[jj_dahlheimer]

I am getting a little confused here but severity 10 will not cause the object not to get created, if the object is not created you have something else going on. Are you checking the regular compile listing, not the pre compiler listing. It comes right after the pre compile listing, and my guess is there is something there as to why your object is not getting created.

I am confused when you say trying to created it from a real dds compile, are you just putting your sql into a source file and running the CRTPF command on it. Never tried it but I can't imagine that would work, it would be looking for dds commands not sql.

[64waves]

Ok, I was looking for the regular output on the compile but I recall it doesn't look for logic errors until it gets past the SQL errors. I was creating a file on the fly on a STRSQL command session. When I try to compile from actual DDS source of the same field definitions, the object is getting deleted in the middle of the compile. I included that detail in my last post. I think I need the DDS compiled source, but don't know why it is deleting it. At a new job so there may be a setting on my profile or something that is causing the delete.

[64waves]

Hello, I commented all my sql code and did a compile, I looked at the output and somehow it was seeing 2 instances of *DBGVIEW. I could only find one but was doing a /copy on a couple of small pieces of code. I took out that line, reset all my sql code and it got past it with no sql error. I continued on with fixing compile errors and got it compiled. Now I have to see if it works as designed.

Sorry for the frustration and thank you for the input.

[DeadManWalks]

on your CRTSQL...check the value of GENLVL
its help
Severity level (GENLVL) - Help

Specifies whether the compiler is called, depending on the
severity of messages generated as a result of errors found
by the SQL precompiler. If precompiler errors are
generated that have a message severity level greater than
the value specified for this parameter, the compiler is
not called.

As a side note, your insert statement should list the fields its going to insert. I understand for a qtemp not so important but its a good practice.
What is not good practice is hard coding the library name. Even if you are on a separate box for QA testing.

[Brian Rusch]

Are you compiling in batch? If so, then your program will not get created in your interactive job's QTEMP library since each batch job has their own version of QTEMP. Try compiling interactively.