?
Solved

Pervasive SQL query help

Posted on 2016-07-26
18
Medium Priority
?
41 Views
Last Modified: 2016-08-12
Let's say FieldA is customer name.  FieldB can contain "A", "B" or "C".

I'd like the sort order to be by name BUT listing "B" (FieldB) last.

Thank you
0
Comment
Question by:classnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
  • +1
18 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41729900
order by case when fieldb ='B' then 1 else 0 end, fieldA
0
 

Author Comment

by:classnet
ID: 41729939
Guy... you sure Pervasive likes that?  Not sure if Pervasive can do case when:
Error
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41729954
sorry, forgot that persative has a different syntax ( not using it myself ... )
http://help.pervasive.com/plugins/servlet/mobile#content/view/6390355
0
Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41729957
and it's as from version 9 ...
0
 

Author Comment

by:classnet
ID: 41729968
Thank you for the effort... still get an error.
Error
0
 
LVL 18

Expert Comment

by:mirtheil
ID: 41730060
What version of Pervasive are you using?  I haven't seen "SImba" errors in a long time.  

I'm not sure it's going to be possible but I want to test on the version you're using.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41730153
I see, you put order by select case.
Remove select keyword

Order by case ...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41730155
And also it is end and not end select
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41731049
Pervasive does support both CASE EXPRESSIONS and CASE STATEMENTS ( they are DIFFERENT )

You need a CASE EXPRESSION (and when using these do not re-use the key word "select")

========

perhaps you will share the actual SQL you are using? In the long run it is easier for all partes

e.g. try this:

SELECT
      job
    , jstatus
    , jdesc
FROM master_jcm_record_1_1
WHERE jtitl4 = 'LS'
ORDER BY
      CASE
            WHEN jstatus = 'In Progress' THEN 1
            ELSE 0
      END
    , job

Open in new window

0
 

Author Comment

by:classnet
ID: 41731063
I think that the Timberline Pervasive ODBC drive doesn't support CASE.

I used the code below to obtain what I need:
SELECT
  TJOB,
  TEXTRA,
  TPHASE,
  TCAT,
  IF(TCAT = 'L', 1, 2) AS "Category Sort"
 
FROM
  CURRENT_JCT_RECORD_1
 
WHERE
  TJOB='03-001' AND
  TCAT<>''
 
ORDER BY
  1, 2, 3, 5, 4

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41731087
thats also a possibility
or you create a view that dies the order by, and your app only does a select * from your_view...
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41731121
If the ODBC driver does not support a case expression, but supports IF() instead try the equivalent IF directly in the ORDER BY.

SELECT
      job
    , jstatus
    , jdesc
FROM master_jcm_record_1_1
WHERE jtitl4 = 'LS'
ORDER BY
      IF(jstatus  = 'In Progress' , 1, 0)
    , job


If that doesn't work then try the use of column number as suggested - but using column number is a problem should anyone change the query down the track.

ps ===
I'm not keen on using order by in a View .. & does Pervasive even allow that?
(Some products won't permit order by in view definitions.)
0
 

Accepted Solution

by:
classnet earned 0 total points
ID: 41738733
I was able to use the IF statement in the SELECT portion of the code... couldn't get it to work in the ORDER BY clause.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41738819
try this perhaps?
SELECT
      job
    , jstatus
    , jdesc
FROM (

      SELECT
            job
          , jstatus
          , jdesc
          , IF(jstatus = 'In Progress', 1, 0) sortby
      FROM master_jcm_record_1_1
      WHERE jtitl4 = 'LS'
      ) d
ORDER BY
      sortby
;

Open in new window

0
 

Author Comment

by:classnet
ID: 41738821
The sort desired was pretty simple (list all then B) so I simply used the IF statement directly in the SELECT.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41738827
ok. thought you were still stuck.
curious that a standard case expression should not work but IF() does, oh well.
0
 

Author Comment

by:classnet
ID: 41738876
Neither IF or CASE work in the ORDER BY clause implementation of Timberline's ODBC driver... supposedly in a future date.  Thus I did it in the SELECT portion.
0
 

Author Closing Comment

by:classnet
ID: 41753521
Solved myself
0

Featured Post

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

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

'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 …
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 …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

771 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