Solved

Pervasive SQL query help

Posted on 2016-07-26
18
31 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
  • 7
  • 6
  • 4
  • +1
18 Comments
 
LVL 142

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 142

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
 
LVL 142

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 142

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 142

Expert Comment

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

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 142

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 48

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 48

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 48

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

'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 …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

914 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

14 Experts available now in Live!

Get 1:1 Help Now