Re: SQL Strangeness

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Does this give the same error?

select
           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
           );

If it's not funky characters...I'd be thinking along Gary's lines of there
being another file without the last set of columns...

Charles



On Tue, Jan 7, 2014 at 5:07 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

> Hi Mark,
>
> I've seen some strangeness with copy/paste of SQL from green screen or
> iNav into RDi...
>
> When you remove osname through tscst then add them back, to you copy and
> paste again or type them out?
>
> If copy and paste, try typing them out.
>
> Charles
>
>
> On Tue, Jan 7, 2014 at 4:57 PM, Gary Thompson <gthompson@xxxxxxxxxxx>wrote:
>
> > 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.