Re: ORDER BY a previous result
From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 17 Feb 2009 15:22:09 -0800 (PST)
Message-ID: <8bf575ae-09fb-49b5-af61-ee78b621e16b_at_f11g2000vbf.googlegroups.com>
On Feb 17, 5:31 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> SELECT .......
> FROM
> FOO,
> BAR
> WHERE
> FOO.FOO_ID=BAR.FOO_ID(+)
> AND FOO_ID IN (1, 98, 12, 33)
> ORDER BY
> INSTR('1,98,12,33,' , TO_CHAR(FOO_ID)||',');
>
> Before deciding to use the above technique, determine if there is a
> better way to do *everything* in a single SQL statement. You might be
> able to do this by wrapping your complicated SQL statement into an
> inline view, and joining to that just as if it were a regular table:
> SELECT .......
> FROM
> FOO,
> BAR,
> ( complicated SQL here ) V
> WHERE
> V.FOO_ID=FOO.ID
> AND FOO.FOO_ID=BAR.FOO_ID(+)
> ORDER BY
> V.RN;
>
> The RN column would be generated inside the inline view V, possibly
> like this, if there is an ORDER BY clause in the inline view:
> ROWNUM RN
Small correction to my post - about 10 minutes after posting, I thought what if...
Date: Tue, 17 Feb 2009 15:22:09 -0800 (PST)
Message-ID: <8bf575ae-09fb-49b5-af61-ee78b621e16b_at_f11g2000vbf.googlegroups.com>
On Feb 17, 5:31 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> SELECT .......
> FROM
> FOO,
> BAR
> WHERE
> FOO.FOO_ID=BAR.FOO_ID(+)
> AND FOO_ID IN (1, 98, 12, 33)
> ORDER BY
> INSTR('1,98,12,33,' , TO_CHAR(FOO_ID)||',');
>
> Before deciding to use the above technique, determine if there is a
> better way to do *everything* in a single SQL statement. You might be
> able to do this by wrapping your complicated SQL statement into an
> inline view, and joining to that just as if it were a regular table:
> SELECT .......
> FROM
> FOO,
> BAR,
> ( complicated SQL here ) V
> WHERE
> V.FOO_ID=FOO.ID
> AND FOO.FOO_ID=BAR.FOO_ID(+)
> ORDER BY
> V.RN;
>
> The RN column would be generated inside the inline view V, possibly
> like this, if there is an ORDER BY clause in the inline view:
> ROWNUM RN
Small correction to my post - about 10 minutes after posting, I thought what if...
To avoid matching only the last digit of a two digit number, or the
last two digits of a three digit number, etc. the list must also begin
with a comma, and the FOO_ID column must have a comma embedded at the
beginning inside the INSTR function.
This:
INSTR('1,98,12,33,' , TO_CHAR(FOO_ID)||',')
Becomes:
INSTR(',1,98,12,33,' , ','||TO_CHAR(FOO_ID)||',')
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Feb 17 2009 - 17:22:09 CST