Sheldon Livingston
asked on
Pervasive 11 order by query
Using Pervasive 11, I wish to order by "category".
I wish that the category MAT is listed first, followed by LAB, OTH, MSC and then all the rest. All the rest will be numbers.
So, category looks like MAT, LAB, OTH, MSC, 112, 700, 430, etc.
Tried, with no luck, using a case statement.
Any thoughs?
I wish that the category MAT is listed first, followed by LAB, OTH, MSC and then all the rest. All the rest will be numbers.
So, category looks like MAT, LAB, OTH, MSC, 112, 700, 430, etc.
Tried, with no luck, using a case statement.
Any thoughs?
And if your values are NOT always larger than 4, then you could use:
SELECT *
FROM yourtable
ORDER BY CASE
WHEN category = 'MAT' THEN -4
WHEN category = 'LAB' THEN -3
WHEN category = 'OTH' THEN -2
WHEN category = 'MSC' THEN -1
ELSE convert(category, SQL_INTEGER)
END
Also, please note that the proper terminator for the CASE statement on PSQL is simply "END", not "END CASE".
SELECT *
FROM yourtable
ORDER BY CASE
WHEN category = 'MAT' THEN -4
WHEN category = 'LAB' THEN -3
WHEN category = 'OTH' THEN -2
WHEN category = 'MSC' THEN -1
ELSE convert(category, SQL_INTEGER)
END
Also, please note that the proper terminator for the CASE statement on PSQL is simply "END", not "END CASE".
ASKER
The code below is not working...
SELECT
"TJOB" AS "Job",
"TPHASE" AS "Phase",
"TCAT" AS "Category"
FROM
"CURRENT_JCT_RECORD_1"
WHERE
"TJOB"= '01-1234-01'
ORDER BY CASE
WHEN "TCAT" = 'MAT' THEN 1
WHEN "TCAT" = 'LAB' THEN 2
WHEN "TCAT" = 'OTH' THEN 3
WHEN "TCAT" = 'MSC' THEN 4
ELSE 5
END
SELECT
"TJOB" AS "Job",
"TPHASE" AS "Phase",
"TCAT" AS "Category"
FROM
"CURRENT_JCT_RECORD_1"
WHERE
"TJOB"= '01-1234-01'
ORDER BY CASE
WHEN "TCAT" = 'MAT' THEN 1
WHEN "TCAT" = 'LAB' THEN 2
WHEN "TCAT" = 'OTH' THEN 3
WHEN "TCAT" = 'MSC' THEN 4
ELSE 5
END
what does "not working" mean?
error? wrong results? no results? keyboard catches fire?
error? wrong results? no results? keyboard catches fire?
ASKER
The mouse escapes my hand and runs under the desk.
I am doing this in Excel and am not privy to the error code/message... I don't get any results... just a generic "didn't work" message.
Does Pervasive have a utility, kind of like Access query builder, where I can run the code to get the exact error message?
I am doing this in Excel and am not privy to the error code/message... I don't get any results... just a generic "didn't work" message.
Does Pervasive have a utility, kind of like Access query builder, where I can run the code to get the exact error message?
Of course. Start the Pervasive Control Center either with the Start Menu icon or by typing "PCC" at any command prompt. Run your query there first.
I am worried about the FIRST line of that -- the error message indicates SimbaEngine ODBC Driver. If this were the PSQL ODBC Engine, it would say something different. Were you able to run your queries from the PCC?
ASKER
Can't connect the PCC to the desired db.
What application are you running? Is it something like Timberline which uses its own, custom, ODBC drivers?
ASKER
Timberline and "Office Connector"...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you... I'll find a work around.
FROM yourtable
ORDER BY CASE
WHEN category = 'MAT' THEN 1
WHEN category = 'LAB' THEN 2
WHEN category = 'OTH' THEN 3
WHEN category = 'MSC' THEN 4
ELSE 5
END CASE,
category
or if your numeric values will always be larger than 4 you could simply do...
SELECT *
FROM yourtable
ORDER BY CASE
WHEN category = 'MAT' THEN 1
WHEN category = 'LAB' THEN 2
WHEN category = 'OTH' THEN 3
WHEN category = 'MSC' THEN 4
ELSE convert(category, SQL_INTEGER)
END CASE