× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. January 2014

RE: SQL Strangeness

Hmm, if I've been following, your "strange SQL"
runs in STRSQL yet produces "object not found"
error in SQLRPGLE.

And, you've looked at the various ways SQL
gets "damaged" in the source mbr: characters
past col 80, characters less than hex 40, etc.

And you've confirmed no other versions of the
file exist on your server and it's not an SQL
naming convention issue.

I'm stumped.

To re-start, I would create a trivial SQL statement
in a test-debug routine and specify library/file with
the goal of getting something working.

Baby steps from there.

Like a friend and fellow programmer once said:

"It's either something really simple,
or something really strange and complex."

My friend favored really simple.

I hope this helps.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark Murphy/STAR BASE Consulting Inc.
Sent: Wednesday, January 08, 2014 8:21 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Strangeness

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.

--
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 thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.