How to use the below expression in the SQL Query

sybase

I have this expression for sorting. I need to use this expression in my SQL code for the sorting purpose. Can anyone help me with this??

I tried case when stmt but it fails.

Expression:

if ([All Accounts].[ea_end_of_activity] is missing or 
[All Accounts].[ea_end_of_activity] is null)
then (
if   ([Japanese Accounts].[aco_ac_ea_id] is missing)
then ('1')
else ('0')
)
else ('2')
+
[All Accounts].[aco_acc_short_name]

CODE:

SELECT DISTINCT
    First.aco_ac_ea_id,
    Sec.aco_acc_short_name,
    Sec.aco_acc_marketing,
    Sec.ea_spark_number,
    Sec.ea_end_of_activity
FROM
    (
    SELECT aco_ac_ea_id                                            
    FROM CD.dbo.MAC
    WHERE
    MAC.aco_locale = 'ja' AND
    MAC.aco_acc_marketing_status = 'VALIDATED'
    ) as First
RIGHT OUTER JOIN
    (
    SELECT
     aco_ac_ea_id,
     aco_acc_short_name,
     aco_acc_marketing,
     ea_spark_number,
    ea_end_of_activity
    FROM
    CD.dbo.MAC,
    CD.dbo.MEA
    WHERE
    MAC.aco_locale ='en-us' and
    MAC.aco_ac_ea_id = MEA.ea_id
    ) as Sec
ON First.aco_ac_ea_id=Sec.aco_ac_ea_id

Best Answer

No idea what the is missing is supposed to mean (is it the same as is null?). If yes, the CASE expression and the ORDER BY can be written like this:

ORDER BY
  CASE 
    WHEN Sec.ea_end_of_activity IS NOT NULL
      THEN 2
    WHEN First.aco_ac_ea_id IS NULL 
      THEN 1
    ELSE 0 
  END,
  Sec.aco_acc_short_name ;

The First. and Sec. can be omitted (at least in other DBMS, not 100% sure about Sybase), since they are in the SELECT list.

Related Question