MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » January 2014

SQL Strangeness



fixed

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





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact