Nested SQL Queries

[bugg_tb]

Hi Guys, this is my first foray into the SQL side of progress, we're running 9.1c on an AIX box, I connect it using the merrant ODBC drivers and I'm trying to write a nested query that shows our sales figures for the month. I've crafted this....

Select *
FROM[
Select PUB.D2_MOVE.trad_code, PUB.D2_MOVE.mv_value, PUB.D2_MOVE.mv_cost,
PUB.D2_MOVE.mv_type
From PUB.D2_MOVE
GROUP BY PUB.D2_MOVE.trad_code, PUB.D2_MOVE.mv_value,
PUB.D2_MOVE.mv_cost, PUB.D2_MOVE.mv_type, PUB.D2_MOVE.mv_date
HAVING (PUB.D2_MOVE.mv_date Between '10/1/2007' And '10/31/2007') AND
(PUB.D2_MOVE.mv_type ='002')
ORDER BY PUB.D2_MOVE.mv_date] AS test

But no matter what I put on the outside select statement(it wont end up select * thats just to try and get it to work) it just tells me there is a syntax error, but I can't work out where it is, the inner query works fine. I've tried [] brackets, () brackets, alias, no alias etc etc but I can't work out what its complaining about.

If anyone can show me how this should be written that would be great,

Cheers

Tom

 

[Russell1]

I was having a similar issue on a select from statement while using an ODBC tool to access our progress database. It seems that Progress (or at least the ODBC I have) doesn't like selecting strictly from an expression. By selecting a field from a table, where the field was within the inner statements and by removing the "order by" portion of the inner select statement that it would work. I'm not sure why it doesn't like the order by clause.

Example, in my case, this didn't work:

SELECT * from  (SELECT OrderNum FROM PUB.ORDERHED)

and this didn't work:

SELECT   O1.OrderNum
FROM     PUB.OrderDtl O1 
WHERE    O1.OrderNum in (SELECT OrderNum FROM PUB.ORDERHED ORDER BY OrderNum desc)

but this did work:

SELECT   O1.OrderNum
FROM     PUB.OrderDtl O1 
WHERE    O1.OrderNum in (SELECT OrderNum FROM PUB.ORDERHED)