Conditional parsing on sort clause

[db2user2]

I have a two tables regarding my question. Table 1, say INDEX, stores metadata on statements. Table 2, say DEFINITION, defines what type of record is in INDEX.

There is a column in table INDEX where the metadata, say METADATA, is stored. This column is a CHARACTER type. I need to write a query that will sort table INDEX by the METADATA column and have some sort of logic to parse the sort to an INTEGER when the related column, say TYPE, in DEFINITION is equal to 'I'.


Example desired sort when METADATA is defined as an 'I' type by column TYPE in table DEFINITION:

SELECT
*
FROM
INDEX INDEX,
DEFINITION DEFINITION
WHERE
DEFINITION.DEF_ID = INDEX.DEF_ID AND
DEFINITION.DEF_ID = 1
ORDER BY INTEGER(METADATA)

Example normal sort:

SELECT
*
FROM
INDEX INDEX,
DEFINITION DEFINITION
WHERE
DEFINITION.DEF_ID = INDEX.DEF_ID AND
DEFINITION.DEF_ID = 1
ORDER BY METADATA


Is there a way to add in the integer parsing of METADATA based on a related record in a different table? Would it be possible to inject the INTEGER( ) into the ORDER BY clause based on some sort of IF statement/logic?


(Note: This is the way our tables are designed, please don't suggest modifying the design as that is not a viable solution. Thank you.)

 

[delta403]

Can you please share your table information?

 

[fredericofonseca]

you can add case statements to your order by..


e.g.
select...
from ...
order by
case
when metadata like ´%first order%´
then 1
when metadata like ´%second order%´
then 2
else 3
end

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Truusvlugindewind]

I use this syntax

select empno
     , sex
     , salary
     , case when sex  = 'F'
            then salary * -1
            else salary
       end as sort_col     
from   employee
order by sort_col

Nonsence example, but when you replace "sex = male/female" by some kind of "debit/credit" code it suddenly makes sence