Invalid column name error 1

[MikeAuz1979]

Hi,

I'm using an Access 97 pass through query, connecting to an oracle 8 db and I'm getting the error Invalid column name.

Can anyone take a look at my code and let me know where I'm going wrong? (I have a feeling it's something major rather than a comma out of place.)

Background is that the SITE and ACTIVE_SITE tables have single records but I have to select the most recent records in SUBCONTRACT and MDQ_BOOKING_SUMMARY. (Hence all the complex sub-queries.)

Thanks for any help

Code Start----

SELECT SITE.SITE_NAME, ACTIVE_SITE.SITE_NUMBER, MDQ3.BOOKED_GJ_BASE

FROM CCDR.ACTIVE_SITE ACTIVE_SITE, CCDR.SITE SITE,

(SELECT SUBCONTRACT.SUBCONTRACT_NUMBER, SUBCONTRACT.ACCOUNT_NUMBER
FROM CCDR.SUBCONTRACT SUBCONTRACT,

(SELECT SUBCONTRACT.ACCOUNT_NUMBER, Max(SUBCONTRACT.SUBCONTRACT_EXPIRY_DATE) AS SUBCONTRACT_EXPIRY_DATE
FROM CCDR.SUBCONTRACT SUBCONTRACT
GROUP BY SUBCONTRACT.ACCOUNT_NUMBER
)Sub1

Where Sub1.SUBCONTRACT_EXPIRY_DATE = SUBCONTRACT.SUBCONTRACT_EXPIRY_DATE
AND Sub1.ACCOUNT_NUMBER = SUBCONTRACT.ACCOUNT_NUMBER
)Sub2,

(SELECT MDQ_BOOKING_SUMMARY.BOOKED_GJ_BASE,SITE.SITE_NUMBER
From CCDR.MDQ_BOOKING_SUMMARY MDQ_BOOKING_SUMMARY,CCDR.SUBCONTRACT SUBCONTRACT, CCDR.ACTIVE_SITE ACTIVE_SITE, CCDR.SITE SITE,

(SELECT MDQ_BOOKING_SUMMARY.SUBCONTRACT_NUMBER,Max(MDQ_BOOKING_SUMMARY.BOOKING_START_DATE) AS BOOKING_START_DATE

FROM CCDR.MDQ_BOOKING_SUMMARY MDQ_BOOKING_SUMMARY,
(
SELECT MDQ_BOOKING_SUMMARY.SUBCONTRACT_NUMBER, Max(MDQ_BOOKING_SUMMARY.BOOKING_EFFECTIVE_DATE) AS MaxOfBOOKING_EFFECTIVE_DATE
FROM CCDR.MDQ_BOOKING_SUMMARY MDQ_BOOKING_SUMMARY
GROUP BY MDQ_BOOKING_SUMMARY.SUBCONTRACT_NUMBER
)MDQ1

WHERE MDQ1.MaxOfBOOKING_EFFECTIVE_DATE = MDQ_BOOKING_SUMMARY.BOOKING_EFFECTIVE_DATE
AND MDQ1.SUBCONTRACT_NUMBER = MDQ_BOOKING_SUMMARY.SUBCONTRACT_NUMBER
GROUP BY MDQ_BOOKING_SUMMARY.SUBCONTRACT_NUMBER
)MDQ2,

(
SELECT MDQ_BOOKING_SUMMARY.SUBCONTRACT_NUMBER, Max(MDQ_BOOKING_SUMMARY.BOOKING_EFFECTIVE_DATE) AS MaxOfBOOKING_EFFECTIVE_DATE
FROM CCDR.MDQ_BOOKING_SUMMARY MDQ_BOOKING_SUMMARY
GROUP BY MDQ_BOOKING_SUMMARY.SUBCONTRACT_NUMBER
)MDQ1Again

Where MDQ2.BOOKING_START_DATE = MDQ_BOOKING_SUMMARY.BOOKING_START_DATE
AND MDQ2.SUBCONTRACT_NUMBER = MDQ_BOOKING_SUMMARY.SUBCONTRACT_NUMBER
AND MDQ1Again.MaxOfBOOKING_EFFECTIVE_DATE = MDQ_BOOKING_SUMMARY.BOOKING_EFFECTIVE_DATE
AND MDQ1Again.SUBCONTRACT_NUMBER = MDQ_BOOKING_SUMMARY.SUBCONTRACT_NUMBER
AND MDQ_BOOKING_SUMMARY.SUBCONTRACT_NUMBER = SUBCONTRACT.SUBCONTRACT_NUMBER
AND SUBCONTRACT.ACCOUNT_NUMBER = ACTIVE_SITE.ACCOUNT_NUMBER
AND ACTIVE_SITE.SITE_NUMBER = SITE.SITE_NUMBER
)MDQ3

