CASE in ORDER BY

Reply

tlbignerd Posted: 10-03-2009 1:04 PM

rated by 0 users

I have a dynamic query with a hierarchical object model where we want to order records by the parent sort if it is a parent record, otherwise use a child sort. The query I have is this:

SELECT
    eme.[EMEntityID] ,
    eme.[EMEntityCode] ,
    eme.[EMEntityName] ,
    eme.[Active] ,
    ens.[EMEntityParentID] ,
    eme.[ShowOrder] ,
    p.[ShowOrder] AS 'ParentsShowOrder'
FROM
    [ref_EMEntity] eme INNER JOIN [ref_EMEntityType] emety
	ON  eme.[EMEntityTypeID] = emety.[EMEntityTypeID] 
	LEFT JOIN [ref_EMEntityStructure] ens
	ON  eme.[EMEntityID] = ens.[EMEntityID] 
	INNER JOIN [ref_EMEntity] p
	ON  ens.[EMEntityParentID] = p.[EMEntityID]
WHERE
    emety.[EMEntityTypeID] = 2
ORDER BY
    CASE p.ementityid when 24 then eme.ShowOrder else p.[ShowOrder] end ASC ,
    CASE p.ementityid when 24 then 0 else eme.[ShowOrder] end ASC

Where I really just care about the order by section. How can I do this order by in a dynamic query, sort of like:

                qryEMEntity.OrderBy(IIf(qryParentEMEntity.EMEntityID = SiteEntity.HQ, qryEMEntity.ShowOrder.Ascending, qryParentEMEntity.ShowOrder.Ascending), _
                            IIf(qryParentEMEntity.EMEntityID = SiteEntity.HQ, 0, qryEMEntity.ShowOrder.Ascending))

Thanks.

Reply

Mike.Griffin replied on 10-03-2009 6:08 PM

rated by 0 users

See this POST. Scroll down to the "Raw SQL Injection Everywhere" section. For cases where we our DynamicQuery API doesn't support something you need you can use what we call "Raw SQL Injection" which is raw sql pass in in the form of a string with < > around it. Anything you pass in like that we pass directly to your database engine without the < > of course. You can build your whole query using the normal API and then on the OrderBy() you can use the raw SQL injection.