Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Pervasive 11 order by query

Posted on 2014-02-24
Medium Priority
328 Views
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
Question by:classnet
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 6
• 5
• 2

LVL 74

Expert Comment

ID: 39882708
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

LVL 28

Expert Comment

ID: 39883009
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 Comment

ID: 39883285
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

LVL 74

Expert Comment

ID: 39883315
what does "not working" mean?

error? wrong results? no results? keyboard catches fire?
0

Author Comment

ID: 39883326
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

LVL 28

Expert Comment

ID: 39883358
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 Comment

ID: 39884153
This is the error I receive:
0

LVL 28

Expert Comment

ID: 39884191
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 Comment

ID: 39884204
Can't connect the PCC to the desired db.
0

LVL 28

Expert Comment

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

Author Comment

ID: 39884241
Timberline and "Office Connector"...
0

LVL 28

Accepted Solution

Bill Bach earned 2000 total points
ID: 39884275
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 Closing Comment

ID: 39884277
Thank you... I'll find a work around.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote toâ€¦
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.â€¦
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can reâ€¦
Weâ€™ve all felt that sense of false security beforeâ€”locking down external access to a database or component and feeling like weâ€™ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many wâ€¦
###### Suggested Courses
Course of the Month9 days, 19 hours left to enroll

#### 722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.