Help With SQL Query

[Zigo]

Hi everyone, I am a noob with SQL and Oracle (ta-daaa) and I nedd a little help with a query...

In my scenario we have to get the oldest order number form a header table, then get some others info about that order in two other tables.

The problem is that we cannot use any stored procedure or PL/sql, just a single SQL statement.

 

Right now we use something like

 

SELECT *  FROM HTX JOIN DTX HTX.NO = DTX.NO LEFT JOIN CTX ON CTX.SOCTX_ORDER_NO = HTX.NO WHERE HTX.STATUS='N' AND HTX.NO IN (SELECT HTX.NO FROM HTX WHERE HTX.STATUS='N' AND ROWNUM=1) FOR UPDATE OF HTX.STATUS, DTX.STATUS, CTX.STATUS

 but, if we have 1 header row * 130 DTX rows* 2000 CTX rows, about 300000 rows.

 

Any help on how to dcreas the number of rows?

BTW, values from table DTX are always present, from CTX not.

 

THANX A LOT GUYS!
 

[whalemangler]

Reduce the number of rows?  I would think that you are either getting what you wanted or you are not.  If you want to reduce the rows, change the where clause.

What are you trying to do?  What data are in each table?

[ammoQ]

"ROWNUM=1" gives you an arbitrary row that matches the where condition, not necessarily the oldest one.

You should replace that part of the query by

AND HTX.NO = (select min (HTX_NO FROM HTX WHERE HTX.STATUS='N')

(assuming that the lowest number is the oldest one; if not, it gets a bit more complicated) 

Anyway, which kind of information do you really need from the other tables?

Maybe you really want something along the lines of

SELECT * FROM (
SELECT HTX.*, (SELECT SUM(CTX.FOOBAR) FROM CTX WHERE SOCTX_ORDER_NO=HTX.NO) AS CTX_FOOBAR_SUM, (SELECT SUM(DTX.FOOBAR) FROM DTX WHERE NO=HTX.NO) AS DTX_FOOBAR_SUM
FROM HTX WHERE STATUS='N' ORDER BY HTX.CREATION_DATE
) WHERE ROWNUM = 1

As far as I can read from your query, CTX and DTX are not related to each other; both are some kind of detail to HTX. It makes little sense at all to join both of them in one query.
 

 

[Zigo]

Thanks anyone for the answers, actualy there were others requirements that I was (and still am) unaware of (I "inherited" the query from a previous developer).

The problem is now solved, it was a bad mistake by the application generating the tables.

Awfully, as I said, I still don't know some of the requirements since our clients said "it's solved, don't touch it".

 

Thanks A LOT anyway!