Re: SQL Strangeness

I would start by looking at the cross reference section of the SQL
precompiler listing. OPTION(*XREF) is the default, so you should have it in
your listing just before the list of errors. Is the table name found and
listed there (indicating it was found and used for checking during the
precompile). Are *all* of the column names for the table listed, or just
the ones prior to where the error occurred? If they are all listed, it
means the precompiler was aware of all the column definitions and there
must be something odd in the source member that is causing SQL to not
recognize the statement correctly.

Hope this helps you narrow down the problem!

Sue Romano
IBM i SQL Development

This should be a very simple SQL, but it is not working out that way. Can

anyone see what I am not?

RPGLE Free Form, V7.1 TR6

Exec SQL
insert into saysp100 (
activ, cmpno, ordno, cusno, cuspo, shipv, trmcd, trmds,

otype,

oregn, osaln, "PRIOR", invno, shpno, frght, c1pmo, corcd,

line#,

prdno, quano, listp, actsp, ovrsp, unwgt, unitm, class,

cstsp,

c2via, c2dwh, seqno, shipd, sellp, house, c3inv, c3tc1,

c3tc2,

sperd, blrgn, blsls, pline, shptk, tfrgt, fob, coodt,

ohrdt,

oscdt, cordt, c2sdt, s30dt, c1qfl, c1car, c2qfl,
osnam, osad1, osad2, osadx, osad3, ossta, oszip, tscst)
select
activ, cmpno, ordno, cusno, cuspo, shipv, trmcd, trmds,

otype,

oregn, osaln, "PRIOR", invno, shpno, frght, c1pmo, corcd,

line#,

prdno, quano, listp, actsp, ovrsp, unwgt, unitm, class,

cstsp,

c2via, c2dwh, seqno, shipd, sellp, house, c3inv, c3tc1,

c3tc2,

sperd, blrgn, blsls, pline, shptk, tfrgt, fob, coodt,

ohrdt,

oscdt, cordt, c2sdt, s30dt, c1qfl, c1car, c2qfl,
osnam, osad1, osad2, osadx, osad3, ossta, oszip, tscst
from saysv100
where s30dt <= Current_Date - 1 day
and not exists (
select * from saysp100 where invno = saysv100.invno
);

Error is SQL1105: Position 63 Object TSCST in *LIBL not found.

Error on line directly above from saysv100.

Strange thing is if I remove the tscst field from that line, the error

shifts to oszip, and so on until the line is gone, and then >it gives me:

SQL0117: Statement contains wrong number of values.

I just don't see it... In order to see what is being compiled into the

program I looked at the PRTSQLINFO output, and the statement >looks like
this:

INSERT INTO SAYSP100 ( ACTIV , CMPNO , ORDNO , CUSNO , CUSPO , SHIPV ,

TRMCD ,

TRMDS , OTYPE , OREGN , OSALN , "PRIOR" , INVNO , SHPNO , FRGHT ,

C1PMO , CORCD

, LINE# , PRDNO , QUANO , LISTP , ACTSP , OVRSP , UNWGT , UNITM ,

CLASS , CSTSP

, C2VIA , C2DWH , SEQNO , SHIPD , SELLP , HOUSE , C3INV , C3TC1 ,

C3TC2 , SPERD

, BLRGN , BLSLS , PLINE , SHPTK , TFRGT , FOB , COODT , OHRDT ,

OSCDT , CORDT ,

C2SDT , S30DT , C1QFL , C1CAR , C2QFL , OSNAM , OSAD1 , OSAD2 ,

OSADX , OSAD3 ,

OSSTA , OSZIP , TSCST ) SELECT ACTIV , CMPNO , ORDNO , CUSNO , CUSPO ,

SHIPV ,

TRMCD , TRMDS , OTYPE , OREGN , OSALN , "PRIOR" , INVNO , SHPNO ,

FRGHT , C1PMO

, CORCD , LINE# , PRDNO , QUANO , LISTP , ACTSP , OVRSP , UNWGT ,

UNITM , CLASS

, CSTSP , C2VIA , C2DWH , SEQNO , SHIPD , SELLP , HOUSE , C3INV ,

C3TC1 , C3TC2

, SPERD , BLRGN , BLSLS , PLINE , SHPTK , TFRGT , FOB , COODT ,

OHRDT , OSCDT ,

CORDT , C2SDT , S30DT , C1QFL , C1CAR , C2QFL , OSNAM , OSAD1 ,

OSAD2 , OSADX ,

OSAD3 , OSSTA , OSZIP , TSCST FROM SAYSV100 WHERE S30DT <=

CURRENT_DATE - 1 DAY

AND NOT EXISTS ( SELECT * FROM SAYSP100 WHERE INVNO = SAYSV100 .

INVNO )

Once again, this does not look bad to my eyes, but when I run the program,

I get:

Message ID . . . . . . : SQL0206 Severity . . . . . . . : 30

Message type . . . . . : Diagnostic

Date sent . . . . . . : 01/07/14 Time sent . . . . . . :

16:04:35

Message . . . . : Column or global variable TSCST not found.

Cause . . . . . : TSCST was not found as a column of table *N in *N and

was

not found as a global variable in *N. If the table is *N, TSCST is not a

column of any table or view that can be referenced.

Recovery . . . : Do one of the following and try the request again:

-- Ensure that the column and table names are specified correctly in

the

statement.

-- If this is a SELECT statement, ensure that all the required tables

were

named in the FROM clause.

-- If the column was intended to be a correlated reference, qualify

the

column with the correct table designator.

-- If the column was intended to be a global variable, qualify the

name

with the schema where the global variable exists or ensure the schema is

in

the path.

This makes no sense. If I remove columns osnam through tscst in both the

insert column list, and the select column list, all is >well, and the
program runs properly. If I cut and paste into green screen SQL, it works
as expected.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx