Column not found/specified (7520) when column is valid

itsAK

New Member
Hi all,

Im running a query and Im getting the following error

Column not found/specified (7520) - However all columns are valid??

I know that 99% of the time this error would be due to having the wrong table/field name down but I have checked and checked my code and all table and field names are correct.

As anyone ever come accross this issue before? Can it be fixed?

Iv already looked at KB P 26725 and a few others but that solution did not work.

Is this quite common?

Im running off PROGRESS 9.1d06 and Im connecting via (ODBC) MERANT 3.60 32-BIT driver
 

Casper

ProgressTalk.com Moderator
Staff member
Hi Itsak,

Maybe the obvious but is there no dash ("-") in the field/table name?
If so the you have to enclose the field/table name in quotes....

e.g.
Code:
select * from PUB."Table-with-dash"

HTH,

Casper.
 

itsAK

New Member
Hi Casper,

No there is no dash in the table name.

The tables are as follows:

PUB.tp_customer.cust_ID

I have ran scripts with my tables like above I have also ran them s follows

"PUB"."tp_customer"."cust_ID"

Both running without error.

Any other thoughts?
thanks
 

itsAK

New Member
Oh yes.... might help ;)

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM (PUB.Table_One Table_One
INNER JOIN PUB.Table_Three Table_Three
ON (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
LEFT OUTER JOIN PUB.Table_Two Table_Two
ON ((Table_One.Field_G=Table_Two.Field_G)
AND (Table_Three.Field_K=Table_Two.Field_K))
AND (Table_Three.Field_L=Table_Two.Field_L)
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
UNION ALL
SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, null, null
FROM PUB.Table_One Table_One
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
AND NOT EXISTS
(SELECT * FROM PUB.Table_Three Table_Three
WHERE (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
 

Casper

ProgressTalk.com Moderator
Staff member
Could you narrow it down to a specific field?
(Break the query up in small parts and find which statement is responsible for the error).

Casper.
 

itsAK

New Member
Sorry this is it
(missing bracket in the last one!)

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM (PUB.Table_One Table_One
INNER JOIN PUB.Table_Three Table_Three
ON (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
LEFT OUTER JOIN PUB.Table_Two Table_Two
ON ((Table_One.Field_G=Table_Two.Field_G)
AND (Table_Three.Field_K=Table_Two.Field_K)
AND (Table_Three.Field_L=Table_Two.Field_L))
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
UNION ALL
SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, null, null
FROM PUB.Table_One Table_One
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
AND NOT EXISTS
(SELECT * FROM PUB.Table_Three Table_Three
WHERE (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))

And Im now getting a syntax error........
 

itsAK

New Member
Hi Casper,

Yes I had already split the code up.....

Im getting the error for the second part of the query:

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, null, null
FROM PUB.Table_One Table_One
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
AND NOT EXISTS
(SELECT * FROM PUB.Table_Three Table_Three
WHERE (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
 

itsAK

New Member
Hi I have re-written my code to make it more simple to get rid of the damn syntax error! however still no joy!!.....

I need help Progress guys!!..... can u see anything wrong that Im doing??

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM (PUB.Table_One Table_One
LEFT OUTER JOIN
(PUB.Table_Three Table_Three
INNER JOIN PUB.Table_Two Table_Two
ON (Table_Three.Field_K=Table_Two.Field_K)
AND (Table_Three.Field_L=Table_Two.Field_L))
ON (Table_One.Field_B=Table_Three.Field_B)
AND (Table_One.Field_G=Table_Three.Field_G)
AND ((Table_One.Field_G=Table_Two.Field_G))
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
 

roopeshperla

New Member
Hi Guys, Even i am getting the same problem while accessing the progress db 9.1E. Is there any solution that is found for this.

Thanks
-Roopesh
 

BCM

Member
First, this query is everyday faire for SQL Server, Oracle , DB2, mySQL, etc., but it is going to be a grind for Progress.

I believe the problem is:
...AND NOT EXISTS (SELECT * FROM ...

The more typical way to do this is:
...AND NOT EXISTS (SELECT 1 as COL1 FROM ...

or

...AND NOT EXISTS (SELECT NULL AS COL1 FROM ...

The test for existence will be satisfied by the return of a single byte. Also, Progress, with all its quirks, may want the returned column to have a name. That is why my examples read "AS COL1".
 

MattRoy

New Member
I know it's an old thread, but I had the exact same problem/error with same environnement (Progress 9.1D and ODBC MERANT 3.60 32-BIT driver).
I found the reason and I think it could be useful to someone else.
If I put an alias to Field_A into Table_One to be renamed as Field_B, it's OK. But as soon as I join Table_Two which contains a column named Field_B (same name as my alias), I get the error.
Progress (or MERANT?) don't like it when you have an alias named like another available column in other tables joined.

Give the error:
SELECT Table_One.Field_A AS Field_B
FROM PUB.Table_One
INNER JOIN PUB.Table_Two ON Table_Two.Field_B = Table_One.Field_A

To correct it, I can just change my alias.
SELECT Table_One.Field_A AS Field_B_2
FROM PUB.Table_One
INNER JOIN PUB.Table_Two ON Table_Two.Field_B = Table_One.Field_A
 
Top