x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 366

# 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?
0
classnet
• 6
• 5
• 2
1 Solution

Commented:
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 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
0

PresidentCommented:
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".
0

Author Commented:
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
0

Commented:
what does "not working" mean?

0

Author Commented:
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?
0

PresidentCommented:
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.
0

Author Commented:
This is the error I receive:
0

PresidentCommented:
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?
0

Author Commented:
Can't connect the PCC to the desired db.
0

PresidentCommented:
What application are you running?  Is it something like Timberline which uses its own, custom, ODBC drivers?
0

Author Commented:
Timberline and "Office Connector"...
0

PresidentCommented:
Ouch.  Sadly, Timberline does NOT use the "standard" Pervasive PSQL ODBC drivers.  Instead, they ship their own version, which they believe offers additional security (since nobody can read or access data in a way that they don't want to allow).

It appears that their own ODBC driver does not support the CASE function offered by sdstuber.  This construct is perfectly valid in PSQLv11.30 (because I tested it there on the DEMODATA database), but is apparently not valid in the Timberline drivers.

You will need to contact Timberline technical support to log this as a defect in their software.  I don't know how quickly they fix things like this, though.  You may find is easier to import all of the data into MS Access FIRST, then either try the query in Access, or run a conversion that changes the data the way you want in the Access tables, in order to avoid the CASE statement.
0

Author Commented:
Thank you... I'll find a work around.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.