Re: bad sql code?

From: Vitaliy Mogilevskiy <vit100gain_at_earthlink.net>
Date: Sat, 08 May 1999 23:04:31 -0700
Message-ID: <3735256F.1026A003@earthlink.net>

Try this:

Select ord.ORDER_ID

,      min(ord.REFERENCE_NUM)
,      min(ord.ISSUED_DATE)
,      sum(item.UNIT_PRICE * item.QUANTITY)
,      min(ord.CUSTOMER_PO_NUMBER )
,      min(org.NAME||DECODE(bdem.REFERENCE_NUM,
                                            '',''
                                              ,' [') || bdem.REFERENCE_NUM

||DECODE(bdem.REFERENCE_NUM,

'',''

,']'))

from             ORDERS               ord
,                ORDER_ITEM           item
,                BUS_PARTY_ROLE       role
,                ORGANIZATION         org
,                BP_DEMOGRAPHICS      bdem
where   ord.TYPE                  = 'INVOICE'
and     ord.RECEIVED_BY_EDI       = 'Y'
and     ord.ORDER_ID              = item.ORDER_ID
and     ord.RESELLER_ID           = role.BUS_PARTY_ROLE_ID(+)
and     item.ACCOUNT_ID           = 0                         /* I am not sure
if you want accounts that are = 0, if not do < 0 */
and     role.BUS_PARTY_ID         = org.BUS_PARTY_ID(+)
and     role.BUS_PARTY_ROLE_ID    = bdem.BUS_PARTY_ROLE_ID(+)

group by ord.ORDER_ID

I hope I understood your query correctly if not run EXPLAIN PLAN or better set tracing on and user TKPROF

Good luck!

Vitaliy Mogilevskiy
Get free DBA Scripts @http://home.earthlink.net/~vit100gain/index.html

David Spaisman wrote:

> Hello:

>

> I am trying to figure out why a new application (100 + tables) and very
> little data thus far --app is up 2 weeks -- is consuming 2gb of temp
> tablespace and 50 users are logged in, with about 15 using the code below
> which is one of the main memo choices:

>

> Can any one help me rewrite this bad code? Thanks.

>

> David Spaisman
> ----------------------------------------------------------------------------
> -----------

>


> Select        ord.ORDER_ID


>  , min(ord.REFERENCE_NUM)

> ,min(ord.ISSUED_DATE)
> , sum(item.UNIT_PRICE * item.QUANTITY)
> , min(ord.CUSTOMER_PO_NUMBER )
> , min(org.NAME
> || DECODE(bdem.REFERENCE_NUM, '', '', ' [') || bdem.REFERENCE_NUM ||
> DECODE(bdem.REFERENCE_NUM, '', '', ']'))
> from ORDERS ord ,
> ORDER_ITEM item ,
> BUS_PARTY_ROLE role ,
> ORGANIZATION org ,
> BP_DEMOGRAPHICS bdem where ord.TYPE = 'INVOICE' and ord.RECEIVED_BY_EDI
> = 'Y'
> and
> item.ORDER_ID = ord.ORDER_ID
> and
> ord.RESELLER_ID = role.BUS_PARTY_ROLE_ID(+)
> and role.BUS_PARTY_ID = org.BUS_PARTY_ID(+)
> and bdem.BUS_PARTY_ROLE_ID(+) = role.BUS_PARTY_ROLE_ID
> and not exists (Select * from ORDER_ITEM itemsub
> where ord.ORDER_ID = itemsub.ORDER_ID and ACCOUNT_ID >
> 0)
> group by ord.ORDER_ID
Received on Sun May 09 1999 - 01:04:31 CDT