?
Solved

Pervasive 11 order by query

Posted on 2014-02-24
13
Medium Priority
?
345 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 29

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 29

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 29

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 29

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 29

Accepted Solution

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

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

579 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