|
Silly question; have you done something like:otype,
select * from saysp100;
and
select * from sysv100;
I have fumbled with detail SQL errors caused by basic or "foundation"
errors (incorrect naming convention, library list errors).
I guess you need to do this within your RPGLE because "green screen
SQL" (STRSQL) works ?
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark Murphy/STAR BASE
Consulting Inc.
Sent: Tuesday, January 07, 2014 2:14 PM
To: Midrange Systems Technical Discussion
Subject: SQL Strangeness
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,
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,otype,
osnam, osad1, osad2, osadx, osad3, ossta, oszip, tscst)
select
activ, cmpno, ordno, cusno, cuspo, shipv, trmcd, trmds,
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,the
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
column with the correct table designator.name
-- If the column was intended to be a global variable, qualify the
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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
