Improve company productivity with a Business Account.Sign Up

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

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
Asked:
classnet
  • 6
  • 5
  • 2
1 Solution
 
sdstuberCommented:
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
 
Bill BachPresidentCommented:
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
 
classnetAuthor 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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
sdstuberCommented:
what does "not working" mean?

error? wrong results? no results? keyboard catches fire?
0
 
classnetAuthor 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
 
Bill BachPresidentCommented:
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
 
classnetAuthor Commented:
This is the error I receive:
Pervasive error
0
 
Bill BachPresidentCommented:
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
 
classnetAuthor Commented:
Can't connect the PCC to the desired db.
0
 
Bill BachPresidentCommented:
What application are you running?  Is it something like Timberline which uses its own, custom, ODBC drivers?
0
 
classnetAuthor Commented:
Timberline and "Office Connector"...
0
 
Bill BachPresidentCommented:
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
 
classnetAuthor 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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now