Solved

Pervasive 11 order by query

Posted on 2014-02-24
13
300 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 73

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 73

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql joining from the same table 6 42
Update data using formula 22 25
possible to record changes (trigger I think) msql 11 34
SELECT query on two levels (detail and summary) 13 48
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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