RE: SQL Strangeness -- MIDRANGE-L

No

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx

-----Gary Thompson <gthompson@xxxxxxxxxxx> wrote: -----
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
From: Gary Thompson <gthompson@xxxxxxxxxxx>
Date: 01/07/2014 05:28PM
Subject: RE: SQL Strangeness

I was focused on just that . . .
Any way there are alternate versions of these
files where added columns don't exist ?

-----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 3:03 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Strangeness

The embedded RPG worked until I added the last line of columns to each block. So yes, I have successfully accessed both tables from embedded sql.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx

-----Gary Thompson <gthompson@xxxxxxxxxxx> wrote: -----
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
From: Gary Thompson <gthompson@xxxxxxxxxxx>
Date: 01/07/2014 04:57PM
Subject: RE: SQL Strangeness

Silly question; have you done something like:
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, 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
--
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.
--
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.