Syntax error when left joining tables

[mluken]

In the following SQL, I have 2 tables (TRACTOR_TABLE and SOFTWARE_TABLE) that I am doing a LEFT JOIN on. If I remove the "GLOBAL_TABLE" from the end of the query, it works fine. However, I need to join the entire results of this query to another table, so that is why I need the lable at the end of the query. When I leave "GLOBAL_TABLE" in the query, I get an error that just says the syntax is correct. This confuses me though, because this query works:

SELECT
TRIP_MST_NO
FROM
(
SELECT
TRIP_MST_NO
FROM
TRIP_MASTER
) TESTING

That works, but it is the same principal as the query below which doesn't work when I leave the "GLOBAL_TABLE" label in. Can anybody please tell me why???


SELECT
TRIP_MST_NO,
TRACKING_POS_LOCATION
FROM
(
(
SELECT
TRIP_MST_NO,
TRIP_MST_TRACTOR,
TRIP_MST_STATUS,
TRIP_MST_ORIG_CITY,
TRIP_MST_ORIG_STATE
FROM
TRIP_MASTER AS ZZ
WHERE
TRIP_MST_STATUS IN ('B','D','A') AND
TRIP_MST_TRIP_LEG = (
SELECT
MAX(TRIP_MST_TRIP_LEG)
FROM
TRIP_MASTER
WHERE
TRIP_MST_NO = ZZ.TRIP_MST_NO
)
) TRACTOR_TABLE
LEFT JOIN
(
SELECT
QUAL_POS_UNIT AS TRACTOR_MAPPER,
MAX(QUAL_POS_ID) AS TRACKING_POS_ID,
QUAL_POS_LOCATION AS TRACKING_POS_LOCATION,
QUAL_POS_DATE_TIME AS TRACKING_POS_DATE_TIME
FROM
QUAL_POSITION
GROUP BY
QUAL_POS_UNIT, QUAL_POS_LOCATION, QUAL_POS_DATE_TIME
) SOFTWARE_TABLE
ON TRACTOR_TABLE.TRIP_MST_TRACTOR = SOFTWARE_TABLE.TRACTOR_MAPPER
) GLOBAL_TABLE





Thanks in advance!!!!!!!!!!!!

Mike

 

[VintageWine]

Hi Mike,

FROM
TRIP_MASTER
WHERE
TRIP_MST_NO = ZZ.TRIP_MST_NO

is invalid. You can only use field from tables at the immediate level in any partidular sub select.

With nested selects think in terms of "building up", and try to do so in as logical and as simple a way possible.

Also your overall wrapper of GLOBAL_TABLE is superflous, although I don't think it would stop it working.

Equally superfluous are
TRIP_MST_STATUS,
TRIP_MST_ORIG_CITY,
TRIP_MST_ORIG_STATE

Try this

SELECT
  Q2.TRIP_MST_NO,
  Q3.TRACKING_POS_LOCATION
FROM
(
  SELECT
    T2.TRIP_MST_NO,
    MAX(Q1.QUAL_POS_ID)AS QUAL_POS_ID
  FROM
  (
    SELECT
      T1.TRIP_MST_NO, 
      MAX(T1.TRIP_MST_TRIP_LEG) AS TRIP_MST_TRIP_LEG
    FROM
      TRIP_MASTER T1
    WHERE
      T1.TRIP_MST_STATUS IN ('B','D','A') 
    GROUP BY
      T1.TRIP_MST_NO
  ) T2
  JOIN TRIP_MASTER T3 ON 
    T3.TRIP_MST_NO=T2.TRIP_MST_NO AND
    T3.TRIP_MST_TRIP_LEG=T2.TRIP_MST_TRIP_LEG
  LEFT JOIN QUAL_POSITION Q1 ON 
    Q1.QUAL_POS_UNIT=T3.TRIP_MAST_TRACTOR
) Q2
LEFT JOIN QUAL_POSITION Q3 ON 
  Q3.QUAL_POS_ID=Q2.QUAL_POS_ID

Naturally I am unable to test the above

Have fun
Simon

 

[mluken]

The query that I listed is only part of the query. I am using the fields:

TRIP_MST_STATUS,
TRIP_MST_ORIG_CITY,
TRIP_MST_ORIG_STATE

I just didn't take the time to include them in the example here. I also removed the other part of the query that you said was superflous. The reason that I need the overall wrapper GLOBAL_TABLE is so that I can later join that to another table looking something like this:

SELECT SOME_FIELD FROM
{
// query from above
) GLOBAL_TABLE
INNER JOIN ANOTHER_TABLE ON ......

So anyway that is why I need that wrapper. In the initial query that I listed, if I remove the wrapper, the query I listed worked, but with it, I get a syntax error.

I haven't been able to test the code you gave me because the server appears to be down at the moment. But it doesn't appear as that the code you gave me has a wrapper on it either, so I would be left in the same position? I am really curious as to why GLOBAL_TABLE is causing a problem. I have done many joins in this manner before and this just has me stumped? Hmmm.....

 

[swampBoogie]

You can only use field from tables at the immediate level in any partidular sub select.

Not true.

 

[mluken]

I thought that I would simplify this a little. This works:


SELECT *
FROM
(
(
SELECT
TRIP_MST_NO,
TRIP_MST_TRACTOR,
TRIP_MST_STATUS,
TRIP_MST_ORIG_CITY,
TRIP_MST_ORIG_STATE
FROM
TRIP_MASTER AS ZZ
WHERE
TRIP_MST_STATUS IN ('B','D','A')
)
) GLOBAL_TABLE

but this gives me a syntax error:


SELECT *
FROM
(
(
SELECT
TRIP_MST_NO,
TRIP_MST_TRACTOR,
TRIP_MST_STATUS,
TRIP_MST_ORIG_CITY,
TRIP_MST_ORIG_STATE
FROM
TRIP_MASTER AS ZZ
WHERE
TRIP_MST_STATUS IN ('B','D','A')
) TRACTOR_TABLE
) GLOBAL_TABLE

why? I know you don't need the label in tihs example, but I will need it because I need to join it to another example and I will need it then...

 

[donutman]

The Global_table is missing it's select clause. Move the open ( further up the food chain.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

No, that's not it. I checked with some of my tables. I think the answer is that there is no other table. You have all the tables identified. Software_table and Tractor_table (from your 1st post) is all you need. The initial Select at the beginning is pulling the data from one or the other of the two tables.
The new joins you add must reference values from one or the other (or both) of those two tables already aliased.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]