Solved

Pervasive 11 order by query

Posted on 2014-02-24
13
297 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

'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 …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now