"Add Command" Fetch Out Of Sequence

[TopJack]

In Crystal 9 Reports I have used two "Command" SQL statements seperately and trying to link them back together but get an error message. The error is "ORA-01002: fetch out of sequence".

Each of the individual SQL statements are reading multiple Oracle tables. I don't think it would be possible to merge the two Commands together in one SQL code because the nature of the data retrieval.

The error message led me to force each of the Commands to be sorted specifically around how I was going to link them together, eg "ORDER BY 1, 2".

I suppose there is two questions here ..... 1) Can Crystal 9 cope with two Commands at the same time (through linking them directly)? and also 2) If it can, how can I ensure the fetch sequence is correct for the linkage.

To clarify what I'm doing, below is a simplified example trying to illustrate the my scenario :-

Command_1

SELECT Company,OrderNo
FROM Orders
UNION ALL
SELECT Company,OrderNo
FROM Orders_2
ORDER BY Company

Command_2

SELECT Company, Min(Price)
FROM Products
ORDER BY Company

In the Crystal Database Expert I link Command_1 to Command_2 by "Company".


This still creates a fetch sequence error ?

Any help would be appreciated. Thanks.

 

[lbass]

Try using one command something like:

SELECT Company,OrderNo,(select min(A.Price) from Products A
where A.Company = Products.Company)
FROM Orders inner join Products on
Orders.Customer ID = Products.Products ID
UNION ALL
SELECT Company,OrderNo,(select min(A.Price) from Products A
where A.Company = Products.Company)
FROM Orders_2 inner join Products on
Orders_2.Customer ID = Products.Products ID
ORDER BY 1

-LB

 

[TopJack]

Thanks. My SQL understanding is very limited. I didn't realise you can create a sub-SQL inside the main-SQL.

Thanks again.