I'm working with mimer mobile db and need som help with the following
statement in a procedure.

I have the following today:
SELECT column1 FROM table1
ORDER BY case when some_expression then column2 else column3
end;

I would like to do the following but the syntax seems to be wrong:

SELECT column1 FROM table1
ORDER BY case when some_expression then coloumn1, column2 else
coloumn3, column4 end;

i.e. add some more columns to the order expression.

Alternatively, I would like to do something like:

SELECT column1 FROM table1{INDEX case when some_expression then index1
else index2 end};

where index1 and index2 are indexes created for table1.

Any idea's of how to accomplish this?
Cheers
//Kid

dos.fishing (AT) gmail (DOT) com wrote:
Try:

SELECT column1 FROM table1
ORDER BY
case when some_expression then coloumn1 else column3 end,
case when some_expression then coloumn2 else column4 end

/Lennart

Thanks Lennart, that works! =)

But unfortunatly, I still have problems.

The reason I wanted to order by either (column1,column2) or
(column3,column4) was because I have indexes on these coloumns:
CREATE INDEX index1 ON table1(column1,column2);
CREATE INDEX index2 ON table1(column3,column4);

But it seems like the compiler/optimizer doesn't understand that I
want to use the index when using you solution (and I'm not blaming
it!)
Any other ideas?

Cheers
//Kid

dos.fishing (AT) gmail (DOT) com wrote:
I assume the optimizer uses the index if you do either:

order by column1, column2

or:

order by column3, column4

correct?


What is some_expression? Can you determine it in advance and either:

order by colomn1, column2

or

order by colomn3, column4

What type is column1, 2, 3 and 4? If they are some kind of chartype
perhaps it helps with:

order by
case when some_expression then coloumn1 || column2 else column3 ||
column4 end

dos.fishing (AT) gmail (DOT) com wrote:
On second thought, why would it use the index to order the result set?
Does it really use the index if you

order by column1, column2?


/Lennart

Yes, it uses the index to scan through the table and not having to
sort/order afterwards.

I'm solving it by duplicating code:

if some_expression then
..
..
SELECT column1 FROM table1
ORDER BY coloumn1, column2;
..
..
else
..
..
SELECT column1 FROM table1
ORDER BY coloumn3, column4;
..
..
endif

Thanks for your help!
//Kid

dos.fishing (AT) gmail (DOT) com wrote:
Yes, but I'm not sure that is the most cost efficient way to do it at
all times. I dont know Mimer so I can only guess that by doing an index
scan you will get a lot of random touch on the data pages. A sequential
scan on the data pages might be more efficient, since you are reading
the whole table.

Anyhow, glad you solved it

/Lennart