I am stuck with a problem. I want a MSSQL equivalent for similar MySQL query:
$IDS are in array converted to string to pass in the query: (Below if not a part of code, just example text to show you what I want to do)
$IDS = “‘B’, ‘A’, ‘D’, ‘E’, ‘C’”
SELECT id, name, some_date
WHERE name IN ($IDS)
ORDER BY FIELD(name, $IDS), some_date DESC
Please guide me !
CASE expressions, using SUBSTRING
note: will work only if each term in the $IDS list is the same length, and if there is a fixed number of them – otherwise, you’ll have to consider designing a new “sequence” table containing the $IDS terms each in its own row, along with a sequence number
I am afraid that length of the terms in $IDS is same however, there can be 1 OR more terms.
okay, so whatever language you are using to generate the query and insert the $IDS string, you will have to use that language to generate the CASE expressions
Actually I am working on JasperStudio / Jasper Report. So query there is just text.
Can you give me an example of the case statement, may be you can take the my example query and give me an idea like what the case statement should look like ?
WHERE name IN ( 'Curly'
, 'Curly Joe' )
BY CASE WHEN name = 'Curly' THEN 1
WHEN name = 'Larry' THEN 2
WHEN name = 'Moe' THEN 3
WHEN name = 'Shemp' THEN 4
WHEN name = 'Joe' THEN 5
WHEN name = 'Curly Joe' THEN 6
ELSE 9 END
, some_date DESC
So nice of you !
May you get more success, fame and knowledge ! May you keep helping others.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.