Re: getting error on this view

I hope you didn't put the AND at the very end!!

In STRSQL you can move the cursor over the message and press F1 to get second-level text - more information.

What you put in this post is flawed, I think - 3rd line ends with OEIND - nothing after it, so something is missing, right?

But after you check the help on the message, you'll want to take the text at face value - somewhere in the statement is the OEINDLID that the parser does not know about - it might be due to the rename to T01, it might be due to being in a nested situation. You had all those parentheses around the various parts of the JOINs - I don't know what release of the OS you are on, but those parentheses take on more importance as to order in more current releases.

Here is a link on how to syntax-check a statement using RUNSQLSTM - it should work with a CREATE TABLE.

There are also online SQL syntax checkers. I found one at http://www.dpriver.com/pp/sqlformat.htm and put your original statement into it and asked it to be formatted - it reported a problem in line 25, position 1 - that's where I saw you needed an AND.

I put the AND there and formatted again - it went fine.

HTH
Vern

On 9/15/2013 9:28 AM, Hoteltravelfundotcom wrote:

I added the AND at the end but it gives this message

Column qualifier or table OEINDLID undefined.
CREATE VIEW astccdta.acsusage
AS
SELECT ICPRTMIA.IARCC9, OEINDLID.IDDOCD, OEIND
ADRESSAD.ADSFX#, OEINDLID.IDPRT#, OEINDLID.IDG

cursor is on CREATE what does this message mean?


On Sun, Sep 15, 2013 at 10:16 AM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

yes will do, I only use strsql, actually want to know if you can enter
these statements in Navigator?


On Sun, Sep 15, 2013 at 10:10 AM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx

wrote:
You are missing an AND near the end, in the final WHERE clause.

Try running this in STRSQL - it should put the cursor on the position
where it thinks the error is. That is, on WHICH OEINDLID might be the
problem.

HTH
Vern

On 9/15/2013 8:51 AM, Hoteltravelfundotcom wrote:

I am getting this error: " Column qualifier or table OEINDLID
undefined. " can u see what is wrong here? I am new to VIEWS i have
done only 1 view previously.


CREATE VIEW astccdta.acsusage
AS
SELECT ICPRTMIA.IARCC9, OEINDLID.IDDOCD, OEINDLID.IDCOM#,
ADRESSAD.ADSFX#, OEINDLID.IDPRT#, OEINDLID.IDGRC#, OEINDLID.IDENT#,
OEINDLID.IDSFX#, OEINDLID.IDPRLC, OEINDLID.IDNTU$, OEINDLID.IDSHP#,
ADRESSAD.ADFNM, ADRESSAD.ADLNM, OEINHDIH.IHVIAC, ADRESSAD.ADSTTC,
OEINDLID.IDINV#, (
SELECT
SUM (T01.IDNTU$)
FROM
ASTDTA.OEINDLID T01
WHERE
T01.IDCOM# = '001' AND
T01.IDDOCD = OEINDLID.IDDOCD AND
T01.IDINV# = OEINDLID.IDINV# AND
T01.IDPRLC LIKE 'F%'

)
FROM (ASTDTA.OEINDLID
INNER JOIN ASTDTA.ICPRTMIA ON OEINDLID.IDPRT#=ICPRTMIA.IAPRT#)
INNER JOIN (ASTDTA.ADRESSAD
INNER JOIN ASTTRN.OEINHDIH ON ADRESSAD.ADENT#=OEINHDIH.IHENT#)
ON OEINDLID.IDIDC#=OEINHDIH.IHIDC#
WHERE ICPRTMIA.IARCC9='ACS'
OEINDLID.IDCOM#='001'
AND OEINDLID.IDPRT# LIKE 'ACS%'

--
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.