Solved

Pervasive 11 order by query

Posted on 2014-02-24
13
302 Views
Last Modified: 2014-02-24
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
Comment
Question by:classnet
  • 6
  • 5
  • 2
13 Comments
 
LVL 74

Expert Comment

by:sdstuber
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

by:Bill Bach
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

by:classnet
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39883315
what does "not working" mean?

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

Author Comment

by:classnet
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

by:Bill Bach
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

by:classnet
ID: 39884153
This is the error I receive:
Pervasive error
0
 
LVL 28

Expert Comment

by:Bill Bach
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

by:classnet
ID: 39884204
Can't connect the PCC to the desired db.
0
 
LVL 28

Expert Comment

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

Author Comment

by:classnet
ID: 39884241
Timberline and "Office Connector"...
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 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

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

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

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

Join & Ask a Question