WHERE ACTIVE_SITE.SITE_NUMBER = SITE.SITE_NUMBER
AND ACTIVE_SITE.ACCOUNT_NUMBER = Sub2.ACCOUNT_NUMBER
AND MDQ3.SUBCONTRACT_NUMBER = Sub2.SUBCONTRACT_NUMBER
AND ACTIVE_SITE.SITE_NUMBER = SITE.SITE_NUMBER

Code End-----

 

[Thargy]

Mike,

I'm not able to post a solution - it's a bit too early in the morning for that. However, I do have a couple of observations/questions.

Am I right in thinking that a pass-through query from Access 'hits' oracle as if it was native oracle SQL?

I've plugged your SQL into a syntax colouring editor (TOAD to be precise) and none of it is highlighted as key or reserved words, so I don't think you've got anything daft in your table and/or column names.

I found the sheer quantity of SQL intimidating, but have a couple of pointers.

First, you alias tables to themselves, which is unnecessary, and I think clutters up the SQL (bear in mind that I'm trying to understand this with zero knowledge of your system). I therefore suggest that you alias tersely, as below. In oracle, you don't need to use the keyword 'AS' when aliasing. Also, if you connect to the schema in question (in this case I presume it's called CCDR) then you don't need to explicitly state it every time.

This would reduce

(SELECT SUBCONTRACT.ACCOUNT_NUMBER, Max(SUBCONTRACT.SUBCONTRACT_EXPIRY_DATE) AS SUBCONTRACT_EXPIRY_DATE
FROM CCDR.SUBCONTRACT SUBCONTRACT
GROUP BY SUBCONTRACT.ACCOUNT_NUMBER
)Sub1

to

(SELECT s.ACCOUNT_NUMBER, MAX(s.SUBCONTRACT_EXPIRY_DATE)  SUBCONTRACT_EXPIRY_DATE
   FROM SUBCONTRACT s
  GROUP BY ACCOUNT_NUMBER) Sub1

The formatting's poor, due to the width of the screen, but you get the gist. In the above snippet, you select a max value, which guarantees that only a single value can be returned, so why bother with a 'group by' clause? Unless there are multiple account numbers with exactly the same date....

Note that in Oracle-land, by default, the date automatically includes time information. If you want to compare several dates on the same day, but with differing times, you have to use the TRUNC function, to 'chop off' the time part. Did you really mean to compare raw date and times, and not just dates above?

Could you post the DDL for the tables in question, some sample data, and a sample output, so we can do better than my poor first attempt to help.

Regards

Tharg

Grinding away at things Oracular

 

[Beilstwh]

One problem I see is that you don't have a comma after "sub1".

Bill
Oracle DBA/Developer
New York State, USA

 

[MikeAuz1979]

Thanks Tharg,

I've taken your advice and simplified the aliasing (Below)
and yes this query is native oracle sql.

No worries with datetimes as all these dates are simply dates.

One thing I can confirm is that when I pull out Sub2 and MDQ3 they both work fine.

Thanks Beilstwh but I don't think there should be a comma after Sub1 as it's a subquery of Sub2 rather than the main query.(Please correct me if I'm wrong!)

I don't know what a ddl is but here's a description of each table involved.

Active Site - Contains all currently active site numbers
Site_Number
Account_Number

Site - Contains details about sites
Site_Number
Site_Name

SubContract - Contains history of contracts for sites
(I'm just grabbing the most recent)
Account_Number
SubContract_Expiry_Date

MDQ_BOOKING_SUMMARY - Contains history of contractual booked usage. (Again I'm grabbing the most recent, which is convaluted due to the tables data)
SubContract_Number
Booking_Start_Date
Booking_Effective_Date
Booked_GJ_Base

Code Start----
SELECT s.SITE_NAME, a.SITE_NUMBER, MDQ3.BOOKED_GJ_BASE

FROM CCDR.ACTIVE_SITE a, CCDR.SITE s,

(SELECT s.SUBCONTRACT_NUMBER, s.ACCOUNT_NUMBER
FROM CCDR.SUBCONTRACT s,

(SELECT s.ACCOUNT_NUMBER, Max(s.SUBCONTRACT_EXPIRY_DATE) AS SUBCONTRACT_EXPIRY_DATE
FROM CCDR.SUBCONTRACT s
GROUP BY s.ACCOUNT_NUMBER
)Sub1

Where Sub1.SUBCONTRACT_EXPIRY_DATE = s.SUBCONTRACT_EXPIRY_DATE
AND Sub1.ACCOUNT_NUMBER = s.ACCOUNT_NUMBER
)Sub2,

(SELECT mdq.BOOKED_GJ_BASE, s.SITE_NUMBER
From CCDR.MDQ_BOOKING_SUMMARY mdq,CCDR.SUBCONTRACT s, CCDR.ACTIVE_SITE a, CCDR.SITE s,

(SELECT mdq.SUBCONTRACT_NUMBER,Max(mdq.BOOKING_START_DATE) AS BOOKING_START_DATE

FROM CCDR.MDQ_BOOKING_SUMMARY mdq,
(
SELECT mdq.SUBCONTRACT_NUMBER, Max(mdq.BOOKING_EFFECTIVE_DATE) AS MaxOfBOOKING_EFFECTIVE_DATE
FROM CCDR.MDQ_BOOKING_SUMMARY mdq
GROUP BY mdq.SUBCONTRACT_NUMBER
)MDQ1

WHERE MDQ1.MaxOfBOOKING_EFFECTIVE_DATE = mdq.BOOKING_EFFECTIVE_DATE
AND MDQ1.SUBCONTRACT_NUMBER = mdq.SUBCONTRACT_NUMBER
GROUP BY mdq.SUBCONTRACT_NUMBER
)MDQ2,

(
SELECT mdq.SUBCONTRACT_NUMBER, Max(mdq.BOOKING_EFFECTIVE_DATE) AS MaxOfBOOKING_EFFECTIVE_DATE
FROM CCDR.MDQ_BOOKING_SUMMARY mdq
GROUP BY mdq.SUBCONTRACT_NUMBER
)MDQ1Again

Where MDQ2.BOOKING_START_DATE = mdq.BOOKING_START_DATE
AND MDQ2.SUBCONTRACT_NUMBER = mdq.SUBCONTRACT_NUMBER
AND MDQ1Again.MaxOfBOOKING_EFFECTIVE_DATE = mdq.BOOKING_EFFECTIVE_DATE
AND MDQ1Again.SUBCONTRACT_NUMBER = mdq.SUBCONTRACT_NUMBER
AND mdq.SUBCONTRACT_NUMBER = s.SUBCONTRACT_NUMBER
AND s.ACCOUNT_NUMBER = a.ACCOUNT_NUMBER
AND a.SITE_NUMBER = s.SITE_NUMBER
)MDQ3

WHERE a.SITE_NUMBER = s.SITE_NUMBER
AND a.ACCOUNT_NUMBER = Sub2.ACCOUNT_NUMBER
AND MDQ3.SUBCONTRACT_NUMBER = Sub2.SUBCONTRACT_NUMBER
AND a.SITE_NUMBER = s.SITE_NUMBER

Code End----

 

[ddiamond]

Do you have access to sql*plus? If you run your query through sql*plus, it will tell you which field it thinks is invalid.

 

[MikeAuz1979]

Thanks ddiamond! I don't have sql plus (old version of oracle) but I did download a trial copy of an sql editor and it told me that the line 'AND MDQ3.SUBCONTRACT_NUMBER = Sub2.SUBCONTRACT_NUMBER' was the problem. When I looked at the select statement for MDQ3 I realised I wasn't selecting SUBCONTRACT_NUMBER.

Thanks for all your help!

p.s. I also didn't need the last line as it was a duplicate.

Mike