SQL Command Index 1

[Smithsc]

Hi there, I've created an SQL command query listed below:
select
to_number(ALFA_ASSET_ID) AS IASSNUM,
ASSETS.ALFA_ASSET_ID,
ASSETS.ASSET_ID,
ASSETS.LOST,
ASSETS.MAKE,
ASSETS.UNIT,
ASSETS.REFERENCE_NUMBER,
ASSETS.SERIAL_NUM,
ASSETS.TAG_NUMBER,
ASSETS.DESCRIPTION,
ASSETS.ASSET_COST,
ASSETS.DISPOSAL_AMOUNT,
ASSETS.MECHANICAL_DESCRIPTION,
ASSETS.COSMETIC_DESCRIPTION,
ASSETS.DEHIRE_DATE,
ASSET_TRANSACTIONS.DEAL_ID,
TRANSACTIONS.DATE_CSV_EXPORTED
from
ASSETS
left outer join ASSET_TRANSACTIONS on ASSETS.ASSET_ID = ASSET_TRANSACTIONS.ASSET_ID
left outer join TRANSACTIONS on ASSET_TRANSACTIONS.TRANSACTION_ID = TRANSACTIONS.TRANSACTION_ID
where
(TRANSACTIONS.TRANSACTION_TYPE = 'RETURNS') or (TRANSACTIONS.TRANSACTION_TYPE = 'SCRAP')

create index SRA
on command(ASSET_ID);

and I'm trying to add an index onto the ASSET_ID field.

Unfortunately I keep getting an error 'SQL command not properly ended'. If somebody could supply the correct code for creating the index I wold be very grateful.

I am using crystal XI accessing data from an Oracle DB.

 

[IanWaterman]

Why do you need an index for the report, not sure it will help you. Just use SQL as above excluding
create index SRA
on command(ASSET_ID);

And it should save as a command.

Ian

 

[Smithsc]

I need to add an index so that when I link it to other tables it retrieves data quickly.

 

[IanWaterman]

Not sure you can create an index inside the command.

Have you tried using the Report Bursting index. Create command as I suggest and then in

Report - Bursting Index

Create an index on the field you want, these are meant to speed up data selection.

Ian